Re: Proposal: Conflict log history table for Logical Replication

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?

thanks
Shveta