Re: Proposal: Conflict log history table for Logical Replication

On Mon, Oct 12, 2026 at 6:04 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, Oct 9, 2026 at 1:43 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > Another concern on v79 is:
> >
> > I noticed that one CLT row contains three timestamp representations
> > with three different behaviors:
> >
> > 1) replica_identity is frozen in the apply worker’s DateStyle.
> > Session's Datestyle has no impact on it.
> > 2) remote_commit_ts is represented in the reader’s (session's) DateStyle, and
> > 3) local_conflicts.commit_ts is always ISO.
> >
> > This may cause some conufsion. Please see the results below. I can
> > share the testcase if needed:
> >
> > Conflicts:
> >
> > postgres=# select conflict_type, remote_commit_ts, replica_identity,
> > local_conflicts from pg_conflict.pg_conflict_log_16390;
> > conflict_type | remote_commit_ts |
> > replica_identity |
> > local_conflicts
> > -----------------------+--------------------------------+-----------------------------------+------------------------------------------------------------------------------------------
> > delete_origin_differs | 09/10/2026 14:15:29.055423 IST |
> > {"k":"2025-12-25 10:00:00+05:30"} |
> > {"{\"xid\":\"672\",\"commit_ts\":\"2026-10-09T14:15:22.033391+05:30\",\"origin\":null}"}
> > delete_origin_differs | 09/10/2026 14:17:12.929024 IST |
> > {"k":"02/01/2025 08:00:00 IST"} |
> > {"{\"xid\":\"678\",\"commit_ts\":\"2026-10-09T14:17:09.326423+05:30\",\"origin\":null}"}
> > (2 rows)
> >
> >
> > 1) First conflict row was inserted with default Datastyle of DB and
> > the apply worker which is:
> > postgres=# show DateStyle;
> > DateStyle
> > -----------
> > ISO, DMY
> >
> > 2) Before inserting second conflict, below was done.
> >
> > ALTER DATABASE postgres SET DateStyle = 'SQL, DMY';
> > ALTER SUBSCRIPTION sub1 DISABLE; ALTER SUBSCRIPTION sub1 ENABLE;
> >
> > (So Above conflict data is read when session's format was also 'SQL,
> > DMY', thus we see remote_commit_ts in SQL format)
> > ----
> >
> > We can see the differnce in 'replica_identity' values in 2 rows, while
> > remote_commit_ts and local_conflicts.commit_ts remain same. Second RI
> > is 02/01/2025 in DMY format, meaning 2nd january.
> >
> > 3)
> > Now if I do this:
> > SET DateStyle = 'ISO, MDY'
> >
> > postgres=# select conflict_type, remote_commit_ts, replica_identity,
> > local_conflicts from pg_conflict.pg_conflict_log_16390;
> > conflict_type | remote_commit_ts |
> > replica_identity |
> > local_conflicts
> > -----------------------+----------------------------------+-----------------------------------+------------------------------------------------------------------------------------------
> > delete_origin_differs | 2026-10-09 14:15:29.055423+05:30 |
> > {"k":"2025-12-25 10:00:00+05:30"} |
> > {"{\"xid\":\"672\",\"commit_ts\":\"2026-10-09T14:15:22.033391+05:30\",\"origin\":null}"}
> > delete_origin_differs | 2026-10-09 14:17:12.929024+05:30 |
> > {"k":"02/01/2025 08:00:00 IST"} |
> > {"{\"xid\":\"678\",\"commit_ts\":\"2026-10-09T14:17:09.326423+05:30\",\"origin\":null}"}
> > (2 rows)
> >
> > See remote_commit_ts changed from previous SQL to ISO, while
> > replica_identity and local_conflicts.commit_ts are the same.
> >
> > If I try to read it as documented (casting it back to original
> > column's type), I get misleading results:
> >
> > postgres=# SET DateStyle = 'ISO, MDY';
> >
> > postgres=#
> > SELECT (local_conflicts[1]->>'commit_ts')::timestamptz as local_ts,
> > (replica_identity->>'k')::timestamptz as ri_ts
> > FROM pg_conflict.pg_conflict_log_16390;
> >
> > local_ts | ri_ts
> > ----------------------------------+---------------------------
> > 2026-10-09 14:15:22.033391+05:30 | 2025-12-25 10:00:00+05:30
> > 2026-10-09 14:17:09.326423+05:30 | 2025-02-01 08:00:00+05:30
> > (2 rows)
> >
> > See second row for ri_ts, it will now represent '1st Feb' rather than
> > '2nd January'.
> >
> > Shouldn't all three timestamps be in the same format?
> >
> > IMO, remote_commit_ts being session-dependent is expected.
> > The concern is the other two JSON text columns. replica_identity
> > stores the worker’s DateStyle, so the ->>'k'::timestamptz can
> > silently return a different value (here 2nd Jan read back as 1st Feb);
> > while local_conflicts.commit_ts is always ISO. Shouldn’t the recorded
> > JSON timestamps use a single, stable format (ISO) so they’re
> > unambiguous and safe to cast back?
> >
>
> What are the values if you don't change the Datestyle manually and use
> the default or what was used by worker while reading it?
>

With default, values are:

postgres=# SELECT conflict_type,
remote_commit_ts,
replica_identity->>'k' AS ri_key_timestamp,
local_conflicts[1]->>'commit_ts' AS lc_commit_ts
FROM pg_conflict.pg_conflict_log_16390;

conflict_type | remote_commit_ts |
ri_key_timestamp | lc_commit_ts
-----------------------+----------------------------------+---------------------------+----------------------------------
delete_origin_differs | 2026-10-12 10:47:07.349306+05:30 | 2025-12-25
10:00:00+05:30 | 2026-10-12T10:47:04.618018+05:30
delete_origin_differs | 2026-10-12 10:48:22.419669+05:30 | 2025-01-02
08:00:00+05:30 | 2026-10-12T10:48:20.277719+05:30
(2 rows)

Default DateStyle of Db:

postgres=# show DateStyle;
DateStyle
-----------
ISO, DMY
(1 row)

Same is used by worker while insert and in same we have read the above data.

~~

So with the default (ISO), or whenever the reader’s DateStyle matches
the one the worker used at write time, the values read back correctly;
the misleading result only appears when the reader’s DateStyle differs
from the worker’s write-time DateStyle (specifically when the worker
wrote under a non-ISO style).

~~

Further observations and analysis:

1) Apply worker uses current datestyle of DB for replica_identity, and
then materializes it into text (json col).

2) local_conflicts always uses ISO because it’s built via row_to_json.
See JsonEncodeDateTime() → EncodeDateTime(), which is called with
USE_XSD_DATES; in EncodeDateTime() that falls through the same branch
as USE_ISO_DATES.

3) remote_commit_ts is stored as timestamp column rather than text in json.

The one stored as timestamptz (3) will have no issues. Because it’s a
genuine timestamptz, we always get back the correct instant; only its
display changes with the reader’s DateStyle.

Both (1) and (2) are frozen as text, but IIUC, only replica_identity
is at risk.

local_conflicts.commit_ts avoids it by alwyas using ISO (written
always as YYYY-MM-DD, where month and day are in fixed positions. Thus
no reader’s DMY/MDY setting can swap them.

replica_identity is the only one that stores the worker’s DateStyle
into stored text (unknown and un-recoverable later). Different rows
can be frozen under different styles (config change), so a reader has
no reliable style to pick while reading.

The fix could be to have the apply worker also use ISO style while
inserting; similar to row_to_json, and how logical replication’s
libpqwalreceiver standardizes the wire timestamp values using ISO. See
libpqrcv_connect using datestyle=ISO.

thanks
Shveta