RE: PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434) - Mailing list pgsql-hackers

On Thursday, April 2, 2026 7:26 AM Roberto Mello <roberto.mello@gmail.com>  wrote:
> 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.

Thanks for raising this point.

> 
> The attached patch adds DDL-time WARNINGs so the misconfiguration is reported
> immediately.  The warnings 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 is preserved as a safety net.

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.

> 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).

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