interesting side effect of autocommit = off

Barry Lind wrote:

After turning autocommit off on my test database, my cron scripts that
vacuum the database are now failing.

This can be easily reproduced, turn autocommit off in your
postgresql.conf, then launch psql and run a vacuum.

[blind@blind databases]$ psql files
Welcome to psql 7.3b2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

files=# vacuum;
ERROR: VACUUM cannot run inside a BEGIN/END block
files=#

It turns out that you need to commit/rollback first before you can issue
the vacuum command. While I understand why this is happening (psql is
issuing some selects on startup which automatically starts a
transaction) it certainly isn't intuitive.

Does this mean that I need to change my cron scripts to do "rollback;
vacuum;"?

OK, I can reproduce it here, but the issue is only reproducable if you
use autocommit off in postgresql.conf. If you run it interactively as
your first command, it is OK.

I am sure the problem is that psql doing a query on startup:

$ sql -E test
********* QUERY **********
SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'postgres'
**************************

Fortunately, we have an open item for 7.3 for this exact case:

Fix client apps for autocommit = off

and psql is one of them. I was just asking what we need to do to get
this addressed. I think the fix will be in within the next few days.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I am sure the problem is that psql doing a query on startup:

Yeah, and libpq does one too in some cases :-(. Both of these need to
be fixed before 7.3 if possible.

Whether we fix these or not, it'd be a good idea to document that
turning autocommit off in postgresql.conf is not yet well-supported.
I doubt that all client-side code will be happy with that for awhile
yet ...

regards, tom lane

Tom Lane wrote:

Yeah, and libpq does one too in some cases :-(. Both of these need to
be fixed before 7.3 if possible.

Whether we fix these or not, it'd be a good idea to document that
turning autocommit off in postgresql.conf is not yet well-supported.
I doubt that all client-side code will be happy with that for awhile
yet ...

Yup -- here's another example. I was playing around with autocommit off in
postgresql.conf to see the effect on dblink. Just now I tried to use
pg_dumpall in preparation for an initdb, and got this:

$ pg_dumpall > cur.2002.10.14.dmp
pg_dump: WARNING: BEGIN: already a transaction in progress
pg_dump: could not set transaction isolation level to serializable: ERROR:
SET TRANSACTION ISOLATION LEVEL must be called before any query
pg_dumpall: pg_dump failed on dblink_test_master, exiting

Joe