Description
Migrated issue, originally created by Anonymous
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
Activity
sqlalchemy-bot commentedon Oct 8, 2007
Anonymous wrote:
(original author: ants) Fixed in 47d3f45 and c931b9d. The custom commit/rollback implementations of two-phase transactions didn't end up with a new transaction context as the regular dbapi implementations do.
sqlalchemy-bot commentedon Oct 8, 2007
Changes by Anonymous: