XA support (distributed transactions)

Hi,

We (Fabalabs) are looking into adding XA support (distributed
transactions) to Postgresql.

I have been searching the web and came up with
http://www.nabble.com/Re%3A-Postgres-XA-support-t226681.html#a633000
which cites some design difficulty.

It is the case that the X/Open XA standard mandates "transaction
branches" that are managed by calls to xa_start()/xa_end() in an
out-of-band manner (via some kind of exported C VMT, sigh), in parallel
to "native resource manager access" and so Michael Allman's objections
(see link above) are correct when one assumes one single persistent
postgresql connection per database prevailing, and a transaction manager
that arbitrarily commands when to start/end transaction branches.

What is the current status?

----------
Definitions:

The goal of a Distributed Transaction is to enable a transaction to span
multiple resources databases and still have the usual guarantees hold
(ACID).

Overview over X/Open Distributed Transactions:

Parts:
- Transaction Manager (1)
- Resource Manager (n)
- Application Program

In chronological order,

With a "global transaction", the Transaction Manager decides when to
start one and makes up a globally unique id for the transaction branch.
Then it notifies every Resource Manager that a transaction branch for a
global transaction is to be started, passing the globally unique id along.

From here, the Application Program accesses the Resource Manager as
usual (SQL statements, ...), with the exception that transaction
management commands are off-limits (BEGIN WORK, COMMIT WORK, ROLLBACK WORK).

The Application Program accesses all other Resource Managers involved in
the global transaction and does likewise.

To commit the global transaction, the transaction manager is called and
does:

For all Resource Managers, notify it to dissociate the connection (of
the "normal" native Postgresql connection) from the global transaction.
For all Resource Managers, notify it that the global transaction is to
be prepared to be committed (2-phase commit).
Any failure results in rollback and error.
On success,
(From this point on, amnesia on part of the database about the prepared
transaction is to be prevented)
For all Resource Managers, notify it that the global transaction is to
be committed.

There are funny optimizations possible, but this is a very minimal
picture of what is "supposed to be going on", just so we all have a
basis for talk :)

with kind regards,
Danny Milosavljevic
Fabalabs R&D