Traceable commit for PostgreSQL 10

PostgreSQL 10 now supports finding out the status of a recent transaction for recovery after network connection loss or crash.

Recovery from indeterminate COMMIT

If there was a COMMIT “in flight” when an application crashed, lost its connection to the DB server, or the DB server crashed, the commit’s status is indeterminate as far as the application can tell. The application does not know if the COMMIT message was received by the database and processed to completion or not, so it doesn’t know if re-issuing that transaction is necessary or correct. Sometimes the application can examine rows in its tables to figure it out; sometimes it can’t.

Applications that need to recover pending work after a crash usually use two-phase commit. That works, but it’s slow even after the 2PC performance improvements in Pg 10 because there’s an extra client/server round-trip for PREPARE TRANSACTION.

How does traceable commit help?

The new txid_status(...) function addresses this by allowing applications to look up a recent transaction-id to find out if it committed or aborted, either due to crash or explicit rollback.

The extra round-trip is now only necessary during recovery after a failure, and there’s no extra server-side workload from two-phase commit.

commit 857ee8e391ff6654ef9dcc5dd8b658d7709d0a3c
Author: Robert Haas <[email protected]>
Date:   Fri Mar 24 12:00:53 2017 -0400

    Add a txid_status function.
    
    If your connection to the database server is lost while a COMMIT is
    in progress, it may be difficult to figure out whether the COMMIT was
    successful or not.  This function will tell you, provided that you
    don't wait too long to ask.  It may be useful in other situations,
    too.
    
    Craig Ringer, reviewed by Simon Riggs and by me
    
    Discussion: http://postgr.es/m/[email protected]om

How to use it

The application must obtain the transaction ID of any transaction it wants to look up using txid_current() or txid_current_if_assigned() before it sends the COMMIT. It can avoid an extra round-trip for this query by:

• Issuing a “multi-statement query”, e.g. INSERT INTO ...; SELECT txid_current(); if it doesn’t need the result of the prior query;
• Combining it with another query, e.g. INSERT INTO ... RETURNING txid_current();
• Using client driver support for batching queries to dispatch the txid_current() query along with other queries without waiting for a reply for each query.

In a future version PostgreSQL may automatically report the transaction ID when it is assigned to make this easier for applications.

The application then stores the transaction ID for the pending transaction locally alongside its pending work before it sends the COMMIT to PostgreSQL. For example purposes lets say it gets the xid 63204.

If network connection to PostgreSQL is lost after the COMMIT is sent but before a reply is received, the application can recover by reprocessing its pending work queue, checking to see if each indeterminate-status item needs to be re-processed or not by testing whether the saved xid committed or not, e.g.:

SELECT txid_status(BIGINT '63204')

which will return committed if the transaction is already safely on disk on the DB, so the application can skip re-issuing it. Or it’ll return aborted if the transaction was lost before commit and needs to be re-issued.

Because of the vagaries of TCP/IP, the database may not have even noticed that the application connection has gone away by the time the application reconnects. In this case the query will return in progress. The application can wait to find out if the transaction lands up committing, or it can look up the associated backend in pg_stat_activity and pg_terminate_backend(...) it to hurry things up.

Finally, if the application takes too long to ask after the status of its lost-in-flight commit, the database may return NULL to indicate that its status is no longer known. This happens once all tuples associated with that transaction and all older transactions have been deleted or updated to newer versions and vacuum has cleaned up the old transaction history information.

Because knowledge of transaction status is eventually discarded and because you cannot actually ROLLBACK a transaction once committed, the traceable commit feature does not substitute for two-phase commit in all applications. It won’t plug in to your JTA or XA transaction manager or your MS-DTC setup, but it’ll help throughput for reliable queue-processing apps among others.

An implementation detail

Experienced users may have noticed that txid_status(bigint) takes a bigint (as returned by txid_current(), not PostgreSQL’s internal transaction ID data type xid. That’s because xid is 32-bit and wraps around and we don’t want to risk giving the wrong answer. The bigint is extended to 64 bits with an epoch counter that increments each time the xid wraps around.