From 3aa60ba61d2edd1edaf77ea6d4ae50a1e50f6039 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Wed, 1 May 2024 19:46:52 +0200 Subject: [PATCH 2/2] Disallow NO INHERIT not-null constraints on partitioned tables They are semantically useless and only bring weird cases. Reject them. Maybe this should be done for all constraints, not just not-null ones. Per note by Alexander Lakhin. --- src/backend/parser/parse_utilcmd.c | 10 ++++++++++ src/bin/pg_dump/pg_dump.c | 16 ++++++++++++++-- src/test/regress/expected/constraints.out | 5 +++++ src/test/regress/sql/constraints.sql | 4 ++++ 4 files changed, 33 insertions(+), 2 deletions(-) diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index fef084f5d5..9fb6ff86db 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -679,6 +679,10 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) break; case CONSTR_NOTNULL: + if (cxt->ispartitioned && constraint->is_no_inherit) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("not-null constraints on partitioned tables cannot be NO INHERIT")); /* * Disallow conflicting [NOT] NULL markings @@ -969,6 +973,12 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) break; case CONSTR_NOTNULL: + if (cxt->ispartitioned && constraint->is_no_inherit) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("not-null constraints on partitioned tables cannot be NO INHERIT")); + + cxt->nnconstraints = lappend(cxt->nnconstraints, constraint); break; diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 242ebe807f..1eb58a447e 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -9052,7 +9052,15 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attnames[j]); } else if (PQgetvalue(res, r, i_notnull_is_pk)[0] == 't') - use_throwaway_notnull = true; + { + /* + * We want this flag to be set for columns of a primary key + * in which data is going to be loaded by the dump we + * produce; thus a partitioned table doesn't need it. + */ + if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE) + use_throwaway_notnull = true; + } else if (!PQgetisnull(res, r, i_notnull_name)) use_unnamed_notnull = true; } @@ -9092,7 +9100,11 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) } } else if (PQgetvalue(res, r, i_notnull_is_pk)[0] == 't') - use_throwaway_notnull = true; + { + /* see above */ + if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE) + use_throwaway_notnull = true; + } } if (use_unnamed_notnull) diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 2fc0be7925..ec7c9e53d0 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -321,6 +321,11 @@ ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT; Inherits: atacc1 DROP TABLE ATACC1, ATACC2; +-- no can do +CREATE TABLE ATACC1 (a int NOT NULL NO INHERIT) PARTITION BY LIST (a); +ERROR: not-null constraints on partitioned tables cannot be NO INHERIT +CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT) PARTITION BY LIST (a); +ERROR: not-null constraints on partitioned tables cannot be NO INHERIT -- overridding a no-inherit constraint with an inheritable one CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); CREATE TABLE ATACC1 (a int); diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 8f85e72050..e753b8c345 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -212,6 +212,10 @@ ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT; \d+ ATACC2 DROP TABLE ATACC1, ATACC2; +-- no can do +CREATE TABLE ATACC1 (a int NOT NULL NO INHERIT) PARTITION BY LIST (a); +CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT) PARTITION BY LIST (a); + -- overridding a no-inherit constraint with an inheritable one CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); CREATE TABLE ATACC1 (a int); -- 2.39.2