From cf8be022538937fe9fe22de776fb8cfe6460a784 Mon Sep 17 00:00:00 2001 From: Álvaro Herrera Date: Mon, 3 Nov 2025 15:58:19 +0100 Subject: Prevent setting a column as identity if its not-null constraint is invalid We don't allow null values to appear in identity-generated columns in other ways, so we shouldn't let unvalidated not-null constraints do it either. Oversight in commit a379061a22a8. Author: jian he Backpatch-through: 18 Discussion: https://postgr.es/m/CACJufxGQM_+vZoYJMaRoZfNyV=L2jxosjv_0TLAScbuLJXWRfQ@mail.gmail.com --- src/backend/commands/tablecmds.c | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) (limited to 'src/backend/commands/tablecmds.c') diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 5fd8b51312c..34f9e342362 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8281,6 +8281,31 @@ ATExecAddIdentity(Relation rel, const char *colName, errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL before identity can be added", colName, RelationGetRelationName(rel)))); + /* + * On the other hand, if a not-null constraint exists, then verify that + * it's compatible. + */ + if (attTup->attnotnull) + { + HeapTuple contup; + Form_pg_constraint conForm; + + contup = findNotNullConstraintAttnum(RelationGetRelid(rel), + attnum); + if (!HeapTupleIsValid(contup)) + elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"", + colName, RelationGetRelationName(rel)); + + conForm = (Form_pg_constraint) GETSTRUCT(contup); + if (!conForm->convalidated) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("incompatible NOT VALID constraint \"%s\" on relation \"%s\"", + NameStr(conForm->conname), RelationGetRelationName(rel)), + errhint("You might need to validate it using %s.", + "ALTER TABLE ... VALIDATE CONSTRAINT")); + } + if (attTup->attidentity) ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), -- cgit v1.2.3