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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly