Re: Query preparation - Mailing list pgsql-jdbc

Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>> Heikki Linnakangas wrote:
>>> Does anyone see a problem with caching the result set descriptor
>>> (RowDescription)? AFAICS it should never change after a statement is
>>> prepared. If not, I'll polish up and submit the patch.
>
>> If you tie it to the existing named statement mechanisms I think that
>> works. We invalidate the named statement anyway if the parameter types
>> change, and you want to invalidate any cached row description at the
>> same time. Schema changes could bite you, but I think they bite named
>> statements in other ways anyway. (Not sure how far the server-side
>> efforts to do plan invalidation progressed)
>
> It should be safe.  The server is set up to throw an error if you try to
> re-use a prepared statement whose output tuple descriptor would have
> changed due to schema changes.  So there's a backstop if the driver's
> invalidation logic misses anything.

Here's the patch. Describe message is only sent in the first invocation
of a query. The Field[] array constructed from the RowDescription
message is saved in the SimpleQuery object for reuse.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
Index: org/postgresql/core/v3/QueryExecutorImpl.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/core/v3/QueryExecutorImpl.java,v
retrieving revision 1.43
diff -u -r1.43 QueryExecutorImpl.java
--- org/postgresql/core/v3/QueryExecutorImpl.java    15 Nov 2008 17:48:52 -0000    1.43
+++ org/postgresql/core/v3/QueryExecutorImpl.java    16 Apr 2009 10:43:03 -0000
@@ -894,7 +894,7 @@
         }
     }

