postgresql-jdbc.git / summary / log / commit / refs

commit    17c8afb11c319255aeffc93617545181d20400a3
Author:   Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
Date:     Sun Aug 14 09:34:51 2016 +0000

    fix: invalidate prepared statement cache when "set search_path=..." query is detected
    
    fixes #496


 .../org/postgresql/core/v3/QueryExecutorImpl.java  | 19 ++++++++++
 .../postgresql/test/jdbc4/jdbc41/SchemaTest.java   | 41 ++++++++++++++++++++++
 2 files changed, 60 insertions(+)

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 3abc2bde..45143df0 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java
@@ -95,6 +95,13 @@ public class QueryExecutorImpl extends QueryExecutorBase {
 
   private short deallocateEpoch;
 
+  /**
+   * This caches the latest observed {@code set search_path} query so the reset of prepared
+   * statement cache can be skipped if using repeated calls for the same {@code set search_path}
+   * value.
+   */
+  private String lastSetSearchPathQuery;
+
   public QueryExecutorImpl(PGStream pgStream, String user, String database,
       int cancelSignalTimeout, Properties info, Logger logger) throws SQLException, IOException {
     super(logger, pgStream, user, database, cancelSignalTimeout, info);
@@ -2042,6 +2049,18 @@ public class QueryExecutorImpl extends QueryExecutorBase {
           SimpleQuery currentQuery = executeData.query;
           Portal currentPortal = executeData.portal;
 
+          if (status.startsWith("SET")) {
+            String nativeSql = currentQuery.getNativeQuery().nativeSql;
+            // Scan only the first 1024 characters to
+            // avoid big overhead for long queries.
+            if (nativeSql.lastIndexOf("search_path", 1024) != -1
+                && !nativeSql.equals(lastSetSearchPathQuery)) {
+              // Search path was changed, invalidate prepared statement cache
+              lastSetSearchPathQuery = nativeSql;
+              deallocateEpoch++;
+            }
+          }
+
           if (!executeData.asSimple) {
             pendingExecuteQueue.removeFirst();
           } else {
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc4/jdbc41/SchemaTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc4/jdbc41/SchemaTest.java
index 975a32fa..25d5daa5 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc4/jdbc41/SchemaTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc4/jdbc41/SchemaTest.java
@@ -13,8 +13,12 @@ import org.postgresql.test.TestUtil;
 import junit.framework.TestCase;
 
 import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.sql.Types;
 import java.util.Properties;
 
 public class SchemaTest extends TestCase {
@@ -46,9 +50,12 @@ public class SchemaTest extends TestCase {
     TestUtil.createTable(_conn, "schema1.table1", "id integer");
     TestUtil.createTable(_conn, "schema2.table2", "id integer");
     TestUtil.createTable(_conn, "\"UpperCase\".table3", "id integer");
+    TestUtil.createTable(_conn, "schema1.sptest", "id integer");
+    TestUtil.createTable(_conn, "schema2.sptest", "id varchar");
   }
 
   protected void tearDown() throws SQLException {
+    _conn.setAutoCommit(true);
     _conn.setSchema(null);
     Statement stmt = _conn.createStatement();
     if (dropUserSchema) {
@@ -179,4 +186,38 @@ public class SchemaTest extends TestCase {
     }
   }
 
+  public void testSearchPathPreparedStatementAutoCommitFalse() throws SQLException {
+    _conn.setAutoCommit(false);
+    testSearchPathPreparedStatement();
+  }
+
+  public void testSearchPathPreparedStatementAutoCommitTrue() throws SQLException {
+    testSearchPathPreparedStatement();
+  }
+
+  public void testSearchPathPreparedStatement() throws SQLException {
+    execute("set search_path to schema1,public");
+    PreparedStatement ps = _conn.prepareStatement("select * from sptest");
+    for (int i = 0; i < 10; i++) {
+      ps.execute();
+    }
+    assertColType(ps, "sptest should point to schema1.sptest, thus column type should be INT",
+        Types.INTEGER);
+    ps.close();
+    execute("set search_path to schema2,public");
+    ps = _conn.prepareStatement("select * from sptest");
+    assertColType(ps, "sptest should point to schema2.sptest, thus column type should be VARCHAR",
+        Types.VARCHAR);
+    ps.close();
+  }
+
+  private void assertColType(PreparedStatement ps, String message, int expected) throws SQLException {
+    ResultSet rs = ps.executeQuery();
+    ResultSetMetaData md = rs.getMetaData();
+    int columnType = md.getColumnType(1);
+    assertEquals(message,
+        expected, columnType);
+    rs.close();
+  }
+
 }


[parent: aa53fbae604a]