Re: XA support (distributed transactions)

Danny Milosavljevic wrote:
> > The "only" missing
> > part is an transaction manager, but that wouldn't have to
> > be integrated into postgres. [...]
>
> Our parent company, Fabasoft, already wrote a transaction manager
> ("Fabasoft Transaction Manager"). I doubt that it is Open Source but
> I'll ask.

Are you a Java shop, using JTA, or are working with a X/Open XA spec
compliant interface? If the former, I'd strongly suggest not to invent
the wheel again. Implementing a transaction manager reliably and
efficiently isn't as easy as it looks, and there's plenty of open source
JTA implementations available.

> > because
> > due to the design of 2-phase-commit, you cannot deduce the faith
> > of a transaction by just looking at the nodes.
>
> But you can find out the prepared transactions of a two phase commit
> (there is a pg system table for that, "pg_prepared_xacts").
>
> What's important is that if the database management system were to
> crash, the prepared entries in that table _need to persist_ and be
> available after a restart (so that the transaction manager can find out
> what actually worked and what didn't work per database before the
> connection broke - in XA slang, with a "XA RECOVER").
>
> What's also important is that PREPARED transactions must not complete
> (not commit, not rollback) when the pq connection to the database is
> closed. This ensures that the database doesn't lose (nor commit) already
> prepared transactions in the unlikely but evil case of a
> crash/disconnect just between a 2PC PREPARE and a 2PC COMMIT.
> (Transactions not yet prepared can just vanish in thin air on the DBMS'
> discretion)

We have all that in PostgreSQL.

> Also, XA has some kind of semi-parallelism where one global transaction
> branch is supposed to be able to be suspended, another global
> transaction branch to be worked on "instead", then this one suspended
> too, then the original resumed, and so on, leading to something
> reminiscent of time slices :). I have no idea how far Postgresql
> supports this particularily tricky part, and whether it makes any sense
> to (or whether just a commit/rollback of a previously
> crashed-but-prepared transaction suffices).

We don't support that in PostgreSQL. That's what the thread on
pgsql-jdbc was all about. There's been a lot of discussion on this, and
the conclusion is: there's isn't anything interesting you can't do
without it, and implementing it would require huge changes. What we have
is sufficient for reliable distributed transaction.

> So my actual questions are:
> what is a postgresql transaction tied to, if anything? Or is it a
> first-class object?
> What happens to prepared-but-not-yet-committed 2PC transactions on a crash?

After PREPARE TRANSACTION, the transaction is disassociated from the
connection, and it'll stay prepared even after a server crash.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com