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
