Connection Properties for FetchSize and Autocommit

[David Langton]

Hi pgjdbc coders,

I recently had to deal with a 3rd party application (without source code) that processed large result sets, and kept running out of memory on the client.  I realise this was because the application didn't set a fetch size, nor turn off autocommit and so the criteria for the driver to use a cursor-based result set were not met.

Since I cannot modify the application, I instead altered the postgresql jdbc driver instead to allow two additional driver parameters - defaultFetchSize and defaultAutoCommit to override the built-in defaults (0 and true).

Anyway, it worked well for me, so for what its worth a diff is attached.  All test cases still pass. If you think this is worth pursuing, let me know what else needs to be done!

David Langton


? build.local.properties
Index: org/postgresql/Driver.java.in
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/Driver.java.in,v
retrieving revision 1.78
diff -u -r1.78 Driver.java.in
--- org/postgresql/Driver.java.in 2 Jun 2009 00:22:58 -0000 1.78
+++ org/postgresql/Driver.java.in 8 Oct 2009 19:55:18 -0000
@@ -459,7 +459,11 @@
                 { "kerberosServerName", Boolean.FALSE,
                   "The Kerberos service name to use when authenticating with GSSAPI.  This is equivalent to libpq's PGKRBSRVNAME environment variable." },
                 { "jaasApplicationName", Boolean.FALSE,
-                  "Specifies the name of the JAAS system or application login configuration." }
+                  "Specifies the name of the JAAS system or application login configuration." },
+                { "defaultFetchSize", Boolean.FALSE,
+                  "Specifies an integer fetchSize to use as a default on all new Statement instances. A better approach is to modify the calling application to set an appropriate size - this parameter is for situations where this is not possible." },
+                { "defaultAutoCommit", Boolean.FALSE,
+                  "Sets the autocommit state for all new Connections." }
             };
 
     /**
Index: org/postgresql/jdbc2/AbstractJdbc2Connection.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java,v
retrieving revision 1.52
diff -u -r1.52 AbstractJdbc2Connection.java
--- org/postgresql/jdbc2/AbstractJdbc2Connection.java 1 Jul 2009 05:00:40 -0000 1.52
+++ org/postgresql/jdbc2/AbstractJdbc2Connection.java 8 Oct 2009 19:55:19 -0000
@@ -65,6 +65,7 @@
     protected int prepareThreshold;
     // Connection's autocommit state.
     public boolean autoCommit = true;
+    public boolean defaultAutoCommit = true;
     // Connection's readonly state.
     public boolean readOnly = false;
 
@@ -76,6 +77,9 @@
 
     public abstract DatabaseMetaData getMetaData() throws SQLException;
 
+    //default fetch size for new statementinstances
+    public int defaultFetchSize = 0;
+
     //
     // Ctor.
     //
@@ -117,6 +121,31 @@
         {
         }
 
+        //setup a default fetch size
+        try
+        {
+            this.defaultFetchSize = Integer.parseInt( info.getProperty( "defaultFetchSize", "0" ) );
+        }
+        catch (NumberFormatException nfe)
+        {
+            logger.debug("defaultFetchSize property was not an integer");
+        }
+
+        //override default autocommit state
+        try
+        {
+            this.defaultAutoCommit = Boolean.parseBoolean( info.getProperty( "defaultAutoCommit", "true" ) );
+        }
+        catch (Exception ace)
+        {
+            logger.debug("defaultAutoCommit property could not be parsed as a boolean");
+        }
+
+        if( this.defaultAutoCommit != this.autoCommit )
+        {
+            setAutoCommit( this.defaultAutoCommit );
+ }
+
         //Print out the driver version number
         if (logger.logInfo())
             logger.info(Driver.getVersion());
Index: org/postgresql/jdbc3/Jdbc3Connection.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc3/Jdbc3Connection.java,v
retrieving revision 1.12
diff -u -r1.12 Jdbc3Connection.java
--- org/postgresql/jdbc3/Jdbc3Connection.java 8 Jan 2008 06:56:29 -0000 1.12
+++ org/postgresql/jdbc3/Jdbc3Connection.java 8 Oct 2009 19:55:20 -0000
@@ -28,6 +28,7 @@
     {
         Jdbc3Statement s = new Jdbc3Statement(this, resultSetType, resultSetConcurrency, resultSetHoldability);
         s.setPrepareThreshold(getPrepareThreshold());
+        s.setFetchSize( this.defaultFetchSize );
         return s;
     }
 
@@ -36,6 +37,7 @@
     {
         Jdbc3PreparedStatement s = new Jdbc3PreparedStatement(this, sql, resultSetType, resultSetConcurrency, resultSetHoldability);
         s.setPrepareThreshold(getPrepareThreshold());
+        s.setFetchSize( this.defaultFetchSize );
         return s;
     }
 
Index: org/postgresql/jdbc3g/Jdbc3gConnection.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc3g/Jdbc3gConnection.java,v
retrieving revision 1.6
diff -u -r1.6 Jdbc3gConnection.java
--- org/postgresql/jdbc3g/Jdbc3gConnection.java 8 Oct 2008 18:24:05 -0000 1.6
+++ org/postgresql/jdbc3g/Jdbc3gConnection.java 8 Oct 2009 19:55:20 -0000
@@ -28,6 +28,7 @@
     {
         Jdbc3gStatement s = new Jdbc3gStatement(this, resultSetType, resultSetConcurrency, resultSetHoldability);
         s.setPrepareThreshold(getPrepareThreshold());
+        s.setFetchSize( this.defaultFetchSize );
         return s;
     }
 
@@ -36,6 +37,7 @@
     {
         Jdbc3gPreparedStatement s = new Jdbc3gPreparedStatement(this, sql, resultSetType, resultSetConcurrency, resultSetHoldability);
         s.setPrepareThreshold(getPrepareThreshold());
+        s.setFetchSize( this.defaultFetchSize );
         return s;
     }
 
Index: org/postgresql/jdbc4/Jdbc4Connection.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc4/Jdbc4Connection.java,v
retrieving revision 1.2
diff -u -r1.2 Jdbc4Connection.java
--- org/postgresql/jdbc4/Jdbc4Connection.java 8 Jan 2008 06:56:30 -0000 1.2
+++ org/postgresql/jdbc4/Jdbc4Connection.java 8 Oct 2009 19:55:20 -0000
@@ -28,6 +28,8 @@
     {
         Jdbc4Statement s = new Jdbc4Statement(this, resultSetType, resultSetConcurrency, resultSetHoldability);
         s.setPrepareThreshold(getPrepareThreshold());
+        s.setFetchSize( this.defaultFetchSize );
+
         return s;
     }
 
@@ -36,6 +38,7 @@
     {
         Jdbc4PreparedStatement s = new Jdbc4PreparedStatement(this, sql, resultSetType, resultSetConcurrency, resultSetHoldability);
         s.setPrepareThreshold(getPrepareThreshold());
+        s.setFetchSize( this.defaultFetchSize );
         return s;
     }
 


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Oliver Jowett]

David Langton wrote:
> defaultAutoCommit

That seems a pretty dangerous setting to have :/

-O

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[David Langton]

> David Langton wrote:
>>
>> defaultAutoCommit
>
> That seems a pretty dangerous setting to have :/
>
> -O
>

I'm not sure!  Without this parameter, you get a connection in
autocommit mode anyway.  If you actually set the mode yourself you get
whatever it is you want.  This parameter simply allows you to
initially set autocommit to false in the url for those (rare)
situations where it is what you want, but you can't modify the calling
application.

However, I'm certainly not any sort of JDBC expert, it just helped me
out a lot to be able to do this (along with fetchsize) to avoid
getting the whole result set sent to the driver all at once.  If I
could alter the application, I would have (and that would have been a
better place to do it!).

Regards

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Oliver Jowett]

David Langton wrote:

>> David Langton wrote:
>>> defaultAutoCommit
>> That seems a pretty dangerous setting to have :/
>>
>> -O
>>
>
> I'm not sure!  Without this parameter, you get a connection in
> autocommit mode anyway.  If you actually set the mode yourself you get
> whatever it is you want.  This parameter simply allows you to
> initially set autocommit to false in the url for those (rare)
> situations where it is what you want, but you can't modify the calling
> application.
>
> However, I'm certainly not any sort of JDBC expert, it just helped me
> out a lot to be able to do this (along with fetchsize) to avoid
> getting the whole result set sent to the driver all at once.  If I
> could alter the application, I would have (and that would have been a
> better place to do it!).

The issue I see is that it can cause silent data loss. An application
that expects the documented API default - autocommit on - and just does
some UPDATEs is going to throw away all its changes if autocommit is
mysteriously off by default, because it's never going to call commit().

-O

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[David Langton]

>
> The issue I see is that it can cause silent data loss. An application that
> expects the documented API default - autocommit on - and just does some
> UPDATEs is going to throw away all its changes if autocommit is mysteriously
> off by default, because it's never going to call commit().
>
> -O
>

Yes, of course you are right and it would be very bad indeed.  I guess
I wasn't thinking of the impact properly (the app I am working with
isn't doing updates).

Maybe I'll revisit this again one day, but think about it a bit more first!

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc