Thread

1. interesting side effect of autocommit = off

   Barry Lind <barry@xythos.com> — 2002-10-14T20:06:44Z

   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
   
   
   
   

2. Re: interesting side effect of autocommit = off

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-14T23:00:15Z

   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, Pennsylvania 19073
   
   

3. Re: interesting side effect of autocommit = off

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-14T23:59:24Z

   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
   
   

4. Re: interesting side effect of autocommit = off

   Joe Conway <mail@joeconway.com> — 2002-10-15T02:00:46Z

   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