Subject: PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434)

From: Roberto Mello <[email-masked]>
Date: Sat, 28 Mar 2026 09:11:17 -0600

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

---

From: Roberto Mello <[email-masked]>
Date: Wed, 1 Apr 2026 17:25:58 -0600

> inadvertently disallowing writes in a production system.

The attached patch adds DDL-time WARNINGs so the misconfiguration is
reported immediately.  The warnings fire at:

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

Notes:

  - The check reuses the existing pub_rf_contains_invalid_column()
    function, which walks the WHERE expression tree and compares
    referenced columns against the replica identity bitmap.

  - For the publication DDL paths (CREATE/ALTER PUBLICATION ...
    ADD/SET TABLE), a CommandCounterIncrement() is needed after
    PublicationAddTables() so that the newly inserted
    pg_publication_rel rows are visible to the syscache.

  - For the ALTER PUBLICATION SET (publish = ...) path, the existing
    CommandCounterIncrement() after CatalogTupleUpdate() already
    makes the updated publish flags visible.  The check iterates
    the publication's tables via GetIncludedPublicationRelations().

  - For the ALTER TABLE path, a CommandCounterIncrement() followed
    by a fresh table_open() ensures the relcache reflects the new
    replica identity before running the check.

  - Partition handling uses the existing get_partition_ancestors()
    and pubviaroot logic.

  - Regression tests are updated to expect the new WARNINGs and
    include new targeted test cases, covering both positive
    (warning fires) and negative (INSERT-only, FULL identity) cases.

  - Documentation updates in logical-replication.sgml.

Known limitations:

  - ALTER PUBLICATION SET (publish_via_partition_root = ...) is not
    checked.  This is a narrow edge case involving partitioned tables
    and is deferred to a follow-up.

  - DROP INDEX on a replica-identity index is not checked due to
    layering concerns (would require publication code in
    catalog/index.c).

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

This is a v2 that incorporates fixes, including documentation emphasis.

Roberto Mello
Snowflake

---

From: "Zhijie Hou (Fujitsu)" <[email-masked]>
Date: Thu, 2 Apr 2026 09:21:20 +0000

On Thursday, April 2, 2026 7:26 AM Roberto Mello <[email-masked]>  wrote:
> 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.

Thanks for raising this point.

> 
> The attached patch adds DDL-time WARNINGs so the misconfiguration is reported
> immediately.  The warnings fire at:
> 

I recall that the reason we did not add hard restrictions for all those cases is
that it is tricky to catch every DDL that might bypass the restrictions. Even if
we were to identify them today, implementing the necessary checks would require
a significant amount of code, and future DDL changes could introduce new ways to
bypass the restrictions without us noticing. All of this suggests that the
effort and complexity involved could not be justified by the value of such
checks. Therefore, we decided to handle these cases in a uniform place (e.g.,
during DML processing).

That said, while it might be acceptable to add a few simple checks for the most
common cases, I notice that the patch seems to lack handling for partitioned
tables. Specifically, when a partitioned table is added to a publication with
pubviaroot = true and a valid row filter, we may need to verify that the replica
identity of every leaf partition is sufficient to support the row filter. I
recall this part would add some complexity.

Considering that the stated plan for this thread is to eventually WAL‑log the
columns needed for row filters regardless of replica identity, I suspect it
might not be ideal to introduce a large amount of code to emit warnings at this
stage. If we support logging the necessary columns in the WAL and enable that
behavior by default, these checks might no longer be useful. However, if we do
not enable WAL logging of row‑filter columns by default, then such checks could
still have some value.

>     layering concerns (would require publication code in

In addition to above cases, I think we also need to check the replica identity
whenever a new partition is attached to a published partitioned table that has a
row filter. The relevant DDLs include ATTACH PARTITION and CREATE PARTITION ...
OF... And, the recently introduced SPLIT PARTITION command might also have an
impact, although I cannot say for certain without a closer look.

Best Regards,
Hou zj