On 2024-Apr-11, Alvaro Herrera wrote:
> Well, I think you were right that we should try to handle the situation
> of unmarking attnotnull as much as possible, to decrease the chances
> that the problematic situation occurs. That means, we can use the
> earlier code to handle DROP COLUMN when it causes a PK to be dropped --
> even though we still need to handle the situation of an attnotnull flag
> set with no pg_constraint row. I mean, we still have to handle DROP
> DOMAIN correctly (and there might be other cases that I haven't thought
> about) ... but surely this is a much less common situation than the one
> you reported. So I'll merge everything and post an updated patch.
Here's roughly what I'm thinking. If we drop a constraint, we can still
reset attnotnull in RemoveConstraintById(), but only after checking that
it's not a generated column or a replica identity. If they are, we
don't error out -- just skip the attnotnull update.
Now, about the code to allow ALTER TABLE DROP NOT NULL in case there's
no pg_constraint row, I think at this point it's mostly dead code,
because it can only happen when you have a replica identity or generated
column ... and the DROP NOT NULL should still prevent you from dropping
the flag anyway. But the case can still arise, if you change the
replica identity or ALTER TABLE ALTER COLUMN DROP DEFAULT, respectively.
I'm still not ready with this -- still not convinced about the new AT
pass. Also, I want to add a test for the pg_dump behavior, and there's
an XXX comment.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"The eagle never lost so much time, as
when he submitted to learn of the crow." (William Blake)
>From ce8fff0ccb568a601ac9f765da7b7891066f522b Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Thu, 11 Apr 2024 12:29:34 +0200
Subject: [PATCH] Better handle indirect constraint drops
It is possible for certain cases to remove constraints without
maintaining the attnotnull in its correct state; for example if you drop
a column that's part of the primary key, and the remaining columns don't
have not-null constraints, then we should reset the attnotnull flags for
those other columns, but we don't. We can handle those cases better by
doing the attnotnull reset in RemoveConstraintById() instead of in
dropconstraint_internal().
However, there are some cases where we must not do so. For example if
those other columns are in replica identity indexes or are generated
identity columns, we must keep the flag, even though it results in the
catalog inconsistency that no constraint supports the flag.
Because of these exceptions, we also include a pg_dump hack to include a
not-null constraint when the attnotnull flag is set even if no
pg_constraint row exists. This part is undesirable but necessary,
because failing to handle the case can result in unrestorable dumps.
---
src/backend/catalog/pg_constraint.c | 116 +++++++++++++++++++++-
src/backend/commands/tablecmds.c | 108 ++++++++++----------
src/bin/pg_dump/pg_dump.c | 30 ++++--
src/test/regress/expected/constraints.out | 73 ++++++++++++++
src/test/regress/sql/constraints.sql | 34 +++++++
5 files changed, 301 insertions(+), 60 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 604280d322..c48acf00b0 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -19,6 +19,7 @@
#include "access/htup_details.h"
#include "access/sysattr.h"
#include "access/table.h"
+#include "access/xact.h"
#include "catalog/catalog.h"
#include "catalog/dependency.h"
#include "catalog/heap.h"
@@ -916,6 +917,8 @@ RemoveConstraintById(Oid conId)
Relation conDesc;
HeapTuple tup;
Form_pg_constraint con;
+ bool dropping_pk = false;
+ List *unconstrained_cols = NIL;
conDesc = table_open(ConstraintRelationId, RowExclusiveLock);
@@ -940,7 +943,9 @@ RemoveConstraintById(Oid conId)
/*
* We need to update the relchecks count if it is a check constraint
* being dropped. This update will force backends to rebuild relcache
- * entries when we commit.
+ * entries when we commit. For not-null and primary key constraints,
+ * obtain the list of columns affected, so that we can reset their
+ * attnotnull flags below.
*/
if (con->contype == CONSTRAINT_CHECK)
{
@@ -967,6 +972,36 @@ RemoveConstraintById(Oid conId)
table_close(pgrel, RowExclusiveLock);
}
+ else if (con->contype == CONSTRAINT_NOTNULL)
+ {
+ unconstrained_cols = list_make1_int(extractNotNullColumn(tup));
+ }
+ else if (con->contype == CONSTRAINT_PRIMARY)
+ {
+ Datum adatum;
+ ArrayType *arr;
+ int numkeys;
+ bool isNull;
+ int16 *attnums;
+
+ dropping_pk = true;
+
+ adatum = heap_getattr(tup, Anum_pg_constraint_conkey,
+ RelationGetDescr(conDesc), &isNull);
+ if (isNull)
+ elog(ERROR, "null conkey for constraint %u", con->oid);
+ arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
+ numkeys = ARR_DIMS(arr)[0];
+ if (ARR_NDIM(arr) != 1 ||
+ numkeys < 0 ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != INT2OID)
+ elog(ERROR, "conkey is not a 1-D smallint array");
+ attnums = (int16 *) ARR_DATA_PTR(arr);
+
+ for (int i = 0; i < numkeys; i++)
+ unconstrained_cols = lappend_int(unconstrained_cols, attnums[i]);
+ }
/* Keep lock on constraint's rel until end of xact */
table_close(rel, NoLock);
@@ -986,6 +1021,85 @@ RemoveConstraintById(Oid conId)
/* Fry the constraint itself */
CatalogTupleDelete(conDesc, &tup->t_self);
+ /*
+ * If this was a NOT NULL or the primary key, the constrained columns must
+ * have had pg_attribute.attnotnull set. See if we need to reset it, and
+ * do so.
+ */
+ if (unconstrained_cols)
+ {
+ Relation tablerel;
+ Relation attrel;
+ Bitmapset *pkcols;
+ ListCell *lc;
+
+ /* Make the above deletion visible */
+ CommandCounterIncrement();
+
+ tablerel = table_open(con->conrelid, NoLock); /* already have lock */
+ attrel = table_open(AttributeRelationId, RowExclusiveLock);
+
+ /*
+ * We want to test columns for their presence in the primary key, but
+ * only if we're not dropping it.
+ */
+ pkcols = dropping_pk ? NULL :
+ RelationGetIndexAttrBitmap(tablerel,
+ INDEX_ATTR_BITMAP_PRIMARY_KEY);
+
+ foreach(lc, unconstrained_cols)
+ {
+ AttrNumber attnum = lfirst_int(lc);
+ HeapTuple atttup;
+ HeapTuple contup;
+ Bitmapset *ircols;
+ Form_pg_attribute attForm;
+
+ /*
+ * Obtain pg_attribute tuple and verify conditions on it. We use
+ * a copy we can scribble on.
+ */
+ atttup = SearchSysCacheCopyAttNum(con->conrelid, attnum);
+ if (!HeapTupleIsValid(atttup))
+ elog(ERROR, "cache lookup failed for attribute %d of relation %u",
+ attnum, con->conrelid);
+ attForm = (Form_pg_attribute) GETSTRUCT(atttup);
+
+ /*
+ * Since the above deletion has been made visible, we can now
+ * search for any remaining constraints on this column (or these
+ * columns, in the case we're dropping a multicol primary key.)
+ * Then, verify whether any further NOT NULL or primary key
+ * exists, and reset attnotnull if none.
+ */
+ contup = findNotNullConstraintAttnum(con->conrelid, attnum);
+ if (contup ||
+ bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber,
+ pkcols))
+ continue;
+
+ /*
+ * ... but don't do it if the column is in the replica identity or
+ * it's a generated column
+ */
+ if (attForm->attidentity != '\0')
+ continue;
+ ircols = RelationGetIndexAttrBitmap(tablerel, INDEX_ATTR_BITMAP_IDENTITY_KEY); /* XXX bms_free */
+ if (bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber, ircols))
+ continue;
+
+ /* Reset attnotnull */
+ if (attForm->attnotnull)
+ {
+ attForm->attnotnull = false;
+ CatalogTupleUpdate(attrel, &atttup->t_self, atttup);
+ }
+ }
+
+ table_close(attrel, RowExclusiveLock);
+ table_close(tablerel, NoLock);
+ }
+
/* Clean up */
ReleaseSysCache(tup);
table_close(conDesc, RowExclusiveLock);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8a98a0af48..aa9c11760f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -149,6 +149,7 @@ typedef enum AlterTablePass
AT_PASS_ALTER_TYPE, /* ALTER COLUMN TYPE */
AT_PASS_ADD_COL, /* ADD COLUMN */
AT_PASS_SET_EXPRESSION, /* ALTER SET EXPRESSION */
+ AT_PASS_OLD_COL_ATTRS, /* re-install attnotnull */
AT_PASS_OLD_INDEX, /* re-add existing indexes */
AT_PASS_OLD_CONSTR, /* re-add existing constraints */
/* We could support a RENAME COLUMN pass here, but not currently used */
@@ -7678,17 +7679,23 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
}
/*
- * Find the constraint that makes this column NOT NULL.
+ * Find the constraint that makes this column NOT NULL, and drop it if we
+ * see one. dropconstraint_internal() will do necessary consistency
+ * checking. If there isn't one, there are two possibilities: either the
+ * column is marked attnotnull because it's part of the primary key, and
+ * then we just throw an appropriate error; or it's a leftover marking that
+ * we can remove. However, before doing the latter, to avoid breaking
+ * consistency any further, prevent this if the column is part of the
+ * replica identity.
*/
conTup = findNotNullConstraint(RelationGetRelid(rel), colName);
if (conTup == NULL)
{
Bitmapset *pkcols;
+ Bitmapset *ircols;
/*
- * There's no not-null constraint, so throw an error. If the column
- * is in a primary key, we can throw a specific error. Otherwise,
- * this is unexpected.
+ * If the column is in a primary key, throw a specific error message.
*/
pkcols = RelationGetIndexAttrBitmap(rel, INDEX_ATTR_BITMAP_PRIMARY_KEY);
if (bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber,
@@ -7697,16 +7704,27 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("column \"%s\" is in a primary key", colName));
- /* this shouldn't happen */
- elog(ERROR, "could not find not-null constraint on column \"%s\", relation \"%s\"",
- colName, RelationGetRelationName(rel));
+ /* Also throw an error if the column is in the replica identity */
+ ircols = RelationGetIndexAttrBitmap(rel, INDEX_ATTR_BITMAP_IDENTITY_KEY);
+ if (bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber, ircols))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("column \"%s\" is in index used as replica identity",
+ get_attname(RelationGetRelid(rel), attnum, false)));
+
+ /* Otherwise, just remove the attnotnull marking and do nothing else. */
+ attTup->attnotnull = false;
+ CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
}
+ else
+ {
+ /* The normal case: we have a pg_constraint row, remove it */
+ readyRels = NIL;
+ dropconstraint_internal(rel, conTup, DROP_RESTRICT, recurse, false,
+ false, &readyRels, lockmode);
- readyRels = NIL;
- dropconstraint_internal(rel, conTup, DROP_RESTRICT, recurse, false,
- false, &readyRels, lockmode);
-
- heap_freetuple(conTup);
+ heap_freetuple(conTup);
+ }
InvokeObjectPostAlterHook(RelationRelationId,
RelationGetRelid(rel), attnum);
@@ -12933,10 +12951,10 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
List *children;
ListCell *child;
bool is_no_inherit_constraint = false;
- bool dropping_pk = false;
char *constrName;
List *unconstrained_cols = NIL;
- char *colname;
+ char *colname = NULL;
+ bool dropping_pk = false;
if (list_member_oid(*readyRels, RelationGetRelid(rel)))
return InvalidObjectAddress;
@@ -12968,10 +12986,12 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
*/
if (con->contype == CONSTRAINT_NOTNULL)
{
- AttrNumber colnum = extractNotNullColumn(constraintTup);
+ AttrNumber colnum;
- if (colnum != InvalidAttrNumber)
- unconstrained_cols = list_make1_int(colnum);
+ colnum = extractNotNullColumn(constraintTup);
+ unconstrained_cols = list_make1_int(colnum);
+ colname = NameStr(TupleDescAttr(RelationGetDescr(rel),
+ colnum - 1)->attname);
}
else if (con->contype == CONSTRAINT_PRIMARY)
{
@@ -13027,9 +13047,8 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
performDeletion(&conobj, behavior, 0);
/*
- * If this was a NOT NULL or the primary key, the constrained columns must
- * have had pg_attribute.attnotnull set. See if we need to reset it, and
- * do so.
+ * If this was a NOT NULL or the primary key, verify that we still have
+ * constraints to support GENERATED AS IDENTITY or the replica identity.
*/
if (unconstrained_cols)
{
@@ -13038,7 +13057,7 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
Bitmapset *ircols;
ListCell *lc;
- /* Make the above deletion visible */
+ /* Make implicit attnotnull changes visible */
CommandCounterIncrement();
attrel = table_open(AttributeRelationId, RowExclusiveLock);
@@ -13058,27 +13077,26 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
HeapTuple atttup;
HeapTuple contup;
Form_pg_attribute attForm;
+ char attidentity;
/*
- * Obtain pg_attribute tuple and verify conditions on it. We use
- * a copy we can scribble on.
+ * Obtain pg_attribute tuple and verify conditions on it.
*/
- atttup = SearchSysCacheCopyAttNum(RelationGetRelid(rel), attnum);
+ atttup = SearchSysCacheAttNum(RelationGetRelid(rel), attnum);
if (!HeapTupleIsValid(atttup))
elog(ERROR, "cache lookup failed for attribute %d of relation %u",
attnum, RelationGetRelid(rel));
attForm = (Form_pg_attribute) GETSTRUCT(atttup);
+ attidentity = attForm->attidentity;
+ ReleaseSysCache(atttup);
/*
* Since the above deletion has been made visible, we can now
* search for any remaining constraints on this column (or these
* columns, in the case we're dropping a multicol primary key.)
* Then, verify whether any further NOT NULL or primary key
- * exists, and reset attnotnull if none.
- *
- * However, if this is a generated identity column, abort the
- * whole thing with a specific error message, because the
- * constraint is required in that case.
+ * exists: if none and this is a generated identity column or the
+ * replica identity, abort the whole thing.
*/
contup = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
if (contup ||
@@ -13090,7 +13108,7 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
* It's not valid to drop the not-null constraint for a GENERATED
* AS IDENTITY column.
*/
- if (attForm->attidentity)
+ if (attidentity != '\0')
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("column \"%s\" of relation \"%s\" is an identity column",
@@ -13107,13 +13125,6 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("column \"%s\" is in index used as replica identity",
get_attname(RelationGetRelid(rel), lfirst_int(lc), false)));
-
- /* Reset attnotnull */
- if (attForm->attnotnull)
- {
- attForm->attnotnull = false;
- CatalogTupleUpdate(attrel, &atttup->t_self, atttup);
- }
}
table_close(attrel, RowExclusiveLock);
}
@@ -13152,13 +13163,6 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
errmsg("cannot remove constraint from only the partitioned table when partitions exist"),
errhint("Do not specify the ONLY keyword.")));
- /* For not-null constraints we recurse by column name */
- if (con->contype == CONSTRAINT_NOTNULL)
- colname = NameStr(TupleDescAttr(RelationGetDescr(rel),
- linitial_int(unconstrained_cols) - 1)->attname);
- else
- colname = NULL; /* keep compiler quiet */
-
foreach(child, children)
{
Oid childrelid = lfirst_oid(child);
@@ -13174,8 +13178,8 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
CheckTableNotInUse(childrel, "ALTER TABLE");
/*
- * We search for not-null constraint by column number, and other
- * constraints by name.
+ * We search for not-null constraints by column name, and others by
+ * constraint name.
*/
if (con->contype == CONSTRAINT_NOTNULL)
{
@@ -14656,12 +14660,14 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
else if (cmd->subtype == AT_SetAttNotNull)
{
/*
- * The parser will create AT_AttSetNotNull subcommands for
- * columns of PRIMARY KEY indexes/constraints, but we need
- * not do anything with them here, because the columns'
- * NOT NULL marks will already have been propagated into
- * the new table definition.
+ * When doing alter primary key column type, Removing old
+ * PK constraint as sub-command will reset attnotnull to
+ * false in RemoveConstraintById(). We must set attnotnull
+ * back to true before doing re-add the index sub-command.
*/
+ cmd->subtype = AT_SetAttNotNull;
+ tab->subcmds[AT_PASS_OLD_COL_ATTRS] =
+ lappend(tab->subcmds[AT_PASS_OLD_COL_ATTRS], cmd);
}
else
elog(ERROR, "unexpected statement subtype: %d",
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c52e961b30..7dcbfaf8dc 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8788,12 +8788,15 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
"), E',\n ') AS attfdwoptions,\n");
/*
- * Find out any NOT NULL markings for each column. In 17 and up we have
- * to read pg_constraint, and keep track whether it's NO INHERIT; in older
- * versions we rely on pg_attribute.attnotnull.
+ * Find out any NOT NULL markings for each column. In 17 and up we
+ * read pg_constraint to obtain the constraint name. notnull_noinherit
+ * is set according to the NO INHERIT property. For versions prior to 17,
+ * we store an empty string as the name when a constraint is marked as
+ * attnotnull (this cues dumpTableSchema to print the NOT NULL clause
+ * without a name); also, such cases are never NO INHERIT.
*
- * We also track whether the constraint was defined directly in this table
- * or via an ancestor, for binary upgrade.
+ * We track in notnull_inh whether the constraint was defined directly in
+ * this table or via an ancestor, for binary upgrade.
*
* Lastly, we need to know if the PK for the table involves each column;
* for columns that are there we need a NOT NULL marking even if there's
@@ -8801,13 +8804,24 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
* NULLs after the data is loaded when the PK is created, later in the
* dump; for this case we add throwaway constraints that are dropped once
* the PK is created.
+ *
+ * Another complication arises from columns that have attnotnull set, but
+ * for which no corresponding not-null nor PK constraint exists. This can
+ * happen if, for example, a primary key is dropped indirectly -- say,
+ * because one of its columns is dropped. This is an irregular condition,
+ * so we don't work hard to preserve it, and instead act as though an
+ * unnamed not-null constraint exists.
*/
if (fout->remoteVersion >= 170000)
appendPQExpBufferStr(q,
- "co.conname AS notnull_name,\n"
- "co.connoinherit AS notnull_noinherit,\n"
+ "CASE WHEN co.conname IS NOT NULL THEN co.conname "
+ " WHEN a.attnotnull AND copk.conname IS NULL THEN '' ELSE NULL END AS notnull_name,\n"
+ "CASE WHEN co.conname IS NOT NULL THEN co.connoinherit "
+ " WHEN a.attnotnull THEN false ELSE NULL END AS notnull_noinherit,\n"
"copk.conname IS NOT NULL as notnull_is_pk,\n"
- "coalesce(NOT co.conislocal, true) AS notnull_inh,\n");
+ "CASE WHEN co.conname IS NOT NULL THEN "
+ " coalesce(NOT co.conislocal, true) "
+ "ELSE false END as notnull_inh,\n");
else
appendPQExpBufferStr(q,
"CASE WHEN a.attnotnull THEN '' ELSE NULL END AS notnull_name,\n"
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index d9d8408e86..34927f7afc 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -867,6 +867,79 @@ select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl
(1 row)
DROP TABLE notnull_tbl1;
+-- make sure attnotnull is reset correctly when a PK is dropped indirectly,
+-- or kept if there's a reason for that
+CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
+ALTER TABLE notnull_tbl1 DROP c1;
+\d+ notnull_tbl1
+ Table "public.notnull_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ c0 | integer | | | | plain | |
+
+DROP TABLE notnull_tbl1;
+-- same, via dropping a domain
+CREATE DOMAIN notnull_dom1 AS INTEGER;
+CREATE TABLE notnull_tbl1 (c0 notnull_dom1, c1 int, PRIMARY KEY (c0, c1));
+DROP DOMAIN notnull_dom1 CASCADE;
+NOTICE: drop cascades to column c0 of table notnull_tbl1
+\d+ notnull_tbl1
+ Table "public.notnull_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ c1 | integer | | | | plain | |
+
+DROP TABLE notnull_tbl1;
+-- with a REPLICA IDENTITY column. Here the not-nulls must be kept
+CREATE DOMAIN notnull_dom1 AS INTEGER;
+CREATE TABLE notnull_tbl1 (c0 notnull_dom1, c1 int UNIQUE, c2 int generated by default as identity, PRIMARY KEY (c0, c1, c2));
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_c2_not_null;
+ALTER TABLE notnull_tbl1 REPLICA IDENTITY USING INDEX notnull_tbl1_c1_key;
+DROP DOMAIN notnull_dom1 CASCADE;
+NOTICE: drop cascades to column c0 of table notnull_tbl1
+ALTER TABLE notnull_tbl1 ALTER c1 DROP NOT NULL; -- can't be dropped
+ERROR: column "c1" is in index used as replica identity
+ALTER TABLE notnull_tbl1 ALTER c1 SET NOT NULL; -- can be set right
+\d+ notnull_tbl1
+ Table "public.notnull_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+----------------------------------+---------+--------------+-------------
+ c1 | integer | | not null | | plain | |
+ c2 | integer | | not null | generated by default as identity | plain | |
+Indexes:
+ "notnull_tbl1_c1_key" UNIQUE CONSTRAINT, btree (c1) REPLICA IDENTITY
+Not-null constraints:
+ "notnull_tbl1_c1_not_null" NOT NULL "c1"
+
+-- Leave this table around for pg_upgrade testing
+CREATE DOMAIN notnull_dom2 AS INTEGER;
+CREATE TABLE notnull_tbl2 (c0 notnull_dom2, c1 int UNIQUE, c2 int generated by default as identity, PRIMARY KEY (c0, c1, c2));
+ALTER TABLE notnull_tbl2 DROP CONSTRAINT notnull_tbl2_c2_not_null;
+ALTER TABLE notnull_tbl2 REPLICA IDENTITY USING INDEX notnull_tbl2_c1_key;
+DROP DOMAIN notnull_dom2 CASCADE;
+NOTICE: drop cascades to column c0 of table notnull_tbl2
+\d+ notnull_tbl2
+ Table "public.notnull_tbl2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+----------------------------------+---------+--------------+-------------
+ c1 | integer | | not null | | plain | |
+ c2 | integer | | not null | generated by default as identity | plain | |
+Indexes:
+ "notnull_tbl2_c1_key" UNIQUE CONSTRAINT, btree (c1) REPLICA IDENTITY
+
+ALTER TABLE notnull_tbl2 REPLICA IDENTITY FULL, ALTER c2 DROP IDENTITY;
+ALTER TABLE notnull_tbl2 ALTER c1 DROP NOT NULL, ALTER c2 DROP NOT NULL;
+\d+ notnull_tbl2
+ Table "public.notnull_tbl2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ c1 | integer | | | | plain | |
+ c2 | integer | | | | plain | |
+Indexes:
+ "notnull_tbl2_c1_key" UNIQUE CONSTRAINT, btree (c1)
+Replica Identity: FULL
+
+DROP TABLE notnull_tbl2;
-- nope
CREATE TABLE notnull_tbl2 (a INTEGER CONSTRAINT blah NOT NULL, b INTEGER CONSTRAINT blah NOT NULL);
ERROR: constraint "blah" for relation "notnull_tbl2" already exists
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 87d685ae39..ecc7fb8dcf 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -599,6 +599,40 @@ ALTER TABLE notnull_tbl1 ADD CONSTRAINT foobar NOT NULL a;
select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass;
DROP TABLE notnull_tbl1;
+-- make sure attnotnull is reset correctly when a PK is dropped indirectly,
+-- or kept if there's a reason for that
+CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
+ALTER TABLE notnull_tbl1 DROP c1;
+\d+ notnull_tbl1
+DROP TABLE notnull_tbl1;
+-- same, via dropping a domain
+CREATE DOMAIN notnull_dom1 AS INTEGER;
+CREATE TABLE notnull_tbl1 (c0 notnull_dom1, c1 int, PRIMARY KEY (c0, c1));
+DROP DOMAIN notnull_dom1 CASCADE;
+\d+ notnull_tbl1
+DROP TABLE notnull_tbl1;
+-- with a REPLICA IDENTITY column. Here the not-nulls must be kept
+CREATE DOMAIN notnull_dom1 AS INTEGER;
+CREATE TABLE notnull_tbl1 (c0 notnull_dom1, c1 int UNIQUE, c2 int generated by default as identity, PRIMARY KEY (c0, c1, c2));
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_c2_not_null;
+ALTER TABLE notnull_tbl1 REPLICA IDENTITY USING INDEX notnull_tbl1_c1_key;
+DROP DOMAIN notnull_dom1 CASCADE;
+ALTER TABLE notnull_tbl1 ALTER c1 DROP NOT NULL; -- can't be dropped
+ALTER TABLE notnull_tbl1 ALTER c1 SET NOT NULL; -- can be set right
+\d+ notnull_tbl1
+-- Leave this table around for pg_upgrade testing
+
+CREATE DOMAIN notnull_dom2 AS INTEGER;
+CREATE TABLE notnull_tbl2 (c0 notnull_dom2, c1 int UNIQUE, c2 int generated by default as identity, PRIMARY KEY (c0, c1, c2));
+ALTER TABLE notnull_tbl2 DROP CONSTRAINT notnull_tbl2_c2_not_null;
+ALTER TABLE notnull_tbl2 REPLICA IDENTITY USING INDEX notnull_tbl2_c1_key;
+DROP DOMAIN notnull_dom2 CASCADE;
+\d+ notnull_tbl2
+ALTER TABLE notnull_tbl2 REPLICA IDENTITY FULL, ALTER c2 DROP IDENTITY;
+ALTER TABLE notnull_tbl2 ALTER c1 DROP NOT NULL, ALTER c2 DROP NOT NULL;
+\d+ notnull_tbl2
+DROP TABLE notnull_tbl2;
+
-- nope
CREATE TABLE notnull_tbl2 (a INTEGER CONSTRAINT blah NOT NULL, b INTEGER CONSTRAINT blah NOT NULL);
--
2.39.2
