Re: [Pypgsql-users] Exception: "VACUUM cannot run inside a BEGIN/END block"

[Billy G. A. <bil...@mu...>]

Adam Buraczewski wrote:

>On Sat, Nov 29, 2003 at 06:13:35PM +0100, Karsten Hilbert wrote:
>  
>
>>cursor.execute('end;vacuum analyze;begin;')
>>we use this successfully around "create database"
>>    
>>
>
>Don't you have a feeling it's a bit ugly? ;-)  DBI, and especially
>cursor objects, weren't invented for DDL commands, only for DMLs.
>Personally, I think that DBI spec should be somehow enhanced so that
>it would cover such situations.  As an acceptable solution I usually
>use plain libpq module here.  The goal can be achieved with following
>code:
>
>	from pyPgSQL import PgSQL
>	conn = PgSQL.connect(...)
>	...
>	conn.conn.query("vacuum analyze")
>
>Of course it will work with pyPgSQL only :^)
>
>Best regards,
>
>  
>
Actually, you can just set autocommit to on (per the DB-API spec it's 
off by default).  If autocommit is on, then pyPgSQL won't wrap the query 
within a begin ... end block.

from pyPgSQL import PgSQL
conn - PgSQL.connect(...)
conn.autocommit = 1
curs = conn.cursor()
curs.execute("vacuum analyze")
  

*Note: *You must set autocommit to 0 /before/ creating any cursors.

Of course, using the libpq.conn.query method as suggested by Adam avoids 
the need to create a cursor object, but you still need to be sure that 
there are no open cursors for the connection when you use (opening a 
connection with autocommit == 0 will open a transaction at cursor 
creations time).