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(at)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073