On Fri, Oct 9, 2026 at 1:43 PM shveta malik <[email protected]> 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.
