Two phase for PostgresSQL error - missing prepared transaction

There is a problem with two-phase commit for Postgres. I found that running two transactions with preparing gives error that second prepared transaction doesn't exits when commiting. Connection.recover_twophase() shows that there is no second xa transaction.

Tested on Postgres 8.1.5 on Windows 2000 with sqlalchemy r3558 with Python 2.5

This is error, when we commiting prepared second transaction:

sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) prepared transaction with identifier "_sa_e71bb1a1fa890c866786c5796676329d" does not exist
 'COMMIT PREPARED %(tid)s' {'tid': '_sa_e71bb1a1fa890c866786c5796676329d'}

This reproduces error:

from sqlalchemy import MetaData,Table,Column,Integer,insert
pg = MetaData(bind='postgres://scott:tiger@localhost/test')
table=  Table('Foo', pg, Column('fid',Integer,primary_key=True),Column('val',Integer))
table.create(checkfirst=True)
conn = pg.bind.contextual_connect()

xa = conn.begin_twophase()
### i think this should be there but isn't required ?!
# conn.execute('BEGIN')   
conn.execute(insert(table,values={'val':1}))
xa.prepare()
xa.commit()

xa2 = conn.begin_twophase()
### this solves problem !!!
# conn.execute('BEGIN') 
conn.execute(insert(table,values={'val':2}))
xa2.prepare()
xa2.commit()

### this also solves problem 
# in database.postgres.PGDialect
class PGDialect:
    def do_begin_twophase(self, connection, xid):
        connection.execute(sql.text('BEGIN'))
        self.do_begin(connection.connection)

I think this may be problem with autocommit feature, but I cannot prove that :).

I found that explicty starting transaction resolves that "This command (PREPARE TRANSACTION) must be used inside a transaction block. Use BEGIN to start one." in Notes (http://www.postgresql.org/docs/8.1/static/sql-prepare-transaction.html).

In tests we can reproduce this problem in tests/engine/transaction.py

    def testtwophasetransaction(self):
        connection = testbase.db.connect()

        transaction = connection.begin_twophase()
        connection.execute(users.insert(), user_id=1, user_name='user1')
        transaction.prepare()
        transaction.commit()

        transaction = connection.begin_twophase()
        connection.execute(users.insert(), user_id=2, user_name='user2')
        transaction.prepare() # <-- that was missing, why ? 
        transaction.commit() # <-- this produces exception !

Sacre

Comments (2)