SEO

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("jdbcostgresql://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.receiveEr rorResponse(QueryExecutorImpl.java:1531)
at
org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1313)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:188)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eWithFlags(AbstractJdbc2Statement.java:340)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eUpdate(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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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...6/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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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...6/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!
--------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly