Postgres JDBC driver adaptive fetching running out of memory

Question

I'm trying to use the Postgres JDBC driver to query data from a table where each rows can be up to about 50MB. Unfortunately, without any memory restrictions, the Postgres driver can use too much memory and cause OOMs (even with a very healthy Xmx) because it buffers so much data locally.

I've tried to restrict the driver to using less memory, for example 1GB, and telling it to buffer less too. Since no one row is bigger than 50MB this should work fine, but unfortunately I'm now getting Exceptions thrown from the Postgres driver itself. The exceptions are because it is trying to allocate more memory than I have configured it with.

If I use this configuration:

"jdbc:postgresql://localhost/dbname?maxResultBuffer=1G&adaptiveFetch=true&adaptiveFetchMaximum=2&defaultRowFetchSize=1"

I'll get an Exception thrown here, in PGStream

      if (resultBufferByteCount > maxResultBuffer) {
        throw new PSQLException(GT.tr(
          "Result set exceeded maxResultBuffer limit. Received:  {0}; Current limit: {1}",
          String.valueOf(resultBufferByteCount), String.valueOf(maxResultBuffer)),PSQLState.COMMUNICATION_ERROR);
      }

If I set a breakpoint there I can see:

value = 41155480
resultBufferByteCount = 1021091718
maxResultBuffer = 1000000000

Which shows it's picking up the config fine. I've also inspected it to make sure it's getting the fetch size config and it is.

Is there some other config I'm missing? Clearly the Postgres driver is reading more rows than I've allowed it to.

thanks

(postgreqsl 42.5.1, java 17.0.5, hikaricp 5.0.1 with max connections of 1)

What about using Getting results based on a cursor which was the solution before the "adaptive buffering" was introduced — user330315
– user330315, Commented Jan 20, 2023 at 15:06
@a_horse_with_no_name If I use adaptiveFetch=false&defaultRowFetchSize=1 it still fails. I don't have access to the Statements to call setFetchSize directly on. I need to configure options on the connection string. — David
– David, Commented Jan 20, 2023 at 15:41

jjanes · Accepted Answer · 2023-01-21 23:32:50Z

2

The adaptive buffer, like setFetchSize, only works if autocommit is off. If Autocommit is on, then they are silently ignored. I don't know if there is a way to turn autocommit off though the jdbc connect string, I haven't found one.

answered Jan 21, 2023 at 23:32

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Over a year ago

Ah I didn't know that. Unfortunately turning autocommit off made no difference. (I have access to the Hikari config so was able to turn it off from there)

jjanes Over a year ago

@David Turning auto commit off made this work for me just using java code. I can't say what Hikari's config is actually doing, and can't test it myself.

David Over a year ago

Well if autocommit off works for you, it sounds like I have a problem somewhere else in the stack, and you've answered my original question. Thank you for you looking into it so thoroughly!

Collectives™ on Stack Overflow

Postgres JDBC driver adaptive fetching running out of memory

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related