Re: Proposal: Conflict log history table for Logical Replication

On Sat, Oct 24, 2026 at 11:42 AM Amit Kapila <amit(dot)kapila16(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:
> > >
> > > 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.
>

But it causes ambiguous results as shown in [1]. I am not sure how
documentation will help here. As I see it, the stored key must
convert back to the original value, not to some other value,
especially since the patch’s own docs recommend casting it back to the
column type before comparing. And I don’t think libpqwalreceiver does
this for portability: its comment says it forces these GUCs so values
are output “in a form that is unambiguous to the subscriber.” IMO,
that’s the same goal here: avoiding a wrong stored value, not
portability.

[1]:https://www.postgresql.org/message-id/CAJpy0uB7ExJjtgsSipnGOZT6p%3Dkpx3OWaXSph%2BKRNaQKrhvCLw%40mail.gmail.com

thanks
Shveta