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