postgresql-jdbc.git / summary / log / commit / refs
commit f0deabf7d87bb5bffeb84e9cd686eeb632aa9687
Author: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Commit: GitHub <noreply@github.com>
Date: Mon Jul 24 12:57:14 2017 +0000
fix: named statements were used when fetchSize was non-zero and prepareThreshold=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
.../org/postgresql/core/v3/QueryExecutorImpl.java | 9 +--
.../test/jdbc2/ConcurrentStatementFetch.java | 76 ++++++++++++++++++++++
.../org/postgresql/test/jdbc2/Jdbc2TestSuite.java | 1 +
.../test/jdbc2/PreparedStatementTest.java | 67 +++++++++++++++----
4 files changed, 137 insertions(+), 16 deletions(-)
diff --git a/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java b/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java
index 97971f3b..a1885059 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java
@@ -1747,16 +1747,17 @@ public class QueryExecutorImpl extends QueryExecutorBase {
: "Queries that might contain ; must be executed with QueryExecutor.QUERY_EXECUTE_AS_SIMPLE mode. "
+ "Given query is " + query.getNativeSql();
- // nb: if we decide to use a portal (usePortal == true) we must also use a named statement
- // (oneShot == false) as otherwise the portal will be closed under us unexpectedly when
- // the unnamed statement is next reused.
+ // As per "46.2. Message Flow" documentation (quote from 9.1):
+ // If successfully created, a named portal object lasts till the end of the current transaction, unless explicitly destroyed
+ //
+ // That is named portals do not require to use named statements.
boolean noResults = (flags & QueryExecutor.QUERY_NO_RESULTS) != 0;
boolean noMeta = (flags & QueryExecutor.QUERY_NO_METADATA) != 0;
boolean describeOnly = (flags & QueryExecutor.QUERY_DESCRIBE_ONLY) != 0;
boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta
&& fetchSize > 0 && !describeOnly;
- boolean oneShot = (flags & QueryExecutor.QUERY_ONESHOT) != 0 && !usePortal;
+ boolean oneShot = (flags & QueryExecutor.QUERY_ONESHOT) != 0;
boolean noBinaryTransfer = (flags & QUERY_NO_BINARY_TRANSFER) != 0;
boolean forceDescribePortal = (flags & QUERY_FORCE_DESCRIBE_PORTAL) != 0;
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ConcurrentStatementFetch.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ConcurrentStatementFetch.java
new file mode 100644
index 00000000..2040d3ad
--- /dev/null
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ConcurrentStatementFetch.java
@@ -0,0 +1,76 @@
+/*
+ * Copyright (c) 2017, PostgreSQL Global Development Group
+ * See the LICENSE file in the project root for more information.
+ */
+
+package org.postgresql.test.jdbc2;
+
+import org.postgresql.test.TestUtil;
+
+import org.junit.Assert;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.Collection;
+
+@RunWith(Parameterized.class)
+public class ConcurrentStatementFetch extends BaseTest4 {
+
+ private final AutoCommit autoCommit;
+ private final int fetchSize;
+
+ public ConcurrentStatementFetch(AutoCommit autoCommit, int fetchSize, BinaryMode binaryMode) {
+ this.autoCommit = autoCommit;
+ this.fetchSize = fetchSize;
+ setBinaryMode(binaryMode);
+ }
+
+ @Parameterized.Parameters(name = "{index}: fetch(autoCommit={0}, fetchSize={1}, binaryMode={2})")
+ public static Iterable<Object[]> data() {
+ Collection<Object[]> ids = new ArrayList<Object[]>();
+ for (AutoCommit autoCommit : AutoCommit.values()) {
+ for (int fetchSize : new int[]{1, 2, 20}) {
+ for (BinaryMode binaryMode : BinaryMode.values()) {
+ ids.add(new Object[]{autoCommit, fetchSize, binaryMode});
+ }
+ }
+ }
+ return ids;
+ }
+
+ @Override
+ public void setUp() throws Exception {
+ super.setUp();
+ con.setAutoCommit(autoCommit == AutoCommit.YES);
+ }
+
+ @Test
+ public void testFetchTwoStatements() throws Exception {
+ PreparedStatement ps1 = null;
+ PreparedStatement ps2 = null;
+ try {
+ ps1 = con.prepareStatement("select * from generate_series(0, 9)");
+ ps1.setFetchSize(fetchSize);
+ ResultSet rs1 = ps1.executeQuery();
+ ps2 = con.prepareStatement("select * from generate_series(10, 19)");
+ ps2.setFetchSize(fetchSize);
+ ResultSet rs2 = ps2.executeQuery();
+
+ for (int i = 0; i < 10; i++) {
+ Assert.assertTrue(rs1.next());
+ Assert.assertTrue(rs2.next());
+ Assert.assertEquals("Row#" + i + ", resultset 1", i, rs1.getInt(1));
+ Assert.assertEquals("Row#" + i + ", resultset 2", i + 10, rs2.getInt(1));
+ }
+ Assert.assertFalse(rs1.next());
+ Assert.assertFalse(rs2.next());
+ } finally {
+ TestUtil.closeQuietly(ps1);
+ TestUtil.closeQuietly(ps2);
+ }
+ }
+}
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/Jdbc2TestSuite.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/Jdbc2TestSuite.java
index d43cb7a7..17de53f1 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/Jdbc2TestSuite.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/Jdbc2TestSuite.java
@@ -110,6 +110,7 @@ public class Jdbc2TestSuite extends TestSuite {
suite.addTest(new JUnit4TestAdapter(CallableStmtTest.class));
suite.addTest(new JUnit4TestAdapter(CursorFetchTest.class));
+ suite.addTest(new JUnit4TestAdapter(ConcurrentStatementFetch.class));
suite.addTest(new JUnit4TestAdapter(ServerCursorTest.class));
suite.addTest(new JUnit4TestAdapter(IntervalTest.class));
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementTest.java
index 469344a0..8c5e80ab 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementTest.java
@@ -11,6 +11,7 @@ import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
+import org.postgresql.PGStatement;
import org.postgresql.jdbc.PgStatement;
import org.postgresql.jdbc.PreferQueryMode;
import org.postgresql.test.TestUtil;
@@ -77,6 +78,23 @@ public class PreparedStatementTest extends BaseTest4 {
super.tearDown();
}
+ private int getNumberOfServerPreparedStatements(String sql)
+ throws SQLException {
+ PreparedStatement pstmt = null;
+ ResultSet rs = null;
+ try {
+ pstmt = con.prepareStatement(
+ "select count(*) from pg_prepared_statements where statement = ?");
+ pstmt.setString(1, sql);
+ rs = pstmt.executeQuery();
+ rs.next();
+ return rs.getInt(1);
+ } finally {
+ TestUtil.closeQuietly(rs);
+ TestUtil.closeQuietly(pstmt);
+ }
+ }
+
@Test
public void testSetBinaryStream() throws SQLException {
assumeByteaSupported();
@@ -1225,12 +1243,10 @@ public class PreparedStatementTest extends BaseTest4 {
pstmt.executeBatch();
}
pstmt.close();
- pstmt = con.prepareStatement("select count(*) from pg_prepared_statements where statement = 'INSERT INTO batch_tab_threshold5 (id, val) VALUES ($1,$2)'");
- ResultSet rs = pstmt.executeQuery();
- assertTrue(rs.next());
- assertEquals(1, rs.getInt(1));
- rs.close();
- pstmt.close();
+ assertTrue("prepareThreshold=5, so the statement should be server-prepared",
+ ((PGStatement) pstmt).isUseServerPrepare());
+ assertEquals("prepareThreshold=5, so the statement should be server-prepared", 1,
+ getNumberOfServerPreparedStatements("INSERT INTO batch_tab_threshold5 (id, val) VALUES ($1,$2)"));
}
@Test
@@ -1255,12 +1271,39 @@ public class PreparedStatementTest extends BaseTest4 {
pstmt.executeBatch();
}
pstmt.close();
- pstmt = con.prepareStatement("select count(*) from pg_prepared_statements where statement = 'INSERT INTO batch_tab_threshold0 (id, val) VALUES ($1,$2)'");
- ResultSet rs = pstmt.executeQuery();
- assertTrue(rs.next());
- assertEquals(0, rs.getInt(1));
- rs.close();
- pstmt.close();
+
+ assertFalse("prepareThreshold=0, so the statement should not be server-prepared",
+ ((PGStatement) pstmt).isUseServerPrepare());
+ assertEquals("prepareThreshold=0, so the statement should not be server-prepared", 0,
+ getNumberOfServerPreparedStatements("INSERT INTO batch_tab_threshold0 (id, val) VALUES ($1,$2)"));
+ }
+
+ @Test
+ public void testSelectPrepareThreshold0AutoCommitFalseFetchSizeNonZero() throws SQLException {
+ assumeBinaryModeRegular();
+ Assume.assumeTrue("simple protocol only does not support prepared statement requests",
+ preferQueryMode != PreferQueryMode.SIMPLE);
+
+ con.setAutoCommit(false);
+ PreparedStatement pstmt = null;
+ ResultSet rs = null;
+ try {
+ pstmt = con.prepareStatement("SELECT 42");
+ ((PgStatement) pstmt).setPrepareThreshold(0);
+ pstmt.setFetchSize(1);
+ rs = pstmt.executeQuery();
+ rs.next();
+ assertEquals(42, rs.getInt(1));
+ } finally {
+ TestUtil.closeQuietly(rs);
+ TestUtil.closeQuietly(pstmt);
+ }
+
+ assertFalse("prepareThreshold=0, so the statement should not be server-prepared",
+ ((PGStatement) pstmt).isUseServerPrepare());
+
+ assertEquals("prepareThreshold=0, so the statement should not be server-prepared", 0,
+ getNumberOfServerPreparedStatements("SELECT 42"));
}
}
[parent: 8287e7f92f89]