Re: pg_prepared_xact_status

On Fri, Sep 29, 2017 at 1:53 AM, Konstantin Knizhnik
<k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
> 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.

But you need to keep track of the transaction XID of each transaction
happening on the remote nodes which are part of a global 2PC
transaction, no? If you have this data at hand using txid_status is
enough to guess if a prepared transaction has been marked as committed
or prepared. And it seems to me that tracking those XIDs is mandatory
anyway for other consistency checks.

> If crash happen during 2PC commit, then transaction can be in prepared state
> at some nodes and committed/aborted at other nodes.

Handling inconsistencies here is a tricky problem, particularly if a
given transaction is marked as both committed and aborted on many
nodes. The only way that I could think of would be to perform PITR to
recover from the inconsistent states. So that's not an easy problem,
becoming even more tricky if more than one transaction is involved and
many transactions are inter-dependent across nodes.

> 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.

Yeah, this argument alone is why I think that this is a dead-end approach.

> 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.

+ GetOldestRestartPoint(&lsn, &timeline);
+
+ xlogreader = XLogReaderAllocate(&read_local_xlog_page, NULL);
+ if (!xlogreader)
So you scan a bunch of records for each GID? This is really costly. I
think that you would have an easier life by tracking the XID of each
transaction involved remotely. In Postgres-XL, this is not a problem
as XIDs are assigned globally and consistently. But you would gain in
performance by keeping track of it on the coordinator node.
--
Michael