Re: [HACKERS] logical decoding of two-phase transactions - Mailing list pgsql-hackers

On Mon, Nov 16, 2020 at 4:25 PM Ajin Cherian <itsajin@gmail.com> wrote:
>
> Updated with a new test case
> (contrib/test_decoding/t/002_twophase-streaming.pl) that tests
> concurrent aborts during streaming prepare. Had to make a few changes
> to the test_decoding stream_start callbacks to handle
> "check-xid-aborted"
> the same way it was handled in the non stream callbacks. Merged
> Peter's v20-0006 as well.
>

Thank you for updating the patch.

I have a question about the timestamp of PREPARE on a subscriber node,
although this may have already been discussed.

With the current patch, the timestamps of PREPARE are different
between the publisher and the subscriber but the timestamp of their
commits are the same. For example,

-- There is 1 prepared transaction on a publisher node.
=# select * from pg_prepared_xact;

 transaction | gid |           prepared            |  owner   | database
-------------+-----+-------------------------------+----------+----------
         510 | h1  | 2020-11-16 16:57:13.438633+09 | masahiko | postgres
(1 row)

-- This prepared transaction is replicated to a subscriber.
=# select * from pg_prepared_xact;

 transaction | gid |           prepared            |  owner   | database
-------------+-----+-------------------------------+----------+----------
         514 | h1  | 2020-11-16 16:57:13.440593+09 | masahiko | postgres
(1 row)

These timestamps are different. Let's commit the prepared transaction
'h1' on the publisher and check the commit timestamps on both nodes.

-- On the publisher node.
=# select pg_xact_commit_timestamp('510'::xid);

   pg_xact_commit_timestamp
-------------------------------
 2020-11-16 16:57:13.474275+09
(1 row)

-- Commit prepared is also replicated to the subscriber node.
=# select pg_xact_commit_timestamp('514'::xid);

   pg_xact_commit_timestamp
-------------------------------
 2020-11-16 16:57:13.474275+09
(1 row)

The commit timestamps are the same. At PREPARE we use the local
timestamp when PREPARE is executed as 'prepared' time while at COMMIT
PREPARED we use the origin's commit timestamp as the commit timestamp
if the commit WAL has.

This behaviour made me think a possibility that if the clock of the
publisher is behind then on subscriber node the timestamp of COMMIT
PREPARED (i.g., the return value from pg_xact_commit_timestamp())
could be smaller than the timestamp of PREPARE (i.g., 'prepared_at' in
pg_prepared_xacts). I think it would not be a critical issue but I
think it might be worth discussing the behaviour.

Regards,

-- 
Masahiko Sawada
EnterpriseDB:  https://www.enterprisedb.com/