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