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;"?

thanks,
--Barry



Mon, 14 Oct 2002 13:06:44 -0700
Barry Lind <barry@xythos.com>
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

Mon, 14 Oct 2002 19:00:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: interesting side effect of autocommit = off
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

Mon, 14 Oct 2002 19:59:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: interesting side effect of autocommit = off 
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


Mon, 14 Oct 2002 19:00:46 -0700
Joe Conway <mail@joeconway.com>
Re: interesting side effect of autocommit = off