In-doubt window - Mailing list pgsql-hackers

Some of you may remember that we discussed libpqxx's higher-level
support for transactionality.  

It was pointed out (by Tom, IIRC) that the connection to the backend
may be lost just after you've sent out the commit, while you're still
waiting for a confirmation.  So I built a class that expends some
optional extra effort to reconnect after this type of failure, and find
out whether the transaction was actually committed or not.  It involves
writing a record to a dedicated "transaction log" table inside the
transaction, and deleting it right after the commit--or it could be the
other way around: write the record before the transaction, then delete
it inside the transaction.  If the library ever needs to reconnect to
find out if the transaction went through, it looks up this record and
its mere existence tells it what it needs to know.

A long time after, somebody else pointed out a remaining hole in this
approach: what if your transaction took so long that it's still doing
its commit when you reconnect, and so the state you see on reconnect
suggests that the transaction was aborted--when in reality it may still
complete successfully?  The only things I could think of that didn't
require new database features were ways to slightly improve the odds:
wait a while before reconnecting; ensure that constraints are checked
before sending the commit; only resolve the in-doubt situation if the
answer is "yes, there was definitely a commit."
But now my bright friend and colleague German Rodriguez has come up
with an idea that might just work.  It involves locking, which I don't
know much about so I'd be interested to hear from people with a better
grasp of how locking works and what's possible in the postgres
implementation.

The proposed scheme, based on German's idea, goes as follows:

1. create a log record
2. begin transaction
3. lock record
4. delete record
5. perform "payload" transaction
6. commit--let's say this fails!
7. reconnect
8. check if log record exists:- if no, the transaction was successfully committed- if yes, and we can read it:
transactionwas aborted- let's say yes, but it's still locked: transaction still in progress
 
9. poll for a change in the record's situation

For extra points, I might even use a trigger to reduce the need for 
polling (though I don't think that's a particularly good idea since it
won't help the abort case and sacrifices performance of the normal case)

What do you guys think--could this work?


TIA,

Jeroen