Re: Proposal: Conflict log history table for Logical Replication

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

The current use of the "datestyle" and other GUCs in libpqwalreceiver
or in other places is to keep the format portable across nodes but
here the CLT output will be usually human-readable. So, the same
analogy doesn't follow here. I am not convinced that this required
mid-way changing the GUCs temporarily. I feel we can document this
column and if we still get a request from field for portability of
this field(s) then we can consider using GUCs to make the format
consistent.

--
With Regards,
Amit Kapila.