interesting side effect of autocommit = off

From: Barry Lind <barry(at)xythos(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: interesting side effect of autocommit = off
Date: 2002-10-14 20:06:44
Message-ID: 3DAB23D4.4090705@xythos.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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;"?

thanks,
--Barry

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Treat 2002-10-14 20:08:09 Re: [GENERAL] Postgres-based system to run .org registry?
Previous Message David De Graff 2002-10-14 19:42:37 Postgres-based system to run .org registry?