Re: Why are 'select1; commit' run along with 'set autocommit=on'

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
>