Re: Deadlock while using getNotifications() and Statement.executeQuery() - Mailing list pgsql-jdbc

Joao Rui Leal wrote:
> On Tuesday 25 March 2008, Kris Jurka wrote:
>> Still that's not a real clean/understandable design.  Perhaps instead
>> processNotifies() should be added to the public QueryExecutor interface
>> and then AbstractJdbc2Connection can call processNotifies itself so that
>> fetching notifications from protoConnection doesn't require any
>> interaction with the QueryExecutor.
>
> I agree.
>

I've applied the attached patch to CVS back to the 8.1 driver.  The 8.0
driver does not have this problem because it doesn't allow notification
retrieval without executing a query.

Thanks for the report and diagnosis.

Kris Jurka
Index: org/postgresql/core/QueryExecutor.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/core/QueryExecutor.java,v
retrieving revision 1.41
diff -c -r1.41 QueryExecutor.java
*** org/postgresql/core/QueryExecutor.java    8 Jan 2008 06:56:27 -0000    1.41
--- org/postgresql/core/QueryExecutor.java    1 Apr 2008 07:09:38 -0000
***************
*** 170,175 ****
--- 170,184 ----
       */
      Query createParameterizedQuery(String sql); // Parsed for parameter placeholders ('?')

+     /**
+      * Prior to attempting to retrieve notifications, we need to pull
+      * any recently received notifications off of the network buffers.
+      * The notification retrieval in ProtocolConnection cannot do this
+      * as it is prone to deadlock, so the higher level caller must be
+      * responsible which requires exposing this method.
+      */
+     void processNotifies() throws SQLException;
+
      //
      // Fastpath interface.
      //
Index: org/postgresql/core/v2/ProtocolConnectionImpl.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/core/v2/ProtocolConnectionImpl.java,v
retrieving revision 1.11
diff -c -r1.11 ProtocolConnectionImpl.java
*** org/postgresql/core/v2/ProtocolConnectionImpl.java    8 Jan 2008 06:56:27 -0000    1.11
--- org/postgresql/core/v2/ProtocolConnectionImpl.java    1 Apr 2008 07:09:39 -0000
***************
*** 62,68 ****
      }

      public synchronized PGNotification[] getNotifications() throws SQLException {
-         executor.processNotifies();
          PGNotification[] array = (PGNotification[])notifications.toArray(new PGNotification[notifications.size()]);
          notifications.clear();
          return array;
--- 62,67 ----
Index: org/postgresql/core/v3/ProtocolConnectionImpl.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/core/v3/ProtocolConnectionImpl.java,v
retrieving revision 1.12
diff -c -r1.12 ProtocolConnectionImpl.java
*** org/postgresql/core/v3/ProtocolConnectionImpl.java    8 Jan 2008 06:56:27 -0000    1.12
--- org/postgresql/core/v3/ProtocolConnectionImpl.java    1 Apr 2008 07:09:39 -0000
***************
*** 65,71 ****
      }

      public synchronized PGNotification[] getNotifications() throws SQLException {
-         executor.processNotifies();
          PGNotification[] array = (PGNotification[])notifications.toArray(new PGNotification[notifications.size()]);
          notifications.clear();
          return array;
--- 65,70 ----
Index: org/postgresql/jdbc2/AbstractJdbc2Connection.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java,v
retrieving revision 1.48
diff -c -r1.48 AbstractJdbc2Connection.java
*** org/postgresql/jdbc2/AbstractJdbc2Connection.java    19 Feb 2008 06:12:24 -0000    1.48
--- org/postgresql/jdbc2/AbstractJdbc2Connection.java    1 Apr 2008 07:09:39 -0000
***************
*** 1022,1027 ****
--- 1022,1028 ----

      public PGNotification[] getNotifications() throws SQLException
      {
+         getQueryExecutor().processNotifies();
          // Backwards-compatibility hand-holding.
          PGNotification[] notifications = protoConnection.getNotifications();
          return (notifications.length == 0 ? null : notifications);