Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1we9MV-004b5W-2k for pgsql-hackers@arkaria.postgresql.org; Mon, 29 Jun 2026 10:37:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1we9MU-002gR8-2m for pgsql-hackers@arkaria.postgresql.org; Mon, 29 Jun 2026 10:37:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1we9Ll-002cp2-32 for pgsql-hackers@lists.postgresql.org; Mon, 29 Jun 2026 10:36:26 +0000 Received: from mail-oi1-x234.google.com ([2607:f8b0:4864:20::234]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1we9Lj-00000000kAH-1uMV for pgsql-hackers@lists.postgresql.org; Mon, 29 Jun 2026 10:36:24 +0000 Received: by mail-oi1-x234.google.com with SMTP id 5614622812f47-489795617e9so1557000b6e.0 for ; Mon, 29 Jun 2026 03:36:22 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1782729382; cv=none; d=google.com; s=arc-20260327; b=ovOpz/IWAQC1yRQYKSzLzlXHOrtM9CQneENBQYNYoaXY+K54pTyYQkWQr+VIy4z+1Z 9Aejaao8kKBJOcDuquKt876O6qYncYEND/LfEGfTzbkeRcrQ5fMaQmON5XQuM8Xj0dT4 zt07FnX2tvbGL8PvcJ2+gfboTAbkICR/VqsjkBoRCygEorRqMRPwuRteshRLA6XfiDq8 /NE0f19Z2ce20ZC8JSfgwaJloLOPX2xYa8S9pN42ZBUkqFfz++WofWhoJOcUZJ0idt1y O+pUyV2rlKfp4JFheDfmFv0p9gasfZnP6td+X93MHBD+VgGN0sxWa83SvDsZbCRifXN9 LPpw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20260327; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:dkim-signature; bh=OGRZwD3JWIjswNF3YGDeXwPNXqkwi+bGsXLIDuglXe8=; fh=i/A88MHMG1UZm6YG0ti2ripbv1Sb5kmXqbViB9xBb5w=; b=FwdrMV41WzX/KbvYcny6cPItrXazTbpkTxfoWgRdDxN7T3EoU4M0CAy2Khd+4+SLr+ S49PkT0HE9MNCXzBKDBN9cgAHt9PbfpQOtQ5TkV78o4YS1PIglAtjzMwwQmcvx7LXILZ s93e+IY8m2YTxcbe7eqqj8mYfcTLKCXv/5fLa3TXqwOktVTlZYX7z1znI1YOwe0G68qL 25Ps1qAvWAVxPIe4REc7UAfhxSnh63V6ZJzhnsfvZn9Mawm8NEjwHKEmxYle8sJGSPEv jSiB9/ruc9HEfOi3fYoK+MwrjP8HYfFJt9o1UXpOFtwQKYYpjb1/IW9hpBM2aJQdm94M ze7A==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1782729382; x=1783334182; darn=lists.postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=OGRZwD3JWIjswNF3YGDeXwPNXqkwi+bGsXLIDuglXe8=; b=d2lkIwPV/0I8++2XqgIC4FCrigIoDppV2mMlsEO5a306kpCd9vFjTbVuziLMzrT/Kh qgq/wOJmdtkXwCRHVSzGJrFGDTTDyRQWW7VHi6zMb2DM8iy5HOAXN8SsESiz7bbilVE8 0tieORnfpt3TrWIXSH9Q67MJhHDQ9c02/4jj7vxGhNBYwMgHI4WloMzmwbfP2fK/GHrM +DE069xz8KGIVr1RZq3IOdsAgy1cGCeivSFkMR35w5vX5OhcqeS3oefWTeIAbNxUZnXx qWKNQeB9vYmooFBBnrhONEEH9CaQomrc0yhM5/K5YipwEFkJbo+Gl4y7IqRud7KuZJB1 svXQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1782729382; x=1783334182; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:x-gm-gg:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=OGRZwD3JWIjswNF3YGDeXwPNXqkwi+bGsXLIDuglXe8=; b=dQ96nvtZKyYljPCav6+B9LKhITFJkyt7NAdfbVTCyMuVoSnN0rSPrwqyhvaC+TJUeg j0aJ7HMIGEsMNQdUXuxnysprzZ1aDjovyEPedocwsvXLWSkNfjY+gUH9ckDhg784taKB dxlxSMMxMN/5v8s5FkZTLFvBpdAXDcywJw+mwKSMy87d9Z/p3BCTWu99QMGQrgpZ116l tVYVuKgoSGBBySf/Xhc0gbL9HFRQtM/J07LaoOOEIyANJWHctdgRPudyqTY1yl6/n/vh rHD0p2a9fXqGrt+l9Li03qLxS+kRWzIeg+bGLueAlfU7J76nrYxu0s3yOynzn2FMa/3Z vo7g== X-Forwarded-Encrypted: i=1; AFNElJ96L/QOUZaitz5WbMwfO+IvT1xq8pFOw2Mp+I55NmGG3WfpW5wr2QYdvP249DVoYDiQvDcUNy6qZ0p8bnyh@lists.postgresql.org X-Gm-Message-State: AOJu0YzHTg0N3na0kh5T4Jn3dGTPJ9hVlmhcrllmNDN2yOcKy+tkg1Nq C0RyXD6jW9sEvp72scrLiGTlGTi7sAlBAL/N1A5hMtYddBdiprNR9sipQ4tD4+phoSZarquch3l lG47e/Pz06TMwQyD8l75k3Y6eymC+u3o= X-Gm-Gg: AfdE7cmuIPyWa5EnpAZm04x92aaWY+1QSCTtKzg1B+860Ow4192Jjk795l+BcrJ2kon SzQyNoG2Dm3RkZcw7Uj8bUyrT5t/n/zRDIFSPmbCcAfHh9Xr5SoUcRbOt4ApYoClD7SLXl/gcGs eFfXyx7VtZGGnqlhiBBkrLpOgrZ+45aC8o+OAH14oapodmmgqdcKDNJR8eyC287dek+9Z4T4jIf MwF4xjVim4ynGvmknUaHAHmlY70XSQC145wkEKAaXGEsd+MEtZes73y51PlDihpTkdlawFIOFA= X-Received: by 2002:a05:6808:c189:b0:490:a1df:b17e with SMTP id 5614622812f47-49218112e37mr15420117b6e.31.1782729382383; Mon, 29 Jun 2026 03:36:22 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Shlok Kyal Date: Mon, 29 Jun 2026 16:06:10 +0530 X-Gm-Features: AVVi8CdbtAKYHaYi9wuFweiOF-3jUHH725gXhSSOQ5UHNcVNDZKcqqHDKpj4jzM Message-ID: Subject: Re: Proposal: Conflict log history table for Logical Replication To: Dilip Kumar Cc: Amit Kapila , vignesh C , Nisha Moond , shveta malik , Peter Smith , Masahiko Sawada , Bharath Rupireddy , PostgreSQL Hackers Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Sat, 27 Jun 2026 at 08:03, Dilip Kumar wrote: > > On Fri, Jun 26, 2026 at 3:40=E2=80=AFPM Amit Kapila wrote: > > > > On Thu, Jun 25, 2026 at 4:57=E2=80=AFPM Amit Kapila wrote: > > > > > > On Thu, Jun 25, 2026 at 10:34=E2=80=AFAM vignesh C wrote: > > > > > > > > On Wed, 24 Jun 2026 at 19:27, Dilip Kumar w= rote: > > > > > > > > > > > > > 2) jsonb supports indexing, whereas json does not. Was json chosen > > > > here because inserts are faster due to the lack of parsing and bina= ry > > > > conversion? > > > > > > > > > > I think it is more because the local/remote tuples are usually data > > > which we want users to see as it was in original tables, using jsonb > > > can change the ordering of columns or remove some space etc. We can > > > add a comment on the lines of: "preserve the exact tuple > > > representation (column order, formatting) for the audit record; the > > > value is natively json so this avoids a per-conflict conversion". Now= , > > > this is true for tuple data but I am not so sure if the same thing > > > applies to the replica_identity column in CLT. Would users ever want > > > to query using ORDER/GROUP BY on replica_identity or use DISTINCT on > > > it, because if that is possible then the current schema would result > > > into ERROR as follows: > > > postgres=3D# select * from pg_conflict.pg_conflict_log_16394 order by > > > replica_identity; > > > ERROR: could not identify an ordering operator for type json > > > > > > This needs more thoughts from the perspective of how users would like > > > to fetch the data. > > > > > > > The values stored for replica_identity are heterogeneous across the > > table (different key shapes per relid), and for the RI-FULL case it > > isn't even a stable identifier, two conflicts on the "same row" can > > carry different whole-tuple JSON. So, I don't see a case for creating > > an index on the RI column as well and rather users would like to see > > the exact value used to find the local tuple. So, we can add a comment > > on the lines: "The tuple/key columns (remote_tuple, replica_identity, > > local_conflicts) are typed json rather than jsonb on purpose: they > > hold an exact audit snapshot of the applied tuples and replica > > identity, and json preserves the verbatim representation whereas jsonb > > would normalize it. Indexing them (jsonb's main advantage) wouldn't > > help anyway, as the conflict log is looked up by its scalar columns > > (relid, conflict_type, commit timestamp) while these JSON columns are > > per-conflict payload to inspect, not search keys." > > I have added this comments. > > > One more thing that bothers me is that it will be inconvenient for > > users to identify whether logged values for replica_identity are for > > an index or a complete tuple via replica identity full, so I propose > > to add a boolean column replica_identity_full. I also considered to > > add replica_identity_index but preferred a boolean instead because > > this is a historical/audit row, and the index may be dropped or > > replaced later, leaving a meaningless OID which is opposite of what an > > audit record should do. > > I have added the column replica_identity_full and ordered the column as f= ollows: > > static const ConflictLogColumnDef ConflictLogSchema[] =3D { > <----------Other columns----------------> > { .attname =3D "replica_identity_full", .atttypid =3D BOOLOID }, > { .attname =3D "replica_identity", .atttypid =3D JSONOID }, > { .attname =3D "remote_tuple", .atttypid =3D JSONOID }, > { .attname =3D "local_conflicts", .atttypid =3D JSONARRAYOID } > }; > > > The other question is whether we create an internal index on one of > > the columns? Currently, there's no established query pattern to index > > for. The plausible candidates are relid ("conflicts for table X"), or > > remote_commit_ts (time-based cleanup) but those are guesses. Indexing > > the JSON columns we've already ruled out (payload, not keys). Picking > > an index before we know how people actually query/prune the table > > risks indexing the wrong thing. We can cosider adding the index in > > later versions or even in PG20 itself if we get some user feedback. > > Yeah, I think we can have index once the first patch is committed > based on the usage pattern. > > Hi Dilip, I have tested the patches and have some comments: 1. In the error message, when we try to drop a conflict table, it is referred system catalog: postgres=3D# drop table pg_conflict.pg_conflict_log_16384 ; ERROR: permission denied: "pg_conflict_log_16384" is a system catalog But for other cases in the error message it is referred as conflict log tab= le: postgres=3D# alter table pg_conflict.pg_conflict_log_16384 set schema publi= c; ERROR: permission denied: "pg_conflict_log_16384" is a conflict log table DETAIL: Conflict log tables are system-managed tables for logical replication conflicts. I think in the DROP case we should also refer to it as 'conflict log table' instead of 'system catalog'? Or Am I missing something here? 2. TRUNCATE and DELETE for conflict log tables are published when outplugin is 'test_decoding': Suppose conflict_log_tables have some entries then DELETE/TRUNCATE operatio= ns on the conflict log tables are published. I have tested it with replication slots with test_decoding plugin. postgres=3D# truncate table pg_conflict.pg_conflict_log_16395; TRUNCATE TABLE postgres=3D# SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, N= ULL); lsn | xid | data ------------+-----+--------------------------------------------------------= ------- 0/01792290 | 717 | BEGIN 717 0/01792AD0 | 717 | table pg_conflict.pg_conflict_log_16395: TRUNCATE: (no-flags) 0/01792C10 | 717 | COMMIT 717 (3 rows) Is this expected? postgres=3D# DELETE from pg_conflict.pg_conflict_log_16395 ; DELETE 2 postgres=3D# SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, N= ULL); lsn | xid | data ------------+-----+--------------------------------------------------------= ---------------- ----------------------------- 0/017940A0 | 721 | BEGIN 721 0/017940A0 | 721 | table pg_conflict.pg_conflict_log_16395: DELETE: (no-tuple-data) 0/017940D8 | 721 | table pg_conflict.pg_conflict_log_16395: DELETE: (no-tuple-data) 0/01794140 | 721 | COMMIT 721 I tested the behaviour for pg_class, and noticed DELETE on pg_class do not = get published postgres=3D# delete from pg_class where relname =3D 't'; DELETE 1 postgres=3D# SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, N= ULL); lsn | xid | data ------------+-----+------------ 0/0181C1D0 | 735 | BEGIN 735 0/0181D430 | 735 | COMMIT 735 (2 rows) Also, I tested it with 'pgoutput' plugin and it don't emit any changes postgres=3D# truncate table pg_conflict.pg_conflict_log_16395; TRUNCATE TABLE postgres=3D# SELECT * FROM pg_logical_slot_get_binary_changes( 'pgoutput_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'pub2'); lsn | xid | data -----+-----+------ (0 rows) postgres=3D# DELETE from pg_conflict.pg_conflict_log_16395 ; DELETE 2 postgres=3D# SELECT * FROM pg_logical_slot_get_binary_changes( 'pgoutput_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'pub2'); lsn | xid | data -----+-----+------ (0 rows) Since conflict log tables are system-managed tables, I expected them to behave similarly to pg_class with respect to logical decoding. However, test_decoding emits DELETE and TRUNCATE changes for conflict log tables. Is this expected? Thanks, Shlok Kyal