Skip to content

Two phase for PostgresSQL error - missing prepared transaction #810

Closed
@sqlalchemy-bot

Description

@sqlalchemy-bot
Collaborator

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

sqlalchemy-bot commented on Oct 8, 2007

@sqlalchemy-bot
CollaboratorAuthor

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

sqlalchemy-bot commented on Oct 8, 2007

@sqlalchemy-bot
CollaboratorAuthor

Changes by Anonymous:

  • changed status to closed
added this to the 0.4.xx milestone on Nov 27, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @sqlalchemy-bot

        Issue actions

          Two phase for PostgresSQL error - missing prepared transaction · Issue #810 · sqlalchemy/sqlalchemy