[DB-SIG] DBAPI two phase commit implementation in psycopg2

M.-A. Lemburg mal at egenix.com
Fri Sep 24 10:48:08 CEST 2010

Daniele Varrazzo wrote:
> Hello,
> I've recently joined the db-sig ML, and I've read the threads about
> the two phase commit interface design of Jan 2008.
> I'd like to implement the DBAPI TPC extension in psycopg2: I'm
> considering the best way to overcome the slight model difference
> between the XA-inspired DBAPI and the PostgreSQL commands.
> The DBAPI xid structure has members (format_id, gtrid, bqual). In
> postgresql PREPARE TRANSACTION only takes a string "tid". So it will
> be the driver's responsibility to map between the xid triple and the
> tid string. I may come out with a separator (e.g. "|") and concatenate
> the three parts into a tid, escaping the separator. On the other way
> round, it wouldn't be a problem to perform the inverse split, but I
> should take in consideration transactions whose tid doesn't follow the
> pattern (e.g. created by a non-XA-oriented application) and is just
> composed by a string. tpc_recover would then create xid with format_id
> = 0 and bqual = "", that seem reasonable default values reading the XA
> specs.

Both sound like reasonable ways to map the xid requirements onto
PGs single string approach. I'd set the branch qualifier to something
like 'pgsql' or the database name, since each resource in a global
transaction should have its own branch qualifier.

I'd also look around to check how other tools that interoperate with
PG in two-phase commits handle this. XA is a widely used standard
in the industry, so I assume the problem must have popped up
elsewhere as well.

Note that the TM will usually create the xid and only the TM has
to be able to recognize its own xids for the purpose of managing
different transactions.

> Is postgres the only database with this xid mapping issue? If other
> dbs have similar issues, how is the xid - string mapping usually
> performed?

All the big ones (Oracle, DB2, Sybase, etc.) use XA for this.

> Any other suggestion about the matter would be appreciated. Thank you.

Some references:

The XA spec:

Marc-Andre Lemburg

Professional Python Services directly from the Source  (#1, Sep 24 2010)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::

   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611

More information about the DB-SIG mailing list