Message-ID: <3E34BE45.6010309@inf.ed.ac.uk>
Date: Mon, 27 Jan 2003 05:06:13 +0000
From: Carwyn Edwards <cedward1@inf.ed.ac.uk>
Reply-To: cedward1@inf.ed.ac.uk
Organization: University of Edinburgh
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3a) Gecko/20021212
MIME-Version: 1.0
To: pgsql-interfaces@postgresql.org
Cc: andre@via.ecp.fr, pgsql-admin@postgresql.org
Subject: Python DB-API  problem with PostgresSQL pgdb.
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit


The Python DB-API (2.0) specifies that on creation a connection object 
is to start a transaction if the database supports them. The pgdb 
implementation of the DB-API does this:

 >>> Begin Listing 1
class pgdbCnx:

         def __init__(self, cnx):
                 self.__cnx = cnx
                 self.__cache = pgdbTypeCache(cnx)
                 try:
                         src = self.__cnx.source()
                         src.execute("BEGIN")
                 except:
                         raise OperationalError, "invalid connection."

 >>> End Listing 1

This is all well and good except there does not seem to be any way of 
NOT starting a transaction block. This is a real problem if you are 
trying to create admin scripts that automate database creation as CREATE 
DATABASE in PostgreSQL can not be called from within a transaction 
block. Listing 2 shows the raw SQL typed into psql that simulates what 
happens from the pgdb module when you try and create a database:


 >>> Begin Listing 2
cedward1=# begin;
BEGIN
cedward1=# create database thingy;
ERROR:  CREATE DATABASE: may not be called in a transaction block

 >>> End Listing 2

I'll let the experts argue as to whether this is a problem for Python's 
DB-API or a PostgreSQL issue.

Carwyn