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

On Sat, Mar 28, 2026 at 9:11 AM Roberto Mello <roberto(dot)mello(at)gmail(dot)com>
wrote:

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

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.

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