pg_prepared_xact_status

Hi,

In Postgres 10 we have txid_status function which returns status of
transaction by XID.
I wonder if it will be also useful to have similar function for 2PC
transactions which can operate with GID?
pg_prepared_xacts view allows to get information about prepared
transaction which are not yet committed or aborted.
But if transaction is committed, then there is no way now to find status
of this transaction.

If crash happen during 2PC commit, then transaction can be in prepared
state at some nodes and committed/aborted at other nodes.
Using pg_prepared_xacts view DBA can find out global transactions which
were not completed.
But there is no way (except pg_waldump) to determine whether this
transaction needs to be committed or aborted at rest of the nodes.

Attached please find small patch with pg_prepared_xact_status function.
This function has the following obvious drawbacks:

1. It is not able to extract information about prepared transaction
preceding last checkpoint. It seems to be enough to perform recovery in
case of failure unless
checkpoint happen just before failure or there is large gap between
prepare and commit.
The only workaround I see at this moment is to pass to this function
optional parameter with start position in the WAL.
Any better solution?

2. On systems with huge workload interval between checkpoints may be
very large. In this case we have to scan large amount of WAL data to be
able to locate our transaction.
Whoich make take significant amount of time.
We can traverse WAL in smarter way, starting from last segment, assuming
that in-doubt transaction was prepared just before crash.
But it significantly complicates traverse logic.

3. Same GID can be reused multiple times. In this case
pg_prepared_xact_status function will return incorrect result, because
it will return information about first global transaction with such GID
after checkpoint and not the recent one.

There is actually alternative approach to recovery of 2PC transactions.
We can include coordinator identifier in GID (we can use
GetSystemIdentifier() to identify coordinator's node)
and XID of coordinator's transaction. In this case we can use
txid_status() to check status of transaction at coordinator. It
eliminates need to scan WAL to determine status of prepared transaction.

--

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company