Thread: interesting side effect of autocommit = off
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
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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am sure the problem is that psql doing a query on startup:
Yeah, and libpq does one too in some cases :-(. Both of these need to
be fixed before 7.3 if possible.
Whether we fix these or not, it'd be a good idea to document that
turning autocommit off in postgresql.conf is not yet well-supported.
I doubt that all client-side code will be happy with that for awhile
yet ...
regards, tom lane
Tom Lane wrote: > Yeah, and libpq does one too in some cases :-(. Both of these need to > be fixed before 7.3 if possible. > > Whether we fix these or not, it'd be a good idea to document that > turning autocommit off in postgresql.conf is not yet well-supported. > I doubt that all client-side code will be happy with that for awhile > yet ... Yup -- here's another example. I was playing around with autocommit off in postgresql.conf to see the effect on dblink. Just now I tried to use pg_dumpall in preparation for an initdb, and got this: $ pg_dumpall > cur.2002.10.14.dmp pg_dump: WARNING: BEGIN: already a transaction in progress pg_dump: could not set transaction isolation level to serializable: ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query pg_dumpall: pg_dump failed on dblink_test_master, exiting Joe