Comments

edited

Edited 1 time

f0def edited Jul 21, 2017 (most recent)

I use PgBouncer with pool_mode=transaction. To work in that mode I had to install prepareThreshold=0 in the connection string as described in the FAQ.

I'm using Spring Framework + Hibernate with his @Transactional annotation. This annotation cause to switch off the autoCommit and make the commit at the end of the transaction.

I execute the request and I know that the query will return a lot of data (thousand or millions of records). I want it to work quickly, so I get the prepared statement and modify the fetchSize, e.g. fetchSize=10000.

If you use these three parameters simultaneously:

prepareThreshold=0

autoCommit=false

fetchSize>0

This leads to error from PgBouncer, he answers with ERROR: prepared statement "S_1" does not exist.

I think, the problem is here org.postgresql.core.v3.QueryExecutorImpl:

This comment has been minimized.

On 20 July 2017 at 11:36, f0def ***@***.***> wrote:
Similar to #742 <#742>
I use PgBouncer <https://github.com/pgbouncer/pgbouncer> with
pool_mode=transaction. To work in that mode I had to install
prepareThreshold=0 in the connection string as described in the FAQ
<https://pgbouncer.github.io/faq.html#how-to-use-prepared-statements-with-session-pooling>
.
I'm using Spring Framework + Hibernate with his @transactional
annotation. This annotation cause to switch off the autoCommit and make
the commit at the end of the transaction.
I execute the request and I know that the query will return a lot of data
(thousand or millions of records). I want it to work quickly, so I get the
prepared statement and modify the fetchSize, e.g. fetchSize=10000.
If you use these three parameters simultaneously:
- prepareThreshold=0
- autoCommit=false
- fetchSize>0
This leads to error from PgBouncer, he answers with ERROR: prepared
statement “S_1” already exists.
I think, the problem is here org.postgresql.core.v3.QueryExecutorImpl:
boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta
&& fetchSize > 0 && !describeOnly;boolean oneShot = (flags & QueryExecutor.QUERY_ONESHOT) != 0 && !usePortal;
usePortal is true because I use fetchSize
oneShot is false because usePortal is true
but I set prepareThreshold to 0 and I don't want to store statement
Here is the test that describe the problem
@test
public void testBatchWithPrepareThreshold0AutoCommitFalseFetchSizeNonZero() throws SQLException {
assumeBinaryModeRegular();
Assume.assumeTrue("simple protocol only does not support prepared statement requests",
preferQueryMode != PreferQueryMode.SIMPLE);
con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement("SELECT 1");
((PgStatement) pstmt).setPrepareThreshold(0);
pstmt.setFetchSize(1);
pstmt.executeQuery();
pstmt.close();
pstmt = con.prepareStatement("select count(*) from pg_prepared_statements where statement = 'SELECT 1'");
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
// Failed here:
assertEquals(0, rs.getInt(1));
rs.close();
pstmt.close();
}
Tested on pgjdbc 42.1.3
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
<#869>, or mute the thread
<https://github.com/notifications/unsubscribe-auth/AAYz9iuI-a8IHEnnWQy3DQStIcG0I6D2ks5sP54jgaJpZM4OegVx>
.

On 20 July 2017 at 11:36, f0def ***@***.***> wrote:
Similar to #742 <#742>
I use PgBouncer <https://github.com/pgbouncer/pgbouncer> with
pool_mode=transaction. To work in that mode I had to install
prepareThreshold=0 in the connection string as described in the FAQ
<https://pgbouncer.github.io/faq.html#how-to-use-prepared-statements-with-session-pooling>
.
I'm using Spring Framework + Hibernate with his @transactional
annotation. This annotation cause to switch off the autoCommit and make
the commit at the end of the transaction.
I execute the request and I know that the query will return a lot of data
(thousand or millions of records). I want it to work quickly, so I get the
prepared statement and modify the fetchSize, e.g. fetchSize=10000.
If you use these three parameters simultaneously:
- prepareThreshold=0
- autoCommit=false
- fetchSize>0
This leads to error from PgBouncer, he answers with ERROR: prepared
statement “S_1” already exists.
I think, the problem is here org.postgresql.core.v3.QueryExecutorImpl:
boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta
&& fetchSize > 0 && !describeOnly;boolean oneShot = (flags & QueryExecutor.QUERY_ONESHOT) != 0 && !usePortal;
usePortal is true because I use fetchSize
oneShot is false because usePortal is true
but I set prepareThreshold to 0 and I don't want to store statement
Here is the test that describe the problem
@test
public void testBatchWithPrepareThreshold0AutoCommitFalseFetchSizeNonZero() throws SQLException {
assumeBinaryModeRegular();
Assume.assumeTrue("simple protocol only does not support prepared statement requests",
preferQueryMode != PreferQueryMode.SIMPLE);
con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement("SELECT 1");
((PgStatement) pstmt).setPrepareThreshold(0);
pstmt.setFetchSize(1);
pstmt.executeQuery();
pstmt.close();
pstmt = con.prepareStatement("select count(*) from pg_prepared_statements where statement = 'SELECT 1'");
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
// Failed here:
assertEquals(0, rs.getInt(1));
rs.close();
pstmt.close();
}
Tested on pgjdbc 42.1.3
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
<#869>, or mute the thread
<https://github.com/notifications/unsubscribe-auth/AAYz9iuI-a8IHEnnWQy3DQStIcG0I6D2ks5sP54jgaJpZM4OegVx>
.

