Re: Proposal: Conflict log history table for Logical Replication

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 Regards,
Amit Kapila.