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,
Pennsylvania19073