-    private void sendDescribePortal(Portal portal) throws IOException {
+    private void sendDescribePortal(SimpleQuery query, Portal portal) throws IOException {
         //
         // Send Describe.
         //
@@ -915,6 +915,9 @@
         if (encodedPortalName != null)
             pgStream.Send(encodedPortalName); // portal name to close
         pgStream.SendChar(0);                 // end of portal name
+
+        pendingDescribePortalQueue.add(query);
+        query.setDescribed(true);
     }

     private void sendDescribeStatement(SimpleQuery query, SimpleParameterList params, boolean describeOnly) throws
IOException{ 
@@ -938,9 +941,10 @@
         pgStream.SendChar(0);                       // end message

         pendingDescribeStatementQueue.add(new Object[]{query, params, new Boolean(describeOnly),
query.getStatementName()});
+        query.setDescribed(true);
     }

-    private void sendExecute(Query query, Portal portal, int limit) throws IOException {
+    private void sendExecute(SimpleQuery query, Portal portal, int limit) throws IOException {
         //
         // Send Execute.
         //
@@ -1071,8 +1075,8 @@
         // A statement describe will also output a RowDescription,
         // so don't reissue it here if we've already done so.
         //
-        if (!noMeta && !describeStatement)
-            sendDescribePortal(portal);
+        if (!noMeta && !describeStatement && !query.isDescribed())
+            sendDescribePortal(query, portal);

         sendExecute(query, portal, rows);
     }
@@ -1159,7 +1163,6 @@
         boolean noResults = (flags & QueryExecutor.QUERY_NO_RESULTS) != 0;
         boolean bothRowsAndStatus = (flags & QueryExecutor.QUERY_BOTH_ROWS_AND_STATUS) != 0;

-        Field[] fields = null;
         Vector tuples = null;

         int len;
@@ -1175,6 +1178,7 @@

         int parseIndex = 0;
         int describeIndex = 0;
+        int describePortalIndex = 0;
         int bindIndex = 0;
         int executeIndex = 0;

@@ -1260,15 +1264,19 @@

                 if (doneAfterRowDescNoData) {
                     Object describeData[] = (Object[])pendingDescribeStatementQueue.get(describeIndex++);
-                    Query currentQuery = (Query)describeData[0];
+                    SimpleQuery currentQuery = (SimpleQuery)describeData[0];

-                    if (fields != null || tuples != null)
+                    Field[] fields = currentQuery.getFields();
+
+                    if (fields != null)
                     { // There was a resultset.
+                        tuples = new Vector();
                         handler.handleResultRows(currentQuery, fields, tuples, null);
-                        fields = null;
                         tuples = null;
                     }
                 }
+                else
+                    describePortalIndex++;
                 break;

             case 's':    // Portal Suspended (end of Execute)
@@ -1281,12 +1289,16 @@

                 {
                     Object[] executeData = (Object[])pendingExecuteQueue.get(executeIndex++);
-                    Query currentQuery = (Query)executeData[0];
+                    SimpleQuery currentQuery = (SimpleQuery)executeData[0];
                     Portal currentPortal = (Portal)executeData[1];
+
+                    Field[] fields = currentQuery.getFields();
+                    if (fields != null && !noResults && tuples == null)
+                        tuples = new Vector();
+
                     handler.handleResultRows(currentQuery, fields, tuples, currentPortal);
                 }

-                fields = null;
                 tuples = null;
                 break;

@@ -1298,13 +1310,16 @@

                 {
                     Object[] executeData = (Object[])pendingExecuteQueue.get(executeIndex++);
-                    Query currentQuery = (Query)executeData[0];
+                    SimpleQuery currentQuery = (SimpleQuery)executeData[0];
                     Portal currentPortal = (Portal)executeData[1];

+                    Field[] fields = currentQuery.getFields();
+                    if (fields != null && !noResults && tuples == null)
+                        tuples = new Vector();
+
                     if (fields != null || tuples != null)
                     { // There was a resultset.
                         handler.handleResultRows(currentQuery, fields, tuples, null);
-                        fields = null;
                         tuples = null;

                         if (bothRowsAndStatus)
@@ -1411,18 +1426,20 @@
                 break;

             case 'T':  // Row Description (response to Describe)
-                fields = receiveFields();
+                Field[] fields = receiveFields();
                 tuples = new Vector();
+
                 if (doneAfterRowDescNoData) {
                     Object describeData[] = (Object[])pendingDescribeStatementQueue.get(describeIndex++);
                     Query currentQuery = (Query)describeData[0];

-                    if (fields != null || tuples != null)
-                    { // There was a resultset.
-                        handler.handleResultRows(currentQuery, fields, tuples, null);
-                        fields = null;
-                        tuples = null;
-                    }
+                    handler.handleResultRows(currentQuery, fields, tuples, null);
+                    tuples = null;
+                }
+                else
+                {
+                    SimpleQuery query = (SimpleQuery)pendingDescribePortalQueue.get(describePortalIndex++);
+                    query.setFields(fields);
                 }
                 break;

@@ -1440,6 +1457,7 @@

                 pendingParseQueue.clear();              // No more ParseComplete messages expected.
                 pendingDescribeStatementQueue.clear();  // No more ParameterDescription messages expected.
+                pendingDescribePortalQueue.clear();     // No more RowDescription messages expected.
                 pendingBindQueue.clear();               // No more BindComplete messages expected.
                 pendingExecuteQueue.clear();            // No more query executions expected.
                 break;
@@ -1688,6 +1706,7 @@
     private final ArrayList pendingBindQueue = new ArrayList(); // list of Portal instances
     private final ArrayList pendingExecuteQueue = new ArrayList(); // list of {SimpleQuery,Portal} object arrays
     private final ArrayList pendingDescribeStatementQueue = new ArrayList(); // list of {SimpleQuery,
SimpleParameterList,Boolean} object arrays 
+    private final ArrayList pendingDescribePortalQueue = new ArrayList(); // list of {SimpleQuery,
SimpleParameterList,Boolean} object arrays 

     private long nextUniqueID = 1;
     private final ProtocolConnectionImpl protoConnection;
Index: org/postgresql/core/v3/SimpleQuery.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/core/v3/SimpleQuery.java,v
retrieving revision 1.13
diff -u -r1.13 SimpleQuery.java
--- org/postgresql/core/v3/SimpleQuery.java    30 Sep 2008 23:41:23 -0000    1.13
+++ org/postgresql/core/v3/SimpleQuery.java    16 Apr 2009 10:43:03 -0000
@@ -104,6 +104,20 @@
         return encodedStatementName;
     }

+    void setFields(Field[] fields) {
+        this.fields = fields;
+    }
+    Field[] getFields() {
+        return fields;
+    }
+    // Have we sent a Describe (portal or statement) message for this query yet?
+    boolean isDescribed() {
+        return described;
+    }
+    void setDescribed(boolean described) {
+        this.described = described;
+    }
+
     void setCleanupRef(PhantomReference cleanupRef) {
         if (this.cleanupRef != null) {
             this.cleanupRef.clear();
@@ -122,12 +136,16 @@

         statementName = null;
         encodedStatementName = null;
+        fields = null;
+        described = false;
     }

     private final String[] fragments;
     private final ProtocolConnectionImpl protoConnection;
     private String statementName;
     private byte[] encodedStatementName;
+    private Field[] fields;
+    private boolean described;
     private PhantomReference cleanupRef;
     private int[] preparedTypes;