Re: Proposal: Conflict log history table for Logical Replication - Mailing list pgsql-hackers

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