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.
