On Mon, Oct 12, 2026 at 11:31 AM shveta malik <[email protected]> wrote: > > On Mon, Oct 12, 2026 at 6:04 AM Amit Kapila <[email protected]> wrote: > > > > 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 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. >
If we want to address this, I have attached the patch for a potential solution. I have also attached the testcase. In the worker, we can change the config to datestyle=ISO before building the JSON values. --NewGUCNestLevel() opens a new GUC nesting level and returns its number. It does not change any setting. --SetConfigOption() then changes the GUC (datestyle=ISO). --AtEOXact_GUC() restores the GUC stack back to that marked point. In case of error, AtEOXact_GUC() will be called by Abort-Txn. This way we change the GUC only during the concerned path (the CLT json creation), not for the entire apply-worker duration. thanks Shveta
pub, sub:
CREATE TABLE tab1 (k timestamptz PRIMARY KEY, v int);
pub:
CREATE PUBLICATION pub1 FOR TABLE tab1;
sub:
create subscription sub1 connection '...' publication pub1
WITH(conflict_log_destination='all');
pub:
INSERT INTO tab1 VALUES ('2025-12-25 10:00:00+05:30', 1);
sub:
UPDATE tab1 SET v = 99 WHERE k = '2025-12-25 10:00:00+05:30';
pub:
DELETE FROM tab1 WHERE k = '2025-12-25 10:00:00+05:30';
sub:
--all normal so far.
SELECT (local_conflicts[1]->>'commit_ts')::timestamptz as local_ts,
(replica_identity->>'k')::timestamptz as ri_ts,
remote_commit_ts::timestamptz as rc_ts FROM
pg_conflict.pg_conflict_log_16390;
Sub:
ALTER DATABASE postgres SET DateStyle = 'SQL, DMY';
ALTER SUBSCRIPTION sub1 DISABLE; ALTER SUBSCRIPTION sub1 ENABLE; -- restart
worker
pub: insert an AMBIGUOUS date (Jan 2)
INSERT INTO tab1 VALUES ('2025-01-02 08:00:00+05:30', 1);
sub:
UPDATE tab1 SET v=99 WHERE k='2025-01-02 08:00:00+05:30';
pub:
DELETE FROM tab1 WHERE k='2025-01-02 08:00:00+05:30';
--let user read the CLT in an ISO,MDY session and casts both back:
--Jan 2 read as Feb 1 in ri_ts columns.
SET DateStyle='ISO, MDY';
SELECT (local_conflicts[1]->>'commit_ts')::timestamptz as local_ts,
(replica_identity->>'k')::timestamptz as ri_ts,
remote_commit_ts::timestamptz as rc_ts FROM
pg_conflict.pg_conflict_log_16390;
~~
Before fix:
postgres=# SET DateStyle='ISO, MDY';
SELECT (local_conflicts[1]->>'commit_ts')::timestamptz as local_ts,
(replica_identity->>'k')::timestamptz as ri_ts,
remote_commit_ts::timestamptz as rc_ts FROM
pg_conflict.pg_conflict_log_16390;
SET
local_ts | ri_ts |
rc_ts
----------------------------------+---------------------------+---------------------------------
2026-10-13 09:28:44.599098+05:30 | 2025-12-25 10:00:00+05:30 | 2026-10-13
09:28:49.77991+05:30
2026-10-13 09:29:18.173178+05:30 | 2025-02-01 08:00:00+05:30 | 2026-10-13
09:29:23.37772+05:30
(2 rows)
After fix:
postgres=# SET DateStyle='ISO, MDY';
SELECT (local_conflicts[1]->>'commit_ts')::timestamptz as local_ts,
(replica_identity->>'k')::timestamptz as ri_ts,
remote_commit_ts::timestamptz as rc_ts FROM
pg_conflict.pg_conflict_log_16390;
SET
local_ts | ri_ts |
rc_ts
----------------------------------+---------------------------+----------------------------------
2026-10-13 09:21:16.31546+05:30 | 2025-12-25 10:00:00+05:30 | 2026-10-13
09:21:21.395595+05:30
2026-10-13 09:23:28.976249+05:30 | 2025-01-02 08:00:00+05:30 | 2026-10-13
09:23:34.624595+05:30
(2 rows)
0001-Datestyle-fix.patch
Description: Binary data
