From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Craig Ringer Now, what I do think we need is to give the client the ability to determine whether one of its xacts actually committed or not when it lost the session after dispatching COMMIT but before getting a confirmation from the server and persistently storing that knowledge. Right now if you want that you have to do full 2PC. You shouldn't need to, you should be able to get the xid when the xact is assigned it and store it somewhere locally. Then later, if you're unsure if that xid committed or not due to a client crash etc, you should be able to do some kind of SELECT pg_xact_is_committed(xid) to find out. Right now this is possible to write with a pretty simple extension, but adds an extra roundtrip for a SELECT txid_current() call (unless you pipeline it). I'd prefer that the server just tell you when an xid is assigned. And yes, I think xid is the right identifier for this; it's short, simple, and while it wraps around it takes long enough to do so that it's very well suited for this job.
This is interesting. Oracle provides Transaction Guard for this. Our customers also sometimes encounter the trouble of duplicate records in the database when, when their apps get disconnected during commit and reconnect to insert the same record again.