Hi,
ALTER TABLE ... REPLICA IDENTITY USING INDEX checks key columns by
reading attnotnull, but since a379061a22a8 (PG 18, NOT NULL NOT VALID)
attnotnull is set even when the constraint is unvalidated. An index on
a column that actually contains NULLs is therefore accepted as replica
identity:
CREATE TABLE t (id int);
INSERT INTO t VALUES (1), (NULL), (2);
ALTER TABLE t ADD CONSTRAINT id_nn NOT NULL id NOT VALID;
CREATE UNIQUE INDEX t_idx ON t(id);
ALTER TABLE t REPLICA IDENTITY USING INDEX t_idx; -- accepted
-- relreplident => 'i'
This would cause data divergence on UPDATE/DELETE targeting NULL-keyed rows.
Patch follows same pattern as d9ffc27291f (the same bugfix for identity
columns);
after the attnotnull check, look up the constraint and reject if
!convalidated.
Thanks,
Ante Kresic
Staff Engineer | Tiger Data
TigerData.com
From 25f9e78dd03ad1fd67d435bdfbdd563db3938375 Mon Sep 17 00:00:00 2001
From: Ante Kresic <[email protected]>
Date: Wed, 13 May 2026 10:47:02 +0200
Subject: [PATCH] Reject REPLICA IDENTITY USING INDEX on column with invalid
NOT NULL
ALTER TABLE ... REPLICA IDENTITY USING INDEX verified key columns by
reading pg_attribute.attnotnull, but commit a379061a22a8 made
attnotnull true also for unvalidated (NOT VALID) not-null constraints,
which do not prove the column null-free. An index over such a column
could thus be marked as replica identity even though the column might
contain NULLs, causing apply-side divergence for UPDATE/DELETE on the
nullable rows.
Fix by additionally requiring convalidated for the underlying
constraint, mirroring the fix d9ffc27291f applied to ATExecAddIdentity
for the analogous identity-column case.
---
src/backend/commands/tablecmds.c | 29 +++++++++++++++++++
.../regress/expected/replica_identity.out | 23 +++++++++++++++
src/test/regress/sql/replica_identity.sql | 16 ++++++++++
3 files changed, 68 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 88451c91448..9944df931ad 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -18853,6 +18853,35 @@ ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode
errmsg("index \"%s\" cannot be used as replica identity because column \"%s\" is nullable",
RelationGetRelationName(indexRel),
NameStr(attr->attname))));
+
+ /*
+ * attnotnull is set even for invalid (NOT VALID) not-null
+ * constraints, which do not prove the column is null-free, so verify
+ * that the underlying constraint is validated.
+ */
+ {
+ HeapTuple contup;
+ Form_pg_constraint conForm;
+
+ contup = findNotNullConstraintAttnum(RelationGetRelid(rel), attno);
+ if (!HeapTupleIsValid(contup))
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"",
+ NameStr(attr->attname), RelationGetRelationName(rel));
+
+ conForm = (Form_pg_constraint) GETSTRUCT(contup);
+ if (!conForm->convalidated)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("index \"%s\" cannot be used as replica identity because column \"%s\" has an invalid not-null constraint",
+ RelationGetRelationName(indexRel),
+ NameStr(attr->attname)),
+ /*- translator: second %s is a constraint characteristic such as NOT VALID */
+ errdetail("The constraint \"%s\" is marked %s.",
+ NameStr(conForm->conname), "NOT VALID"),
+ errhint("You might need to validate it using %s.",
+ "ALTER TABLE ... VALIDATE CONSTRAINT"));
+ heap_freetuple(contup);
+ }
}
/* This index is suitable for use as a replica identity. Mark it. */
diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out
index 336b04fa278..7be6ab0115e 100644
--- a/src/test/regress/expected/replica_identity.out
+++ b/src/test/regress/expected/replica_identity.out
@@ -292,10 +292,33 @@ ALTER TABLE test_replica_identity5 DROP CONSTRAINT test_replica_identity5_pkey;
ERROR: constraint "test_replica_identity5_pkey" of relation "test_replica_identity5" does not exist
ALTER TABLE test_replica_identity5 ALTER b DROP NOT NULL;
ERROR: column "b" is in index used as replica identity
+-- An invalid (NOT VALID) not-null constraint sets attnotnull but does not
+-- prove the column null-free, so the index must not be accepted as replica
+-- identity until the constraint is validated.
+CREATE TABLE test_replica_identity6 (id int);
+INSERT INTO test_replica_identity6 VALUES (1), (NULL);
+ALTER TABLE test_replica_identity6 ADD CONSTRAINT id_nn NOT NULL id NOT VALID;
+CREATE UNIQUE INDEX test_replica_identity6_idx ON test_replica_identity6 (id);
+-- should fail
+ALTER TABLE test_replica_identity6 REPLICA IDENTITY USING INDEX test_replica_identity6_idx;
+ERROR: index "test_replica_identity6_idx" cannot be used as replica identity because column "id" has an invalid not-null constraint
+DETAIL: The constraint "id_nn" is marked NOT VALID.
+HINT: You might need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT.
+-- after removing offending row and validating, it should succeed
+DELETE FROM test_replica_identity6 WHERE id IS NULL;
+ALTER TABLE test_replica_identity6 VALIDATE CONSTRAINT id_nn;
+ALTER TABLE test_replica_identity6 REPLICA IDENTITY USING INDEX test_replica_identity6_idx;
+SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity6'::regclass;
+ relreplident
+--------------
+ i
+(1 row)
+
DROP TABLE test_replica_identity;
DROP TABLE test_replica_identity2;
DROP TABLE test_replica_identity3;
DROP TABLE test_replica_identity4;
DROP TABLE test_replica_identity5;
+DROP TABLE test_replica_identity6;
DROP TABLE test_replica_identity_othertable;
DROP TABLE test_replica_identity_t3;
diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql
index 30daec05b71..b202b30ae2b 100644
--- a/src/test/regress/sql/replica_identity.sql
+++ b/src/test/regress/sql/replica_identity.sql
@@ -134,10 +134,26 @@ ALTER TABLE test_replica_identity5 ALTER b SET NOT NULL;
ALTER TABLE test_replica_identity5 DROP CONSTRAINT test_replica_identity5_pkey;
ALTER TABLE test_replica_identity5 ALTER b DROP NOT NULL;
+-- An invalid (NOT VALID) not-null constraint sets attnotnull but does not
+-- prove the column null-free, so the index must not be accepted as replica
+-- identity until the constraint is validated.
+CREATE TABLE test_replica_identity6 (id int);
+INSERT INTO test_replica_identity6 VALUES (1), (NULL);
+ALTER TABLE test_replica_identity6 ADD CONSTRAINT id_nn NOT NULL id NOT VALID;
+CREATE UNIQUE INDEX test_replica_identity6_idx ON test_replica_identity6 (id);
+-- should fail
+ALTER TABLE test_replica_identity6 REPLICA IDENTITY USING INDEX test_replica_identity6_idx;
+-- after removing offending row and validating, it should succeed
+DELETE FROM test_replica_identity6 WHERE id IS NULL;
+ALTER TABLE test_replica_identity6 VALIDATE CONSTRAINT id_nn;
+ALTER TABLE test_replica_identity6 REPLICA IDENTITY USING INDEX test_replica_identity6_idx;
+SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity6'::regclass;
+
DROP TABLE test_replica_identity;
DROP TABLE test_replica_identity2;
DROP TABLE test_replica_identity3;
DROP TABLE test_replica_identity4;
DROP TABLE test_replica_identity5;
+DROP TABLE test_replica_identity6;
DROP TABLE test_replica_identity_othertable;
DROP TABLE test_replica_identity_t3;
--
2.53.0
