Commitcf8be02

committed

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 inother ways, so we shouldn't let unvalidated not-null constraints do iteither. Oversight in commita379061.Author: jian he <jian.universality@gmail.com>Backpatch-through: 18Discussion:https://postgr.es/m/CACJufxGQM_+vZoYJMaRoZfNyV=L2jxosjv_0TLAScbuLJXWRfQ@mail.gmail.com

1 parentf242dbc commitcf8be02Copy full SHA for cf8be02

File tree

+32

-0

lines changed

+32

-0

lines changed

Lines changed: 25 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -8281,6 +8281,31 @@ ATExecAddIdentity(Relation rel, const char *colName,`
`8281`	`8281`	`errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL before identity can be added",`
`8282`	`8282`	`colName, RelationGetRelationName(rel))));`
`8283`	`8283`
	`8284`	`+/*`
	`8285`	`+ * On the other hand, if a not-null constraint exists, then verify that`
	`8286`	`+ * it's compatible.`
	`8287`	`+ */`
	`8288`	`+if (attTup->attnotnull)`
	`8289`	`+{`
	`8290`	`+HeapTuplecontup;`
	`8291`	`+Form_pg_constraint conForm;`
	`8292`	`+`
	`8293`	`+contup = findNotNullConstraintAttnum(RelationGetRelid(rel),`
	`8294`	`+ attnum);`
	`8295`	`+if (!HeapTupleIsValid(contup))`
	`8296`	`+elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"",`
	`8297`	`+ colName, RelationGetRelationName(rel));`
	`8298`	`+`
	`8299`	`+conForm = (Form_pg_constraint) GETSTRUCT(contup);`
	`8300`	`+if (!conForm->convalidated)`
	`8301`	`+ereport(ERROR,`
	`8302`	`+errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),`
	`8303`	`+errmsg("incompatible NOT VALID constraint \"%s\" on relation \"%s\"",`
	`8304`	`+ NameStr(conForm->conname), RelationGetRelationName(rel)),`
	`8305`	`+errhint("You might need to validate it using %s.",`
	`8306`	`+"ALTER TABLE ... VALIDATE CONSTRAINT"));`
	`8307`	`+}`
	`8308`	`+`
`8284`	`8309`	`if (attTup->attidentity)`
`8285`	`8310`	`ereport(ERROR,`
`8286`	`8311`	`(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),`

Lines changed: 4 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -1404,6 +1404,10 @@ ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);`
`1404`	`1404`	`ERROR: cannot create primary key on column "a"`
`1405`	`1405`	`DETAIL: The constraint "nn" on column "a" of table "notnull_tbl1", marked NOT VALID, is incompatible with a primary key.`
`1406`	`1406`	`HINT: You might need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT.`
	`1407`	`+-- cannot set column as generated-as-identity if it has an invalid not-null`
	`1408`	`+ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;`
	`1409`	`+ERROR: incompatible NOT VALID constraint "nn" on relation "notnull_tbl1"`
	`1410`	`+HINT: You might need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT.`
`1407`	`1411`	`-- ALTER column SET NOT NULL validates an invalid constraint (but this fails`
`1408`	`1412`	`-- because of rows with null values)`
`1409`	`1413`	`ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;`

Lines changed: 3 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -832,6 +832,9 @@ ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;`
`832`	`832`	`-- cannot add primary key on a column with an invalid not-null`
`833`	`833`	`ALTERTABLE notnull_tbl1 ADDPRIMARY KEY (a);`
`834`	`834`
	`835`	`+-- cannot set column as generated-as-identity if it has an invalid not-null`
	`836`	`+ALTERTABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYSAS IDENTITY;`
	`837`	`+`
`835`	`838`	`-- ALTER column SET NOT NULL validates an invalid constraint (but this fails`
`836`	`839`	`-- because of rows with null values)`
`837`	`840`	`ALTERTABLE notnull_tbl1 ALTER aSETNOT NULL;`

Comments

(0)