MIME-Version: 1.0
From: Roberto Mello <roberto.mello@gmail.com>
Date: Sat, 28 Mar 2026 09:11:17 -0600
Message-ID: 
 <CAKz==bJXZxTa-++31aSbPhfUNdRtD-Y7oBF=W193cL1er6L-BQ@mail.gmail.com>
Subject: PROPOSAL for when publication row filter columns are not in replica
 identity (BUG #19434)
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000e3f5c064e1709fa"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKz%3D%3DbJXZxTa-%2B%2B31aSbPhfUNdRtD-Y7oBF%3DW193cL1er6L-BQ%40mail.gmail.com>
Precedence: bulk

--0000000000000e3f5c064e1709fa
Content-Type: text/plain; charset="UTF-8"

Hi all,

Tim McLaughlin reported BUG #19434.

When a publication's WHERE clause references columns that are not
covered by the table's replica identity, UPDATE and DELETE silently
succeed at the SQL level but fail with:

    ERROR: cannot update table "t"
    DETAIL: Column used in the publication WHERE expression is not part
            of the replica identity.

This error fires at DML time inside CheckCmdReplicaIdentity(), which
means the DBA discovers the misconfiguration only when production
writes start failing, potentially long after the publication or replica
identity
was created, and creating a real potentially serious problem of
inadvertently disallowing writes in a production system.

I have a patch that adds DDL-time WARNINGs (reusing the existing
pub_rf_contains_invalid_column() function) so the misconfiguration is
reported
immediately, but I am wondering if this is the right approach. It's doable
and
not very invasive, but wouldn't really do away with the potential footgun.
It would be an incremental improvement over the current situation where
this happens silently
(in reporter's case it was a serious production issue).

The warnings would fire at:

  - CREATE PUBLICATION / ALTER PUBLICATION ... SET TABLE / ADD TABLE
    when the WHERE clause references non-identity columns

  - ALTER PUBLICATION SET (publish = ...) when the publish set is
    widened to include UPDATE or DELETE while existing row filters
    reference non-identity columns

  - ALTER TABLE ... REPLICA IDENTITY when the new identity no longer
    covers columns used in an existing publication WHERE clause

The existing DML-time ERROR would be preserved as a safety net.

The patch would not change the WAL format or remove the underlying
restriction. A follow-up patch could extend ExtractReplicaIdentity()
to include WHERE-referenced columns in WAL, which would eliminate the
restriction entirely.

Question being if the incremental and less invasive WARNING approach is
the better one given time constraints, or if a more invasive but more
complete
approach is warranted.

Thoughts?

Roberto Mello
Snowflake

--0000000000000e3f5c064e1709fa
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi all,<br><br>Tim McLaughlin reported BUG #19434.=C2=A0<d=
iv><br>When a publication&#39;s WHERE clause references columns that are no=
t<br>covered by the table&#39;s replica identity, UPDATE and DELETE silentl=
y<br>succeed at the SQL level but fail with:<br><br>=C2=A0 =C2=A0 ERROR: ca=
nnot update table &quot;t&quot;<br>=C2=A0 =C2=A0 DETAIL: Column used in the=
 publication WHERE expression is not part<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 of the replica identity.<br><br>This error fires at DML time =
inside CheckCmdReplicaIdentity(), which<br>means the DBA discovers the misc=
onfiguration only when production<br>writes start failing, potentially long=
 after the publication or replica identity</div><div>was created, and creat=
ing a real potentially serious problem of</div><div>inadvertently disallowi=
ng writes in a production system.</div><div><br></div><div>I have a patch t=
hat adds DDL-time WARNINGs (reusing the existing</div><div>pub_rf_contains_=
invalid_column() function) so the misconfiguration is reported</div><div>im=
mediately, but I am wondering if this is the right approach. It&#39;s doabl=
e and</div><div>not very invasive, but wouldn&#39;t really do away with the=
 potential footgun. It would be an incremental improvement over the current=
 situation where this happens silently</div><div>(in reporter&#39;s case it=
 was a serious production issue).</div><div><br></div><div>The warnings wou=
ld fire at:<br><br>=C2=A0 - CREATE PUBLICATION / ALTER PUBLICATION ... SET =
TABLE / ADD TABLE<br>=C2=A0 =C2=A0 when the WHERE clause references non-ide=
ntity columns<br><br>=C2=A0 - ALTER PUBLICATION SET (publish =3D ...) when =
the publish set is<br>=C2=A0 =C2=A0 widened to include UPDATE or DELETE whi=
le existing row filters<br>=C2=A0 =C2=A0 reference non-identity columns<br>=
<br>=C2=A0 - ALTER TABLE ... REPLICA IDENTITY when the new identity no long=
er<br>=C2=A0 =C2=A0 covers columns used in an existing publication WHERE cl=
ause<br><br>The existing DML-time ERROR would be preserved as a safety net.=
<br><br>The patch would not change the WAL format or remove the underlying<=
br>restriction. A follow-up patch could extend ExtractReplicaIdentity()<br>=
to include WHERE-referenced columns in WAL, which would eliminate the<br>re=
striction entirely.=C2=A0</div><div><br></div><div>Question being if the in=
cremental and less invasive WARNING approach is</div><div>the better one gi=
ven time constraints, or if a more invasive but more complete</div><div>app=
roach is warranted.<br><br></div><div>Thoughts?</div><div><br></div><div>Ro=
berto Mello</div><div>Snowflake</div><div><br></div></div>

--0000000000000e3f5c064e1709fa--