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 1wWnbM-002rZ4-2U for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Jun 2026 03:58:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wWnbL-004jdY-1t for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Jun 2026 03:58:07 +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 1wWnbK-004jdP-2x for pgsql-hackers@lists.postgresql.org; Tue, 09 Jun 2026 03:58:07 +0000 Received: from mail-lf1-x133.google.com ([2a00:1450:4864:20::133]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wWnbH-00000001mwt-47s2 for pgsql-hackers@lists.postgresql.org; Tue, 09 Jun 2026 03:58:05 +0000 Received: by mail-lf1-x133.google.com with SMTP id 2adb3069b0e04-5aa6cdebc33so4562116e87.2 for ; Mon, 08 Jun 2026 20:58:03 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1780977481; cv=none; d=google.com; s=arc-20240605; b=jMCg/uCynsOTaS+4lHr3D7ab68SHP+S4oUJCYWjrzdfnD1Dpyf7c30mDGips1axk9E TigAAFSV25wW6IS10ooPq50twvm84asqCSH0zYSI/WvMN7vx+sEOq40Nt9/5Jn2i3Vsb DDz/+BPmcMB2XPpAnAM2b4df63Wi8K1WEk5jtxyNEPtJskGrXB9vD5b1bq/mJBPTjtYF q71kiD8bbstmONU127buzVuQ1Yh47jI9VRSuK2dD2rDSV4JYdPW3rvLX/TNAFLECSnfW ah8cp76VDl27pnPAYea/8+JXysklVX9P3ovKV4TkwSjOLBTIfC/KfEe6KQ6+Q+NPawbb rt1g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=0UaUzyruzqOBAVLaXz6phlC0P38jjbSy47cCKdSQBcg=; fh=Pj46ociXdCYRy+s/ARKTs0khYIvGv3X1mx2ErcCzekg=; b=Zm5CBYf1QHKLl4Ph6r1CmVedZzkLN+miIWilSp8+QHtd3lNpLXkIiqtKTzVOPR1nVO dbet43zrPr6S/+FCkiuqErdTn7Wxp7OwagGlsF208rI6n/XvWy1gdG6cGJ/SrGmiskSS zk24SBU1xcb6iuIaeccE+Xi4+7SYEB4yU/zlJ90o6RZ+WYocCgAzFVvkGtMh1iifO7jM 3FaFfVxrx6BFhRJe3zIY7UI14t/CXbbcyfcuhI8yUnA0d3qlDRI+54VV3+O9a27/s/kL TL9apTEX6ITKcjH6HGp4c6mYlH3oRwkvt9M1TheJyjDH8F/L/JQrhouZ8tOuCLMavlC2 vvbw==; 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=1780977481; x=1781582281; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=0UaUzyruzqOBAVLaXz6phlC0P38jjbSy47cCKdSQBcg=; b=E7EUyTZffxKd8CVJyrngwq0r4pk2ia1KvaSq/a7KSG3KXrdAJtMmcklLDt0kbKtm2q T4AF626ypWP/TeNo8mpP+yW7rXT07NqlXcX+nuCGb9H1LLi1v70e2nLrQI2DcDVESRcg glb6IlhoikT3qnAi2rmGZvF8CN/c+DqNE6Am46Y3+ZQA/XjtNK2dfF5mnPhV+GIPePFI okKjrmFHFVAhqvJb0WynL3L3j+xW2GGtC7twhecB2a+EPGzbq1mG5mTSIVdRW617ah+a PfmKlaqgp2lG4otRbe+Kpz3WnNKOY/zyAkefKWQtNelApav2+wWjZkhGhjhPxpQrP9BQ YaOw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1780977481; x=1781582281; h=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=0UaUzyruzqOBAVLaXz6phlC0P38jjbSy47cCKdSQBcg=; b=oQ0iX/5m8sY6whdqSnYOiLny+qC55ZdGK7/tcRI+UQMtsX/6b9wwhSgNh3TQhyIYq3 dtE3411jIXFELIXZOsO+ZgWHgFYtnpMZKBsNwN1Dv7MxUGbYoBJTJvMn6HaeE+wBmt/9 MCFpVQdx6Mg5miDZMQmPQ4FjZ9EE4a25Hm2Cmk3VsZCEPNBV+XdcR4t7Rs8ZnzLM4hag +pND7cLRxCSbg9YE5mvs3xYcUqTQXEi3k8GkPO71XAAeUfDRkdUBuTBvP2/f1WRFcRMG kfDUOt7sId7/pGOicqDEDa2DFFPMG3xV12oeIU4X0Hsy0umfWhydr2gTz/GX5JGSlJY2 0PyA== X-Forwarded-Encrypted: i=1; AFNElJ+clerakQyonImMC+Lqs2rmHLDlimVLz50PKVDLmTkxNV6G7zwDPlDj8R6JJ7N/A1cCDM+qV+TCpkXV4wQf@lists.postgresql.org X-Gm-Message-State: AOJu0YznnV7iVrPoI9RDC8WW2vl+z4P7myYDoy9SQEN9eC9jBz+fgbBN D7WutpQ9jaefJp85TLKWL9dNCTYFxw93hoHpdpcNEwr00vX0MTBOWwec1oDKWkk9WOU4E1VGiXb EiC3k9rOq31txFxHpVQMCcAJ2vzxKqr4= X-Gm-Gg: Acq92OFOV99vZRJ2vSqIuM3z9X8ixn3fSL2Lr3aA96d6JjvyzWZe/4IPcOiRSX50UOE BANAHT6xQJXbe4JOqkNyFm8x76S/5qsP53z5HJCHujJxqv3kZ8Ud6PtTPRoo2FVjVEl8B+gNogN t9WIDurdGiE2tODDBf9b68r70EQOJQ4b/CTG5vVCDybnSsT4UeAbG6sX+8zlnYf5u4KDRwXlE37 qglVTQCUKLLjWxtbQblwPuSUWxftU6eOwvlEB5NezChlsZXHBwJisL3XKxB+1MbzF4EnOIE+DV9 LSQet3edKBQwUE//be4SShi+0aDREmSrgxrtn3GrWKNuRtgEkcmSjtFJXL4z8Q== X-Received: by 2002:a05:6512:124c:b0:5aa:8822:c947 with SMTP id 2adb3069b0e04-5aa8822c990mr5381254e87.49.1780977480557; Mon, 08 Jun 2026 20:58:00 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dilip Kumar Date: Tue, 9 Jun 2026 09:27:48 +0530 X-Gm-Features: AVVi8Cdsafpw-EMVOenT-2cqWjQw6w4yv1Ll3pKv5CHPZWUBQ034_C_8phJetfA Message-ID: Subject: Re: Proposal: Conflict log history table for Logical Replication To: shveta malik Cc: vignesh C , Nisha Moond , Amit Kapila , Peter Smith , Masahiko Sawada , Bharath Rupireddy , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="00000000000014e9e70653ca2276" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000014e9e70653ca2276 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, 9 Jun 2026 at 9:18=E2=80=AFAM, shveta malik wrote: > On Mon, Jun 8, 2026 at 7:01=E2=80=AFPM Dilip Kumar wrote: > > > > On Mon, Jun 8, 2026 at 11:43=E2=80=AFAM shveta malik > wrote: > > > > > > On Fri, Jun 5, 2026 at 4:22=E2=80=AFPM Dilip Kumar > wrote: > > > > > > > > On Fri, Jun 5, 2026 at 3:06=E2=80=AFPM shveta malik > wrote: > > > > > > > > > > On Fri, Jun 5, 2026 at 11:53=E2=80=AFAM Dilip Kumar > wrote: > > > > > > > > > > > > On Thu, Jun 4, 2026 at 4:05=E2=80=AFPM shveta malik < > shveta.malik@gmail.com> wrote: > > > > > > > > > > > > > > I noticed that it is currently possible to acquire explicit > locks on a CLT: > > > > > > > > > > > > > > --Session locks table and does not commit txn: > > > > > > > postgres=3D# BEGIN; > > > > > > > LOCK TABLE pg_conflict.pg_conflict_log_16481 IN SHARE MODE; > > > > > > > BEGIN > > > > > > > LOCK TABLE > > > > > > > > > > > > > > Doing so can cause the apply worker to block indefinitely whe= n > it > > > > > > > attempts to modify the CLT: > > > > > > > > > > > > > > [247433] LOG: logical replication apply worker for > subscription > > > > > > > "sub1" has started > > > > > > > [247433] LOG: process 247433 still waiting for > RowExclusiveLock on > > > > > > > relation 16482 of database 5 after 1001.030 ms > > > > > > > [247433] DETAIL: Process holding the lock: 245584. Wait > queue: 247433. > > > > > > > [247433] CONTEXT: waiting for RowExclusiveLock on relation > 16482 of database 5 > > > > > > > > > > > > > > Toast Table behaviour: > > > > > > > postgres=3D*# LOCK TABLE pg_toast.pg_toast_16384 IN SHARE MOD= E; > > > > > > > ERROR: cannot lock relation "pg_toast_16384" > > > > > > > DETAIL: This operation is not supported for TOAST tables. > > > > > > > > > > > > > > Should we consider disallowing explicit LOCK TABLE operations > on CLTs, > > > > > > > similar to how PostgreSQL handles TOAST tables? Or does anyon= e > see any > > > > > > > legitimate use-case (I don't) where we would need to allow > explicit > > > > > > > LOCKs on CLT? > > > > > > > > > > > > We need to add namespace-based checks here, as the current logi= c > > > > > > relies solely on relkind [1], which classifies TOAST tables > > > > > > separately. In my view, choosing to either allow or disallow th= is > > > > > > behavior will not cause significant inconvenience or seem > unusual to > > > > > > anyone. Therefore, I prefer the path that minimizes > special-purpose > > > > > > code. Since explicitly disallowing this requires additional > > > > > > special-purpose logic (as shown below [1]), allowing it seems t= o > be > > > > > > the cleaner approach. Thoughts? > > > > > > > > > > Okay, upon analyzing this new logic, I too prefer to allow it. > > > > > > > > > > I was thinking if there is a way to set lock_timeout in > > > > > ProcessPendingConflictLogTuple() or try to acquire lock and if it > > > > > fails we hit 'ERRCODE_LOCK_NOT_AVAILABLE', log a different warnin= g > in > > > > > the log file and let the apply worker proceed. > > > > > > > > > > But if this too is complicated, I am fine with the current > > > > > implementation. Since LOCK TABLE is a well-known command, if a us= er > > > > > explicitly locks a CLT, they should be responsible for the > > > > > consequences such as blocking the apply worker. > > > > > > > > +1 > > > > > > > > Here is the updated patch which fixes all open issues except Peter > > > > reported on 0004 patch, Vignesh would you take care of that? > > > > > > > > > > Thank You Dilip. > > > > > > v46-001: > > > > > > 1) > > > > > > +static bool alter_sub_conflictlogdestination(Subscription *sub, > > > + ConflictLogDest oldlogdest, > > > + ConflictLogDest newlogdest, > > > + Oid *conflicttablerelid); > > > > > > +static void drop_sub_conflict_log_table(Oid subid, char *subname, > > > + > > > > > > Can we name alter_sub_conflictlogdestination to > > > alter_sub_conflict_log_dest? Feel free to ignore if you find current > > > name better. > > > > Yeah we may change that. > > > > > 2) > > > Ran all the tests again on 0001 alone, inheritance is still working. > > > Let me know if we decided to retain it. IMO, it is better to block i= t > > > for the reasons stated earlier. The changes can be made in > > > MergeAttributes and ATExecAddInherit; we already have similar relatio= n > > > based restrictions there, one more can be added for CLT. > > > > > > ~~ > > > > > > No major issue in 0001, it seems be in good shape. Will do one more > > > round of reveiw and testing on next version though. > > > > After rethinking my previous stance on blocking these operations, let > > me clarify the core principle I think we should follow for CLTs. I am > > completely open to feedback on this approach: > > > > 1. Block Direct Mutations: We should block any operation that directly > > modifies the CLT or its underlying data (e.g., DROP TABLE, ALTER > > TABLE, INSERT, UPDATE), which impact the operation on CLT or update > > the CLT data. > > 2. Don't block Indirect/Edge-Case Operations: We should not write > > custom code just to block edge cases that don't directly modify CLT > > data or impact the operations on CLT. For example, if a user decides > > to inherit from a CLT, that constitutes unexpected usage. We already > > document (or can document) that dropping a subscription internally > > drops the associated CLT. If a user inherits from it anyway and their > > child tables are impacted when the subscription is dropped, that is > > expected behavior and their usage issue. > > Fair enough. I'm okay with this approach, provided we document it > clearly, perhaps as a CAUTION: users must be aware that DROP > SUBSCRIPTION cascades the drop to the CLT and all its dependent > objects, including any user-created inherited tables, view etc Make sense.. > --00000000000014e9e70653ca2276 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On T= ue, 9 Jun 2026 at 9:18=E2=80=AFAM, shveta malik <shveta.malik@gmail.com> wrote:
On Mon, Jun 8, 2026 at 7:01=E2=80=AFPM Di= lip Kumar <di= lipbalaut@gmail.com> wrote:
>
> On Mon, Jun 8, 2026 at 11:43=E2=80=AFAM shveta malik <shveta.malik@gmail.com&g= t; wrote:
> >
> > On Fri, Jun 5, 2026 at 4:22=E2=80=AFPM Dilip Kumar <dilipbalaut@gmail.com&= gt; wrote:
> > >
> > > On Fri, Jun 5, 2026 at 3:06=E2=80=AFPM shveta malik <shveta.malik@gmail.= com> wrote:
> > > >
> > > > On Fri, Jun 5, 2026 at 11:53=E2=80=AFAM Dilip Kumar <= ;dilipbalaut@gma= il.com> wrote:
> > > > >
> > > > > On Thu, Jun 4, 2026 at 4:05=E2=80=AFPM shveta mali= k <shveta.ma= lik@gmail.com> wrote:
> > > > > >
> > > > > > I noticed that it is currently possible to ac= quire explicit locks on a CLT:
> > > > > >
> > > > > > --Session locks table and does not commit txn= :
> > > > > > postgres=3D# BEGIN;
> > > > > > LOCK TABLE pg_conflict.pg_conflict_log_16481 = IN SHARE MODE;
> > > > > > BEGIN
> > > > > > LOCK TABLE
> > > > > >
> > > > > > Doing so can cause the apply worker to block = indefinitely when it
> > > > > > attempts to modify the CLT:
> > > > > >
> > > > > > [247433] LOG:=C2=A0 logical replication apply= worker for subscription
> > > > > > "sub1" has started
> > > > > > [247433] LOG:=C2=A0 process 247433 still wait= ing for RowExclusiveLock on
> > > > > > relation 16482 of database 5 after 1001.030 m= s
> > > > > > [247433] DETAIL:=C2=A0 Process holding the lo= ck: 245584. Wait queue: 247433.
> > > > > > [247433] CONTEXT:=C2=A0 waiting for RowExclus= iveLock on relation 16482 of database 5
> > > > > >
> > > > > > Toast Table behaviour:
> > > > > > postgres=3D*# LOCK TABLE pg_toast.pg_toast_16= 384 IN SHARE MODE;
> > > > > > ERROR:=C2=A0 cannot lock relation "pg_to= ast_16384"
> > > > > > DETAIL:=C2=A0 This operation is not supported= for TOAST tables.
> > > > > >
> > > > > > Should we consider disallowing explicit LOCK = TABLE operations on CLTs,
> > > > > > similar to how PostgreSQL handles TOAST table= s? Or does anyone see any
> > > > > > legitimate use-case (I don't) where we wo= uld need to allow explicit
> > > > > > LOCKs on CLT?
> > > > >
> > > > > We need to add namespace-based checks here, as the= current logic
> > > > > relies solely on relkind [1], which classifies TOA= ST tables
> > > > > separately. In my view, choosing to either allow o= r disallow this
> > > > > behavior will not cause significant inconvenience = or seem unusual to
> > > > > anyone. Therefore, I prefer the path that minimize= s special-purpose
> > > > > code. Since explicitly disallowing this requires a= dditional
> > > > > special-purpose logic (as shown below [1]), allowi= ng it seems to be
> > > > > the cleaner approach.=C2=A0 Thoughts?
> > > >
> > > > Okay, upon analyzing this new logic,=C2=A0 I too prefer= to allow it.
> > > >
> > > > I was thinking if there is a way to set lock_timeout in=
> > > > ProcessPendingConflictLogTuple() or try to acquire lock= and if it
> > > > fails we hit 'ERRCODE_LOCK_NOT_AVAILABLE', log = a different warning in
> > > > the log file and let the apply worker proceed.
> > > >
> > > > But if this too is complicated, I am fine with the curr= ent
> > > > implementation. Since LOCK TABLE is a well-known comman= d, if a user
> > > > explicitly locks a CLT, they should be responsible for = the
> > > > consequences such as blocking the apply worker.
> > >
> > > +1
> > >
> > > Here is the updated patch which fixes all open issues except= Peter
> > > reported on 0004 patch, Vignesh would you take care of that?=
> > >
> >
> > Thank You Dilip.
> >
> > v46-001:
> >
> > 1)
> >
> > +static bool alter_sub_conflictlogdestination(Subscription *sub,<= br> > > + ConflictLogDest oldlogdest,
> > + ConflictLogDest newlogdest,
> > + Oid *conflicttablerelid);
> >
> > +static void drop_sub_conflict_log_table(Oid subid, char *subname= ,
> > +
> >
> > Can we name alter_sub_conflictlogdestination to
> > alter_sub_conflict_log_dest?=C2=A0 Feel free to ignore if you fin= d current
> > name better.
>
> Yeah we may change that.
>
> > 2)
> > Ran all the tests again on 0001 alone, inheritance is still worki= ng.
> > Let me know if we decided to retain it.=C2=A0 IMO, it is better t= o block it
> > for the reasons stated earlier.=C2=A0 The changes can be made in<= br> > > MergeAttributes and ATExecAddInherit; we already have similar rel= ation
> > based restrictions there, one more can be added for CLT.
> >
> > ~~
> >
> > No major issue in 0001, it seems be in good shape. Will do one mo= re
> > round of reveiw and testing on next version though.
>
> After rethinking my previous stance on blocking these operations, let<= br> > me clarify the core principle I think we should follow for CLTs. I am<= br> > completely open to feedback on this approach:
>
> 1. Block Direct Mutations: We should block any operation that directly=
> modifies the CLT or its underlying data (e.g., DROP TABLE, ALTER
> TABLE, INSERT, UPDATE), which impact the operation on CLT or update > the CLT data.
> 2. Don't block Indirect/Edge-Case Operations: We should not write<= br> > custom code just to block edge cases that don't directly modify CL= T
> data or impact the operations on CLT. For example, if a user decides > to inherit from a CLT, that constitutes unexpected usage. We already > document (or can document) that dropping a subscription internally
> drops the associated CLT. If a user inherits from it anyway and their<= br> > child tables are impacted when the subscription is dropped, that is > expected behavior and their usage issue.

Fair enough. I'm okay with this approach, provided we document it
clearly, perhaps as a CAUTION: users must be aware that DROP
SUBSCRIPTION cascades the drop to the CLT and all its dependent
objects, including any user-created inherited tables, view etc=

Make sense..
--00000000000014e9e70653ca2276--