Обсуждение: ResultSet storing all rows with defaulftFetchSize to 5000
An application is using the postgresql comunity jdbc driver v 9.4.1208 and I faced a problem because after analysing a java heap dump of the execution of big batch java procedure, I discover that the jdbc driver is keeping at the keep every row fetched. The jvm heap contained 1,2M rows and we have setup a defaultFetchSize of 5000 rows also I share you adiciontal detail, autocommit is false, ResultSet is TYPE_FORWARD_ONLY , ResultSet holdability is HOLD_CURSOR_OVER_COMMIT.
And the postgresql comunity jdbc driver 9.4 documentation said:
"
By default the driver collects all the results for the query at once. This can be inconvenient for large data sets so the JDBC driver provides a means of basing a ResultSet on a database cursor and only fetching a small number of rows.
A small number of rows are cached on the client side of the connection and when exhausted the next block of rows is retrieved by repositioning the cursor.
Note
Cursor based ResultSets cannot be used in all situations. There a number of restrictions which will make the driver silently fall back to fetching the whole ResultSet at once.
*The connection to the server must be using the V3 protocol. This is the default for (and is only supported by) server versions 7.4 and later.
*The Connection must not be in autocommit mode. The backend closes cursors at the end of transactions, so in autocommit mode the backend will have closed the cursor before anything can be fetched from it.
*The Statement must be created with a ResultSet type of ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need to be rewritten to take advantage of this, but it also means that you cannot scroll backwards or otherwise jump around in the ResultSet.
*The query given must be a single statement, not multiple statements strung together with semicolons.
"
why the driver is storing so many rows because I think my connections satisfy all the conditions to ONLY store a limit number of rows.
Regards
On Wed, Feb 7, 2018 at 1:00 PM, Jaime Soler <jaime.soler@gmail.com> wrote:
... ResultSet holdability is HOLD_CURSOR_OVER_COMMIT. ...And the postgresql comunity jdbc driver 9.4 documentation said:"... There are a number of restrictions which will make the driver silently fall back to fetch the whole ResultSet ..."
And the statement must be created with a ResultSet holdability of CLOSE_CURSORS_AT_COMMIT - which is not documented. https://github.com/pgjdbc/pgjdbc/pull/1105 submitted to fix that.
The relevant code fragment from org.postgresql.jdbc.PgStatement.java is ...
// Enable cursor-based resultset if possible.
if (fetchSize > 0 && !wantsScrollableResultSet() && !connection.getAutoCommit()
&& !wantsHoldableResultSet()) {
flags |= QueryExecutor.QUERY_FORWARD_CURSOR;
}
And the fragment from org.postgresql.v3.QueryExecutorImpl.java ...
boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta
&& fetchSize > 0 && !describeOnly;
Jaime,
9.4.1208 is considerably out of date. You may want to upgrade.
On 8 February 2018 at 19:13, Brad DeJong <bpd0018@gmail.com> wrote:
On Wed, Feb 7, 2018 at 1:00 PM, Jaime Soler <jaime.soler@gmail.com> wrote:... ResultSet holdability is HOLD_CURSOR_OVER_COMMIT. ...And the postgresql comunity jdbc driver 9.4 documentation said:"... There are a number of restrictions which will make the driver silently fall back to fetch the whole ResultSet ..."And the statement must be created with a ResultSet holdability of CLOSE_CURSORS_AT_COMMIT - which is not documented. https://github.com/pgjdbc/pgjdbc/pull/1105 submitted to fix that. The relevant code fragment from org.postgresql.jdbc.PgStatement.java is ... // Enable cursor-based resultset if possible.if (fetchSize > 0 && !wantsScrollableResultSet() && !connection.getAutoCommit()&& !wantsHoldableResultSet()) {flags |= QueryExecutor.QUERY_FORWARD_CURSOR; }And the fragment from org.postgresql.v3.QueryExecutorImpl.java ... boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta && fetchSize > 0 && !describeOnly;
Well testing with last version v42.2.1 I see same behaviour, org.postgresql.jdbc.
private void executeInternal(CachedQuery cachedQuery, ParameterList queryParameters, int flags)
throws SQLException {
closeForNextExecution();
// Enable cursor-based resultset if possible.
if (fetchSize > 0 && !wantsScrollableResultSet() && !connection.getAutoCommit()
&& !wantsHoldableResultSet()) {
flags |= QueryExecutor.QUERY_FORWARD_CURSOR;
}
private void sendOneQuery(SimpleQuery query, SimpleParameterList params, int maxRows,
int fetchSize, int flags) throws IOException {
...
boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta
&& fetchSize > 0 && !describeOnly;
I am helping to migrate a Oracle application to run on Postgresql, and the default holdability setting at oracle is HOLD_CURSOR_OVER_COMMIT so I have tried to set this HOLD_CURSOR_OVER_COMMIT as default holdability setting. Could you explain me why I couldn't use fetchSize in a transaction( autocommit = false ) and keeping those ResultSet available after a commit ? is there a limitation of the jdbc driver or it comes from server side ?
What the application does is something similar to:
postgres=# begin;
BEGIN
edb=# declare p cursor with hold for select * from foo;
DECLARE CURSOR
postgres=# fetch next p;
id
----
1
postgres=# fetch next p;
id
----
2
postgres=# fetch next p;
id
----
3
postgres=# fetch next p;
id
----
4
postgres=# commit;
COMMIT
-- after validating a chunk of data, continue with the next chunk
postgres=# begin;
BEGIN
postgres=# fetch next p;
id
----
5
postgres=# fetch next p;
id
----
6
postgres=# commit;
Regards
2018-02-09 23:09 GMT+01:00 Dave Cramer <pg@fastcrypt.com>:
Jaime,9.4.1208 is considerably out of date. You may want to upgrade.On 8 February 2018 at 19:13, Brad DeJong <bpd0018@gmail.com> wrote:On Wed, Feb 7, 2018 at 1:00 PM, Jaime Soler <jaime.soler@gmail.com> wrote:... ResultSet holdability is HOLD_CURSOR_OVER_COMMIT. ...And the postgresql comunity jdbc driver 9.4 documentation said:"... There are a number of restrictions which will make the driver silently fall back to fetch the whole ResultSet ..."And the statement must be created with a ResultSet holdability of CLOSE_CURSORS_AT_COMMIT - which is not documented. https://github.com/pgjdbc/pgjdbc/pull/1105 submitted to fix that. The relevant code fragment from org.postgresql.jdbc.PgStatement.java is ... // Enable cursor-based resultset if possible.if (fetchSize > 0 && !wantsScrollableResultSet() && !connection.getAutoCommit()&& !wantsHoldableResultSet()) {flags |= QueryExecutor.QUERY_FORWARD_CURSOR; }And the fragment from org.postgresql.v3.QueryExecutorImpl.java ... boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta && fetchSize > 0 && !describeOnly;
On 12 February 2018 at 10:11, Jaime Soler <jaime.soler@gmail.com> wrote:
Without seeing your code I can only direct you to https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/test/java/org/postgresql/test/jdbc2/CursorFetchTest.javaWell testing with last version v42.2.1 I see same behaviour, org.postgresql.jdbc.PgStatement. java and org.postgresql.core.v3. QueryExecutorImpl.java has same condition to use Portal as version 9.4.1208: private void executeInternal(CachedQuery cachedQuery, ParameterList queryParameters, int flags)throws SQLException {closeForNextExecution();// Enable cursor-based resultset if possible.if (fetchSize > 0 && !wantsScrollableResultSet() && !connection.getAutoCommit()&& !wantsHoldableResultSet()) {flags |= QueryExecutor.QUERY_FORWARD_CURSOR; }private void sendOneQuery(SimpleQuery query, SimpleParameterList params, int maxRows,int fetchSize, int flags) throws IOException {...boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta && fetchSize > 0 && !describeOnly;I am helping to migrate a Oracle application to run on Postgresql, and the default holdability setting at oracle is HOLD_CURSOR_OVER_COMMIT so I have tried to set this HOLD_CURSOR_OVER_COMMIT as default holdability setting. Could you explain me why I couldn't use fetchSize in a transaction( autocommit = false ) and keeping those ResultSet available after a commit ? is there a limitation of the jdbc driver or it comes from server side ?What the application does is something similar to:postgres=# begin;BEGINedb=# declare p cursor with hold for select * from foo;DECLARE CURSORpostgres=# fetch next p;id----1postgres=# fetch next p;id----2postgres=# fetch next p;id----3postgres=# fetch next p;id----4postgres=# commit;COMMIT-- after validating a chunk of data, continue with the next chunkpostgres=# begin;BEGINpostgres=# fetch next p;id----5postgres=# fetch next p;id----6postgres=# commit;Regards2018-02-09 23:09 GMT+01:00 Dave Cramer <pg@fastcrypt.com>:Jaime,9.4.1208 is considerably out of date. You may want to upgrade.On 8 February 2018 at 19:13, Brad DeJong <bpd0018@gmail.com> wrote:On Wed, Feb 7, 2018 at 1:00 PM, Jaime Soler <jaime.soler@gmail.com> wrote:... ResultSet holdability is HOLD_CURSOR_OVER_COMMIT. ...And the postgresql comunity jdbc driver 9.4 documentation said:"... There are a number of restrictions which will make the driver silently fall back to fetch the whole ResultSet ..."And the statement must be created with a ResultSet holdability of CLOSE_CURSORS_AT_COMMIT - which is not documented. https://github.com/pgjdbc/pgjdbc/pull/1105 submitted to fix that.
Thanks
The relevant code fragment from org.postgresql.jdbc.PgStatement.java is ... // Enable cursor-based resultset if possible.if (fetchSize > 0 && !wantsScrollableResultSet() && !connection.getAutoCommit()&& !wantsHoldableResultSet()) {flags |= QueryExecutor.QUERY_FORWARD_CURSOR; }And the fragment from org.postgresql.v3.QueryExecutorImpl.java ... boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta && fetchSize > 0 && !describeOnly;
Where we test setFetchSize. If you can provide a self contained test that we can debug that would be useful
Jaime>I am helping to migrate a Oracle application to run on Postgresql, and the default holdability setting at oracle is HOLD_CURSOR_OVER_COMMIT so I have tried to set this HOLD_CURSOR_OVER_COMMIT as default holdability setting
Ok, that makes sense. I mean Oracle serves "fetch across commit" just fine (of course you can hit ORA-01555, but that is another story)
>-- after validating a chunk of data, continue with the next chunk
>postgres=# commit;
>postgres=# fetch next p;
Wait. Did you just make up the output? Does PostgreSQL somehow supports "fetch across commit"?
What is your DB version?
I have no idea why PostgreSQL closes all the cursors as transaction ends, however that is the documented behavior.
42.7.3. Using Cursors -> All portals are implicitly closed at transaction end. Therefore a refcursor value is usable to reference an open cursor only until the end of the transaction.
pgjdbc just tries its best to avoid running into invalid (closed by backend) cursors, and pgjdbc fetches all the contents in HOLD_CURSOR_OVER_COMMIT case.
It might be something to be discussed on pgsql-hackers mailing list.
If only the DB did not close the portals...
Vladimir
Vladimir Sitnikov <sitnikov.vladimir@gmail.com> writes:
> I have no idea why PostgreSQL closes all the cursors as transaction
> ends,
Because the SQL standard says so. If a cursor isn't declared WITH HOLD,
then it's implicitly closed at transaction commit, cf SQL:2011 4.33
"Cursors":
An open cursor that was not defined as a holdable cursor is also
closed by a <commit statement>.
Also, 14.2 <cursor properties> says that WITHOUT HOLD is the default
assumption if you don't explicitly say WITH HOLD.
(Yes, we do support WITH HOLD.)
regards, tom lane
Hi,
2018-02-12 16:45 GMT+01:00 Vladimir Sitnikov <sitnikov.vladimir@gmail.com>:
Jaime>I am helping to migrate a Oracle application to run on Postgresql, and the default holdability setting at oracle is HOLD_CURSOR_OVER_COMMIT so I have tried to set this HOLD_CURSOR_OVER_COMMIT as default holdability settingOk, that makes sense. I mean Oracle serves "fetch across commit" just fine (of course you can hit ORA-01555, but that is another story)>-- after validating a chunk of data, continue with the next chunk>postgres=# commit;>postgres=# fetch next p;Wait. Did you just make up the output?
No. it's a test case on the server database.
Does PostgreSQL somehow supports "fetch across commit"?
Yes, using WITH HOLD.
What is your DB version?
PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bit
I have no idea why PostgreSQL closes all the cursors as transaction ends, however that is the documented behavior.42.7.3. Using Cursors -> All portals are implicitly closed at transaction end. Therefore a refcursor value is usable to reference an open cursor only until the end of the transaction.
Well I am not using PL/PGSQL cursor, but I think postgresql doesn't close portal after commit if you use WITH HOLD.
WITH HOLD specifies that the cursor can continue to be used after the transaction that created it successfully commits. If WITH HOLD is specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session. (But if the creating transaction is aborted, the cursor is removed.) A cursor created with WITH HOLD is closed when an explicit CLOSE command is issued on it, or the session ends. In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions.
pgjdbc just tries its best to avoid running into invalid (closed by backend) cursors, and pgjdbc fetches all the contents in HOLD_CURSOR_OVER_COMMIT case.It might be something to be discussed on pgsql-hackers mailing list.If only the DB did not close the portals...
Don't you think that driver could use Portal and avoid storing all object when setFetchSize was set in a transaction ?
Regards
Vladimir
On 12 February 2018 at 11:08, Jaime Soler <jaime.soler@gmail.com> wrote:
Hi,2018-02-12 16:45 GMT+01:00 Vladimir Sitnikov <sitnikov.vladimir@gmail.com>:Jaime>I am helping to migrate a Oracle application to run on Postgresql, and the default holdability setting at oracle is HOLD_CURSOR_OVER_COMMIT so I have tried to set this HOLD_CURSOR_OVER_COMMIT as default holdability settingOk, that makes sense. I mean Oracle serves "fetch across commit" just fine (of course you can hit ORA-01555, but that is another story)>-- after validating a chunk of data, continue with the next chunk>postgres=# commit;>postgres=# fetch next p;Wait. Did you just make up the output?No. it's a test case on the server database.Does PostgreSQL somehow supports "fetch across commit"?Yes, using WITH HOLD.What is your DB version?PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bitI have no idea why PostgreSQL closes all the cursors as transaction ends, however that is the documented behavior.42.7.3. Using Cursors -> All portals are implicitly closed at transaction end. Therefore a refcursor value is usable to reference an open cursor only until the end of the transaction.Well I am not using PL/PGSQL cursor, but I think postgresql doesn't close portal after commit if you use WITH HOLD.WITH HOLDspecifies that the cursor can continue to be used after the transaction that created it successfully commits.If
WITH HOLDis specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session. (But if the creating transaction is aborted, the cursor is removed.) A cursor created withWITH HOLDis closed when an explicitCLOSEcommand is issued on it, or the session ends. In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions.pgjdbc just tries its best to avoid running into invalid (closed by backend) cursors, and pgjdbc fetches all the contents in HOLD_CURSOR_OVER_COMMIT case.It might be something to be discussed on pgsql-hackers mailing list.If only the DB did not close the portals...Don't you think that driver could use Portal and avoid storing all object when setFetchSize was set in a transaction ?
It does this if you don't use HOLD_CURSOR_OVER_COMMIT.
>(Yes, we do support WITH HOLD.)
Oh, is it something available through extended query protocol?
The documentation says "If successfully created, a named portal object lasts till the end of the current transaction, unless explicitly destroyed", however I just hope it is a documentation bug and one can create a named portal that can survive transaction end.
I don't think explicit "fetch ... from ..." is a workable solution for pgjdbc as it would incur high overhead (both maintenance, and performance):
1) declare ... binary is either full-binary or full text. pgjdbc implements just a subset of binary formats
2) `fetch ...` cannot be prepared, so I expect more network traffic and parse/execute overhead
3) Reimplementing "frontend" from "ExtendedQuery" to "Declare/fetch" is a great amount of work
Vladimir
Vladimir Sitnikov <sitnikov.vladimir@gmail.com> writes:
>> (Yes, we do support WITH HOLD.)
> Oh, is it something available through extended query protocol?
It's available through DECLARE CURSOR WITH HOLD.
regards, tom lane
Tom>It's available through DECLARE CURSOR WITH HOLD.
docs>In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions.
The backend materializes, so it is a sad thing.
docs>In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions.
The backend materializes, so it is a sad thing.
Is there a change "with hold" can be implemented without materialization?
I just wonder if it is more like "not yet implemented" or if it would take incredible amount of resources to implement.
Vladimir
Vladimir
Vladimir Sitnikov <sitnikov.vladimir@gmail.com> writes:
> The backend materializes, so it is a sad thing.
> Is there a change "with hold" can be implemented without materialization?
That would not be an improvement. If we didn't materialize, then we'd
have to retain the locks held by the transaction, as well as keep global
xmin from advancing, so that the rows the cursor still needed to fetch
would not get cleaned by VACUUM nor would the tables containing them get
dropped or significantly altered. Either effect would be awful for
performance if the cursor was held for long. Moreover, keeping the locks
would effectively mean that the transaction wasn't really ended. So if
that's what you want, don't commit the transaction.
regards, tom lane
Tom>So if that's what you want, don't commit the transaction.
What are the options then?
I would argue, users expect cursors to work across commits, and they expect the database to stream the data instead of materializing.
Tom>so that the rows the cursor still needed to fetch
Tom>would not get cleaned by VACUUMThat is understandable.
Tom>If we didn't materialize, then we'd
Tom>have to retain the locks held by the transaction
Tom>have to retain the locks held by the transaction
It is not clear why locks have to retain. Transaction commit should release the locks, shouldn't it?
Tom>nor would the tables containing them get
Tom>dropped or significantly alteredOne does not usually drop a table being selected, but when (s)he does, he does that in production. What I mean is I do not see why "committing a transaction" involves restrictions on table drop/alter.
It looks like all the drop/alter can happen with current implementation (e.g. one connection starts "select", and another connection tries to drop/alter the table). I expect "fetch across commit" to work exactly like a cursor from a separate connection with its own transaction.
----
What if 1000 sessions perform an innocent "select * from big_table" WITH HOLD cursor (e.g. to process the table in a stream fashion)? It would easily fill all the disk space at the server-side.
In my experience, "fetch across commit" is typically used to process data in batches. That is cursor provides IDs to be processed, and the transaction is committed from time to time to avoid the need of doing everything from scratch in case the transaction fails.
Commit is needed, well, to commit, the (partial) work. Of course, locks are released, and I just do not expect for the cursor to dissappear.
Long-running cursor enables to process data serially, and it enables to use just a simple table with no additional indices.
I do understand it is a bad idea to keep long-running "with hold" cursor for a table that is actively changing. However, that would be awful anyway, so I don't expect sane applications to do that kind of thing (even for Oracle DB).
Vladimir
On 9-2-2018 23:09, Dave Cramer wrote: > Jaime, > > 9.4.1208 is considerably out of date. You may want to upgrade. The content of https://jdbc.postgresql.org/documentation/documentation.html suggests that the latest released documentation is for 9.4 (or at least, I always read HEAD as meaning latest development content). Maybe an idea to also add 42.x to that list? Mark > davec@postgresintl.com <mailto:davec@postgresintl.com> > www.postgresintl.com <http://www.postgresintl.com> > > On 8 February 2018 at 19:13, Brad DeJong <bpd0018@gmail.com > <mailto:bpd0018@gmail.com>> wrote: > > > > On Wed, Feb 7, 2018 at 1:00 PM, Jaime Soler <jaime.soler@gmail.com > <mailto:jaime.soler@gmail.com>> wrote: > > ... ResultSet holdability is HOLD_CURSOR_OVER_COMMIT. ... > And the postgresql comunity jdbc driver 9.4 documentation said: > "... There are a number of restrictions which will make the > driver silently fall back to fetch the whole ResultSet ..." -- Mark Rotteveel
Yes, it does appear to be confusing which is why we changed the numbering scheme.
There is very little relationship between JDBC versions and PostgreSQL versions.
On 14 February 2018 at 08:37, Mark Rotteveel <mark@lawinegevaar.nl> wrote:
On 9-2-2018 23:09, Dave Cramer wrote:Jaime,
9.4.1208 is considerably out of date. You may want to upgrade.
The content of https://jdbc.postgresql.org/documentation/documentation.html suggests that the latest released documentation is for 9.4 (or at least, I always read HEAD as meaning latest development content).
Maybe an idea to also add 42.x to that list?
Markdavec@postgresintl.com <mailto:davec@postgresintl.com>
www.postgresintl.com <http://www.postgresintl.com>
On 8 February 2018 at 19:13, Brad DeJong <bpd0018@gmail.com <mailto:bpd0018@gmail.com>> wrote:
On Wed, Feb 7, 2018 at 1:00 PM, Jaime Soler <jaime.soler@gmail.com
<mailto:jaime.soler@gmail.com>> wrote:
... ResultSet holdability is HOLD_CURSOR_OVER_COMMIT. ...
And the postgresql comunity jdbc driver 9.4 documentation said:
"... There are a number of restrictions which will make the
driver silently fall back to fetch the whole ResultSet ..."
--
Mark Rotteveel