Thread: Why are 'select1; commit' run along with 'set autocommit=on' when turning autocommit on again on a jdbc connection

Hi,

I am working with Postgres 7.3 using jdbc driver pg73jdbc3.jar. On acquiring
a jdbc connection, if auto commit is turned off and then turned on again,
with log_statements=true uncommented in postgresql.conf, the following set
of statements are logged in the database log file:

LOG:  query: set datestyle to 'ISO'; select version(), case when
pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
getdatabaseencoding() end;
LOG:  query: set client_encoding = 'UNICODE'; show autocommit
LOG:  query: set autocommit = off;
LOG:  query: select 1;
LOG:  query: commit; set autocommit = on;

Why does the postgres jdbc driver execute 'select 1' and 'commit' followed
by 'set autocommit = on'? Setting autocommit on again should just result in
'set autocommit=on' statement to be executed, not 'select 1' and 'commit'.
User would not expect commit to be issued by just turning autocommit on and
thus its execution here alarms me.

I have included the test class I wrote which takes host, port, dbname, user
and password as command line arguments. I have the pg73jdbc3.jar in my
classpath when I run the test class.

import java.sql.*;

public class TestAutoCommit
{
  public static void main(String[] args) throws SQLException
  {
    if (args.length != 5)
    {
      System.out.println("USAGE: java TestAutoCommit <jdbc_host> <port> <db>
<user> <password>");
      return;
    }

    String host = args[0];
    String port = args[1];
    String db   = args[2];
    String user = args[3];
    String pswd = args[4];

    String connectUrl = "jdbc:postgresql://" + host + ":" + port + "/" + db
+ "?user=" + user + "&password=" + pswd;
    String driverClassName="org.postgresql.Driver";

    System.out.println("Connection Details:\n URL = " + connectUrl +
                       "\n Driver ClassName = " + driverClassName +
                       "\n User = " + user + "\n Password = " + pswd +
"\n");

    try { Class.forName(driverClassName); }
    catch (Exception e)
    {
      System.out.println("Can't register JDBC driver: " + driverClassName +
                         ", Exception: " + e);
    }

    Connection dbCon = DriverManager.getConnection(connectUrl, user, pswd);
    dbCon.setAutoCommit(false);
    dbCon.setAutoCommit(true);
    dbCon.close();
  }
}

Regards,
Vrinda Mushran

Mushran,

I don't remember all the details anymore, but I believe they are in the
mailing list archives somewhere, but I beleive the reason was to prevent
the following:

conn.setAutocommit(false);

<do some work>

conn.setAutocommit(true);
conn.rollback;

If I recall correctly under some circumstances the rollback would
rollback the change to setAutocommit().  So to avoid that, it was
necessary to ensure that no tranaction was in progress when the change
to autoCommit true was done.  So to that end, the select 1; commit; was
used to ensure that after the commit no transaction was in progress.

--Barry

Mushran, Vrinda wrote:
> Hi,
>
> I am working with Postgres 7.3 using jdbc driver pg73jdbc3.jar. On acquiring
> a jdbc connection, if auto commit is turned off and then turned on again,
> with log_statements=true uncommented in postgresql.conf, the following set
> of statements are logged in the database log file:
>
> LOG:  query: set datestyle to 'ISO'; select version(), case when
> pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
> getdatabaseencoding() end;
> LOG:  query: set client_encoding = 'UNICODE'; show autocommit
> LOG:  query: set autocommit = off;
> LOG:  query: select 1;
> LOG:  query: commit; set autocommit = on;
>
> Why does the postgres jdbc driver execute 'select 1' and 'commit' followed
> by 'set autocommit = on'? Setting autocommit on again should just result in
> 'set autocommit=on' statement to be executed, not 'select 1' and 'commit'.
> User would not expect commit to be issued by just turning autocommit on and
> thus its execution here alarms me.
>
> I have included the test class I wrote which takes host, port, dbname, user
> and password as command line arguments. I have the pg73jdbc3.jar in my
> classpath when I run the test class.
>
> import java.sql.*;
>
> public class TestAutoCommit
> {
>   public static void main(String[] args) throws SQLException
>   {
>     if (args.length != 5)
>     {
>       System.out.println("USAGE: java TestAutoCommit <jdbc_host> <port> <db>
> <user> <password>");
>       return;
>     }
>
>     String host = args[0];
>     String port = args[1];
>     String db   = args[2];
>     String user = args[3];
>     String pswd = args[4];
>
>     String connectUrl = "jdbc:postgresql://" + host + ":" + port + "/" + db
> + "?user=" + user + "&password=" + pswd;
>     String driverClassName="org.postgresql.Driver";
>
>     System.out.println("Connection Details:\n URL = " + connectUrl +
>                        "\n Driver ClassName = " + driverClassName +
>                        "\n User = " + user + "\n Password = " + pswd +
> "\n");
>
>     try { Class.forName(driverClassName); }
>     catch (Exception e)
>     {
>       System.out.println("Can't register JDBC driver: " + driverClassName +
>                          ", Exception: " + e);
>     }
>
>     Connection dbCon = DriverManager.getConnection(connectUrl, user, pswd);
>     dbCon.setAutoCommit(false);
>     dbCon.setAutoCommit(true);
>     dbCon.close();
>   }
> }
>
> Regards,
> Vrinda Mushran
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

On Wed, Oct 01, 2003 at 09:23:07AM -0700, Mushran, Vrinda wrote:

> Why does the postgres jdbc driver execute 'select 1' and 'commit' followed
> by 'set autocommit = on'? Setting autocommit on again should just result in
> 'set autocommit=on' statement to be executed, not 'select 1' and 'commit'.
> User would not expect commit to be issued by just turning autocommit on and
> thus its execution here alarms me.

Committing the existing transaction is correct behaviour. The JDBC javadoc
for setAutoCommit includes this:

     NOTE: If this method is called during a transaction, the transaction is committed.

Whether committing when the existing transaction hasn't done any work is
"correct" seems to be a non-issue since it'll always be harmless by
definition.

-O