Re: 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