Thread: Implicit autocommit?

I am using postgresql-8.1-408.jdbc3.jar w/ PostgreSQL 8.1.8 on Kubuntu
Linux.

Given the following testcase (note autocommit is disabled)...

--------- BEGIN SOURCE -----------
import java.sql.*;

public class VacuumTest
{
   public static void main(String[] args)
   throws SQLException
   {
      Connection conn = null;
      try
      {
         Class.forName("org.postgresql.Driver");
         conn =
DriverManager.getConnection("jdbc:postgresql://localhost/test");
         conn.setAutoCommit(false);

         update(conn, "create temp table tt1 (c1 int4)");
         update(conn, "vacuum tt1");
         update(conn, "drop table tt1");
      }
      catch (Exception exc)
      {
         exc.printStackTrace();
      }
      finally
      {
         if (conn != null)
         {
            conn.close();
         }
      }
   }

   private static void update(Connection conn, String sql)
   throws SQLException
   {
      Statement stmt = conn.createStatement();
      try
      {
         stmt.executeUpdate(sql);
      }
      finally
      {
         stmt.close();
      }
   }
}
---------- END SOURCE ------------

...I see the following result:

org.postgresql.util.PSQLException: ERROR: VACUUM cannot run inside a
transaction block
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1531)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1313)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:340)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:286)
        at VacuumTest.update(VacuumTest.java:38)
        at VacuumTest.main(VacuumTest.java:16)

I debugged into the driver and found it was issuing a "BEGIN" to the
backend just before each statement.  Why would it do this with
autocommit disabled?

Thanks,
Eric Faulhaber

Eric Faulhaber wrote:

> I debugged into the driver and found it was issuing a "BEGIN" to the
> backend just before each statement.  Why would it do this with
> autocommit disabled?

autocommit on = implicit transaction wrapping each individual statement.
In the postgres world, this means "don't use BEGIN/COMMIT at all".

autocommit off = explicit transaction demarcation, the first statement
executed starts a new transaction that lasts until rollback()/commit()
are called. In the postgres world, this means "use BEGIN/ROLLBACK/COMMIT
to demarcate the transaction". So the driver issues a BEGIN to start a
new transaction as necessary.

-O

Oliver Jowett wrote:
> Eric Faulhaber wrote:
>
>> I debugged into the driver and found it was issuing a "BEGIN" to the
>> backend just before each statement.  Why would it do this with
>> autocommit disabled?
>
> autocommit on = implicit transaction wrapping each individual
> statement. In the postgres world, this means "don't use BEGIN/COMMIT
> at all".
>
> autocommit off = explicit transaction demarcation, the first statement
> executed starts a new transaction that lasts until rollback()/commit()
> are called. In the postgres world, this means "use
> BEGIN/ROLLBACK/COMMIT to demarcate the transaction". So the driver
> issues a BEGIN to start a new transaction as necessary.
>
> -O
Unless I misunderstand your answer, this suggests that vacuum cannot be
run via JDBC, since it cannot be run within a transaction block.  It is
my understanding that autovacuum skips temp tables:

http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php

This is consistent with my experience.  So, how can I vacuum a
long-lived temp table created with a JDBC connection?

Thanks,
Eric Faulhaber

On Sunday 15 July 2007 13:34:30 Eric Faulhaber wrote:
> Unless I misunderstand your answer, this suggests that vacuum cannot be
> run via JDBC, since it cannot be run within a transaction block.  

Methinks you did misunderstand Oliver; if you use setAutoCommit(true) there
will be no transaction block at all (the name is a bit confusing: autocommit
true means there are effectively no commit statements send. At least by the
pgsql driver). Don't know what that means for your temptables though; if they
are transaction scoped you're probably SOL, but from your example it seems
you're using session scoped temp tables, so that should work.

> It is
> my understanding that autovacuum skips temp tables:
>
> http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php
>
> This is consistent with my experience.  So, how can I vacuum a
> long-lived temp table created with a JDBC connection?
>
> Thanks,
> Eric Faulhaber

jan

--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

Jan de Visser wrote:
> On Sunday 15 July 2007 13:34:30 Eric Faulhaber wrote:
>
>> Unless I misunderstand your answer, this suggests that vacuum cannot be
>> run via JDBC, since it cannot be run within a transaction block.
>>
>
> Methinks you did misunderstand Oliver; if you use setAutoCommit(true) there
> will be no transaction block at all (the name is a bit confusing: autocommit
> true means there are effectively no commit statements send. At least by the
> pgsql driver). Don't know what that means for your temptables though; if they
> are transaction scoped you're probably SOL, but from your example it seems
> you're using session scoped temp tables, so that should work.
>
>
Indeed I did misunderstand.  Setting autocommit to true allows the
vacuum to proceed.

Thanks to both of you for your help!

Regards,
Eric Faulhaber