Re: pg_prepared_xact_status

On 29.09.2017 11:27, Craig Ringer wrote:
> On 29 September 2017 at 15:57, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>> wrote:
>
> So you are saying that Postgresql 2PC mechanism is not complete
> and user needs to maintain some extra information to make it work?
>
>
> No, it provides what's needed for an implementation of what in XA
> terms is a local resource manager (LRM). What it does not provide is
> infrastructure to make postgres its self into a global transaction
> manager (GTM) for co-ordinating multiple LRMs.
>
> It sounds like you're trying to build a GTM using PostgreSQL's
> existing LRM book-keeping as your authorative data store, right?

No exactly. I am trying to add 2PC to our pg_shardman: combination of
pg_pathman + postgres_fdw + logical replication, which should provide HA
and write scalability.
This architecture definitely not assume presence of GTM. Most of
transactions are expected to be local (involves only one node) and
number of participants of distributed transaction is expected to be much
smaller than total number of nodes (usually 2). So we need to perform
2PC without GTM.

> The problems with 2PC arrive when coordinator node is not
> available but is expected to be recovered in future.
> In this case we may have not enough information to make a decision
> whether to abort or commit prepared transaction.
> But it is a different story. We need to use 3PC or some other
> protocol to prevent such situation.
>
>
> In that case the node sits and waits patiently for the GTM (or in more
> complex architectures, *a* valid voting quorum of GTMs) to be
> reachable again. Likely using a protocol like Raft, Paxos, 3PC etc to
> co-ordinate.
>
> It can't do anything else, since if it unilaterally commits or rolls
> back it might later find out that the nodes on the other side of the
> network partition or whatever made the opposite decision and, boom!

Ok, I am not sure if pg_prepared_xact_status can be really useful or not.
I agree with you that if we are implementing distributed transaction on
top of Poasgres, then we need some better mechanism to determine
transaction state.
But a lot of people are using 2PC without GTM or whatever else. For
example, many Java ORMs are using 2PC for their transactions.
I think that it is better to provide to DBA or programmer some way to
determine status of such transaction by GID (which is usually unique and
known), as far as this information
is available in Postgres WAL.

In any case, I attached slightly improved version of this function which
traverse log not only since last checkpoint, but also try iterates
backward inspecting previous WAL segments.

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