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
