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

Reply via email to