On Sat, Mar 28, 2026 at 9:11 AM Roberto Mello <[email protected]> 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
v2-0001-Emit-WARNING-when-publication-row-filter-columns.patch
Description: Binary data
