Re: PATCH: two phase commit

Federico Di Gregorio wrote:
> Il giorno lun, 21/01/2008 alle 11.31 +0900, James Henstridge ha scritto:
>>> The question is, we want to "lock" the connection untill the current
>>> prepared transaction is commited or we want to give the client the
>>> ability to set aside prepared transactions and execute them from a
>>> different connection? If nobody need the second one the first option
>> is
>>> "safe"; you can even abort the currently prepared transaction on
>> object
>>> dealloc.
>> Yep.  The more limited API would handle pretty much everything I need
>> to.
>>
>> I think the primary use case for committing/rolling back a transaction
>> from a different connection is recovery, which may be out of scope for
>> a common 2PC API for Python database adapters.
> 
> Agreed. Then I'll start with a single prepare_current_transaction()
> method that "locks" the connection (i.e., every other operation raise an
> exception) untill commit() or rollback() are called.

This makes the API useless for implementing anything more advanced, such as XA.

In particular, if you are dealing with two phase commit you need some sort
of a watchdog to detect and deal with orphaned transactions or your database
is going to bloat (just like an IDLE in transaction connection left open,
they stop vacuum reclaiming space). Nearly every serious environment using
TPC needs something like this, so is it a common use case rather than an
uncommon one. I know I'm going to have to write one before we can use TPC in
production and I'd like to write it in Python. In fact, I just updated one
of our tools the other day to rollback rogue prepared transactions in order
to reliably tear down databases, manually sending ROLLBACK PREPARED
statements via a connection in autocommit mode.

Adding an optional xid argument to commit & rollback, or adding
commit_prepared and rollback_prepared to the connection would handle this
use case if locking the connection is considered desirable.

Automatically rolling back a prepared transaction on object dealloc is a
potential data loss situation. If something catastrophic happens in the
second phase of a two phase commit, throwing away data may make the
situation unrecoverable. PostgreSQL tries to guarantee that you can commit
the data once you have done a PREPARE TRANSACTION and invalidating this
guarantee is dangerous.

I personally would prefer clean access to the PostgreSQL API rather than
having the driver impose smarts here, thinking it knows best and locking the
connection on me.

-- 
Stuart Bishop <[email protected]>
http://www.stuartbishop.net/

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHlG0qAfqZj7rGN0oRAupZAKCQEgrtUC/7Q4chXTV0jcHGpOa4JQCgmvLp
TMAKo+ldjcOc23AlNOvQzwo=
=9V6t
-----END PGP SIGNATURE-----