Re: Proposal: Conflict log history table for Logical Replication

On Mon, Oct 12, 2026 at 2:05 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Mon, Oct 12, 2026 at 11:31 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > 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.
> >
>
> On checking libpqwalreceiver further, I see that subscriber asks
> publisher to give data with these 3 GUC settings to remove any
> ambiguity:
> -c datestyle=ISO -c intervalstyle=postgres -c extra_float_digits=3
>
> We have already analyzed above the impact of 'datestyle' on
> 'replica_idenity' value. I was wondering if along with 'datestyle',
> the other 2 can also impact the correctness of the CLT data? I think
> we need to analyze the other two GUCs as well.
>

I analyzed that 'intervalstyle' could also cause correctness issue
when reading the values. Attached the testcase with results before and
after fix in it.

The fix would be similar to DateStyle fix in build_index_key_json():

+ (void) set_config_option("intervalstyle", "postgres",
+ PGC_USERSET, PGC_S_SESSION,
+ GUC_ACTION_SAVE, true, 0, false);

Will analyze 'extra_float_digits' next.

thanks
Shveta