This comment has been minimized.

off the top of my head isn't this exactly the same as the un-named statement? I guess the biggest difference is that we currently transfer in binary once we used a named statement, but of course if we are going to invalidate then is there any point in doing the describe as well ? I think we need to deal with connection pools, but it seems to me the easiest way is simply not to use named statements?

off the top of my head isn't this exactly the same as the un-named statement? I guess the biggest difference is that we currently transfer in binary once we used a named statement, but of course if we are going to invalidate then is there any point in doing the describe as well ? I think we need to deal with connection pools, but it seems to me the easiest way is simply not to use named statements?

This comment has been minimized.

my guess is this is highly dependent on the query in question. At the risk of questionable sanity it might be useful to have a "connection pool" set of config parameters which would allow the user to configure this as they see fit ? I imagine one could construct a benchmark which would show both gains and losses from using named statements, so this one falls in the category of letting the user decide what they want to do.

my guess is this is highly dependent on the query in question. At the risk of questionable sanity it might be useful to have a "connection pool" set of config parameters which would allow the user to configure this as they see fit ? I imagine one could construct a benchmark which would show both gains and losses from using named statements, so this one falls in the category of letting the user decide what they want to do.

This comment has been minimized.

I imagine one could construct a benchmark which would show both gains and losses from using named statements

The thing is no current pooler supports named statements. So the users who sit behind poolers just have to use unnamed statements always.
Then we could implement a transaction-wide named statements, so those who use transaction-scoped poolers could leverage named statements.

Of course one can always disable named statements, however it looks like the option to "enable" is missing for pgbouncer-like environment.

I imagine one could construct a benchmark which would show both gains and losses from using named statements

The thing is no current pooler supports named statements. So the users who sit behind poolers just have to use unnamed statements always.
Then we could implement a transaction-wide named statements, so those who use transaction-scoped poolers could leverage named statements.

Of course one can always disable named statements, however it looks like the option to "enable" is missing for pgbouncer-like environment.

…reThreshold=0 (#870)
Non-zero fetchSize triggers use of named portals (for subsequent fetch requests),
however named portals does not require to use named statements.
As per PostgreSQL documentation, named portals are automatically closed as transaction completes, so named portals should play well with transaction-based poolers.
fixes#869

PostgreSQL 8.2 is known to close named portals unexpectedly, so we just ignore the test.
The following scenario might be affected: old backend version + setFetchSize + interleaved ResultSet processing
This is a follow-up for #870 and #869 for PostgreSQL < 8.4

PostgreSQL 8.2 is known to close named portals unexpectedly, so we just ignore the test.
The following scenario might be affected: old backend version + setFetchSize + interleaved ResultSet processing
This is a follow-up for #870 and #869 for PostgreSQL < 8.4

…reThreshold=0 (#870)
Non-zero fetchSize triggers use of named portals (for subsequent fetch requests),
however named portals does not require to use named statements.
As per PostgreSQL documentation, named portals are automatically closed as transaction completes, so named portals should play well with transaction-based poolers.
fixes#869

PostgreSQL 8.2 is known to close named portals unexpectedly, so we just ignore the test.
The following scenario might be affected: old backend version + setFetchSize + interleaved ResultSet processing
This is a follow-up for #870 and #869 for PostgreSQL < 8.4