From 6f8bb7c1e9610dd7af20cdaf74c4ff6e6d678d44 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 13 May 2024 11:31:09 +0200 Subject: [PATCH] Revert structural changes to not-null constraints There are some problems with the new way to handle these constraints that were detected at the last minute, and require fixes that appear too invasive to be doing this late in the cycle. Revert this (again) for now, we'll try again with these problems fixed. The following commits are reverted: b0e96f311985 Catalog not-null constraints 9b581c534186 Disallow changing NO INHERIT status of a not-null constraint d0ec2ddbe088 Fix not-null constraint test ac22a9545ca9 Move privilege check to the right place b0f7dd915bca Check stack depth in new recursive functions 3af721794272 Update information_schema definition for not-null constraints c3709100be73 Fix propagating attnotnull in multiple inheritance d9f686a72ee9 Fix restore of not-null constraints with inheritance d72d32f52d26 Don't try to assign smart names to constraints 0cd711271d42 Better handle indirect constraint drops 13daa33fa5a6 Disallow NO INHERIT not-null constraints on partitioned tables d45597f72fe5 Disallow direct change of NO INHERIT of not-null constraints 21ac38f498b3 Fix inconsistencies in error messages Discussion: https://postgr.es/m/202405110940.joxlqcx4dogd@alvherre.pgsql --- contrib/sepgsql/expected/alter.out | 3 + contrib/sepgsql/expected/ddl.out | 2 - contrib/test_decoding/expected/ddl.out | 12 - doc/src/sgml/catalogs.sgml | 13 +- doc/src/sgml/ddl.sgml | 55 +- doc/src/sgml/ref/alter_table.sgml | 13 +- doc/src/sgml/ref/create_table.sgml | 8 +- src/backend/catalog/heap.c | 386 +--- src/backend/catalog/information_schema.sql | 71 +- src/backend/catalog/pg_constraint.c | 441 +---- src/backend/commands/tablecmds.c | 1664 +++++------------ src/backend/optimizer/util/plancat.c | 2 - src/backend/parser/gram.y | 19 +- src/backend/parser/parse_utilcmd.c | 288 +-- src/backend/utils/adt/ruleutils.c | 22 - src/backend/utils/cache/relcache.c | 42 +- src/bin/pg_dump/common.c | 19 +- src/bin/pg_dump/pg_dump.c | 322 +--- src/bin/pg_dump/pg_dump.h | 9 +- src/bin/pg_dump/t/002_pg_dump.pl | 10 +- src/bin/psql/describe.c | 44 - src/include/catalog/catversion.h | 2 +- src/include/catalog/heap.h | 8 +- src/include/catalog/pg_constraint.h | 7 - src/include/nodes/parsenodes.h | 11 +- .../test_ddl_deparse/expected/alter_table.out | 18 +- .../expected/create_table.out | 26 +- .../test_ddl_deparse/test_ddl_deparse.c | 6 +- src/test/regress/expected/alter_table.out | 24 +- src/test/regress/expected/cluster.out | 7 +- src/test/regress/expected/constraints.out | 518 ----- src/test/regress/expected/create_table.out | 35 +- .../regress/expected/create_table_like.out | 10 - src/test/regress/expected/event_trigger.out | 2 - src/test/regress/expected/foreign_data.out | 108 +- src/test/regress/expected/foreign_key.out | 16 +- src/test/regress/expected/generated.out | 2 - src/test/regress/expected/identity.out | 4 - src/test/regress/expected/indexing.out | 38 +- src/test/regress/expected/inherit.out | 510 ----- src/test/regress/expected/partition_merge.out | 2 - src/test/regress/expected/publication.out | 6 - .../regress/expected/replica_identity.out | 24 - src/test/regress/expected/rowsecurity.out | 2 - src/test/regress/sql/alter_table.sql | 12 +- src/test/regress/sql/constraints.sql | 217 --- src/test/regress/sql/indexing.sql | 5 +- src/test/regress/sql/inherit.sql | 229 --- src/test/regress/sql/replica_identity.sql | 15 - 49 files changed, 709 insertions(+), 4600 deletions(-) diff --git a/contrib/sepgsql/expected/alter.out b/contrib/sepgsql/expected/alter.out index 1462cfa3cb..ae43537505 100644 --- a/contrib/sepgsql/expected/alter.out +++ b/contrib/sepgsql/expected/alter.out @@ -164,6 +164,7 @@ LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_re LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_2.b" permissive=0 ALTER TABLE regtest_table ALTER b DROP NOT NULL; LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_table.b" permissive=0 +LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_2.b" permissive=0 ALTER TABLE regtest_table ALTER b SET STATISTICS -1; LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_table.b" permissive=0 LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_2.b" permissive=0 @@ -248,6 +249,8 @@ LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_re LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_ptable_1_tens.p" permissive=0 ALTER TABLE regtest_ptable ALTER p DROP NOT NULL; LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_ptable.p" permissive=0 +LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_table_part.p" permissive=0 +LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_ptable_1_tens.p" permissive=0 ALTER TABLE regtest_ptable ALTER p SET STATISTICS -1; LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_ptable.p" permissive=0 LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_table_part.p" permissive=0 diff --git a/contrib/sepgsql/expected/ddl.out b/contrib/sepgsql/expected/ddl.out index 7fbaab84d5..93c677e546 100644 --- a/contrib/sepgsql/expected/ddl.out +++ b/contrib/sepgsql/expected/ddl.out @@ -49,7 +49,6 @@ LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_reg LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0 LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=system_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="pg_catalog" permissive=0 LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0 -LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0 LOG: SELinux: allowed { add_name } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0 LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_table name="regtest_schema.regtest_table" permissive=0 LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0 @@ -285,7 +284,6 @@ LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:sepgsql_reg LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_4.y" permissive=0 LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_4.z" permissive=0 LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0 -LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0 LOG: SELinux: allowed { add_name } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0 LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_table name="regtest_schema.regtest_table_4" permissive=0 CREATE INDEX regtest_index_tbl4_y ON regtest_table_4(y); diff --git a/contrib/test_decoding/expected/ddl.out b/contrib/test_decoding/expected/ddl.out index bcd1f74b2b..5713b8ab1c 100644 --- a/contrib/test_decoding/expected/ddl.out +++ b/contrib/test_decoding/expected/ddl.out @@ -492,9 +492,6 @@ WITH (user_catalog_table = true) options | text[] | | | | extended | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not-null constraints: - "replication_metadata_id_not_null" NOT NULL "id" - "replication_metadata_relation_not_null" NOT NULL "relation" Options: user_catalog_table=true INSERT INTO replication_metadata(relation, options) @@ -509,9 +506,6 @@ ALTER TABLE replication_metadata RESET (user_catalog_table); options | text[] | | | | extended | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not-null constraints: - "replication_metadata_id_not_null" NOT NULL "id" - "replication_metadata_relation_not_null" NOT NULL "relation" INSERT INTO replication_metadata(relation, options) VALUES ('bar', ARRAY['a', 'b']); @@ -525,9 +519,6 @@ ALTER TABLE replication_metadata SET (user_catalog_table = true); options | text[] | | | | extended | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not-null constraints: - "replication_metadata_id_not_null" NOT NULL "id" - "replication_metadata_relation_not_null" NOT NULL "relation" Options: user_catalog_table=true INSERT INTO replication_metadata(relation, options) @@ -547,9 +538,6 @@ ALTER TABLE replication_metadata SET (user_catalog_table = false); rewritemeornot | integer | | | | plain | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not-null constraints: - "replication_metadata_id_not_null" NOT NULL "id" - "replication_metadata_relation_not_null" NOT NULL "relation" Options: user_catalog_table=false INSERT INTO replication_metadata(relation, options) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index b530c030f0..56deb4ac5f 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1270,8 +1270,7 @@ attnotnull bool - This column is marked not-null, either by a not-null constraint - or a primary key. + This represents a not-null constraint. @@ -2502,10 +2501,14 @@ SCRAM-SHA-256$<iteration count>:&l - The catalog pg_constraint stores check, not-null, - primary key, unique, foreign key, and exclusion constraints on tables. + The catalog pg_constraint stores check, primary + key, unique, foreign key, and exclusion constraints on tables, as well as + not-null constraints on domains. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) + Not-null constraints on relations are represented in the + pg_attribute + catalog, not here. @@ -2567,7 +2570,7 @@ SCRAM-SHA-256$<iteration count>:&l c = check constraint, f = foreign key constraint, - n = not-null constraint, + f = not-null constraint (domains only), p = primary key constraint, u = unique constraint, t = constraint trigger, diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 026bfff70f..6aab79e901 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -763,38 +763,17 @@ CREATE TABLE products ( price numeric ); - An explicit constraint name can also be specified, for example: - -CREATE TABLE products ( - product_no integer NOT NULL, - name text CONSTRAINT products_name_not_null NOT NULL, - price numeric -); - - - - - A not-null constraint is usually written as a column constraint. The - syntax for writing it as a table constraint is - -CREATE TABLE products ( - product_no integer, - name text, - price numeric, - NOT NULL product_no, - NOT NULL name -); - - But this syntax is not standard and mainly intended for use by - pg_dump. - A not-null constraint is functionally equivalent to creating a check + A not-null constraint is always written as a column constraint. A + not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit - not-null constraint is more efficient. + not-null constraint is more efficient. The drawback is that you + cannot give explicit names to not-null constraints created this + way. @@ -811,10 +790,6 @@ CREATE TABLE products ( order the constraints are checked. - - However, a column can have at most one explicit not-null constraint. - - The NOT NULL constraint has an inverse: the NULL constraint. This does not mean that the @@ -1008,7 +983,7 @@ CREATE TABLE example ( A table can have at most one primary key. (There can be any number - of unique constraints, which combined with not-null constraints are functionally almost the + of unique and not-null constraints, which are functionally almost the same thing, but only one can be identified as the primary key.) Relational database theory dictates that every table must have a primary key. This rule is @@ -1668,16 +1643,11 @@ ALTER TABLE products ADD CHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; - - - - To add a not-null constraint, which is normally not written as a table - constraint, this special syntax is available: + To add a not-null constraint, which cannot be written as a table + constraint, use this syntax: ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; - This command silently does nothing if the column already has a - not-null constraint. @@ -1718,15 +1688,12 @@ ALTER TABLE products DROP CONSTRAINT some_name; - Simplified syntax is available to drop a not-null constraint: + This works the same for all constraint types except not-null + constraints. To drop a not-null constraint use: ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; - This mirrors the SET NOT NULL syntax for adding a - not-null constraint. This command will silently do nothing if the column - does not have a not-null constraint. (Recall that a column can have at - most one not-null constraint, so it is never ambiguous which constraint - this command acts on.) + (Recall that not-null constraints do not have names.) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 0bf11f6cb6..5d352abf99 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -105,7 +105,7 @@ WITH ( MODULUS numeric_literal, REM and column_constraint is: [ CONSTRAINT constraint_name ] -{ NOT NULL [ NO INHERIT ] | +{ NOT NULL | NULL | CHECK ( expression ) [ NO INHERIT ] | DEFAULT default_expr | @@ -121,7 +121,6 @@ WITH ( MODULUS numeric_literal, REM [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | - NOT NULL column_name [ NO INHERIT ] | UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | @@ -1942,17 +1941,11 @@ ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) Compatibility - The forms ADD [COLUMN], + The forms ADD (without USING INDEX), DROP [COLUMN], DROP IDENTITY, RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and SET sequence_option - conform with the SQL standard. - The form ADD table_constraint - conforms with the SQL standard when the USING INDEX and - NOT VALID clauses are omitted and the constraint type is - one of CHECK, UNIQUE, PRIMARY KEY, - or REFERENCES. - The other forms are + conform with the SQL standard. The other forms are PostgreSQL extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER TABLE command is an extension. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 75f06bc49c..a5bf80fb27 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -77,7 +77,6 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | - NOT NULL column_name [ NO INHERIT ] | UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | @@ -2392,6 +2391,13 @@ CREATE TABLE cities_partdef constraint, and index names must be unique across all relations within the same schema. + + + Currently, PostgreSQL does not record names + for not-null constraints at all, so they are not + subject to the uniqueness restriction. This might change in a future + release. + diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 922ba79ac2..08b8362d64 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2163,54 +2163,6 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, return constrOid; } -/* - * Store a not-null constraint for the given relation - * - * The OID of the new constraint is returned. - */ -static Oid -StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum, - bool is_validated, bool is_local, int inhcount, - bool is_no_inherit) -{ - Oid constrOid; - - constrOid = - CreateConstraintEntry(nnname, - RelationGetNamespace(rel), - CONSTRAINT_NOTNULL, - false, - false, - is_validated, - InvalidOid, - RelationGetRelid(rel), - &attnum, - 1, - 1, - InvalidOid, /* not a domain constraint */ - InvalidOid, /* no associated index */ - InvalidOid, /* Foreign key fields */ - NULL, - NULL, - NULL, - NULL, - 0, - ' ', - ' ', - NULL, - 0, - ' ', - NULL, /* not an exclusion constraint */ - NULL, - NULL, - is_local, - inhcount, - is_no_inherit, - false, /* conperiod */ - false); - return constrOid; -} - /* * Store defaults and constraints (passed as a list of CookedConstraint). * @@ -2255,14 +2207,6 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal) is_internal); numchecks++; break; - - case CONSTR_NOTNULL: - con->conoid = - StoreRelNotNull(rel, con->name, con->attnum, - !con->skip_validation, con->is_local, - con->inhcount, con->is_no_inherit); - break; - default: elog(ERROR, "unrecognized constraint type: %d", (int) con->contype); @@ -2320,8 +2264,6 @@ AddRelationNewConstraints(Relation rel, ParseNamespaceItem *nsitem; int numchecks; List *checknames; - List *nnnames; - ListCell *cell; Node *expr; CookedConstraint *cooked; @@ -2352,9 +2294,8 @@ AddRelationNewConstraints(Relation rel, /* * Process column default expressions. */ - foreach(cell, newColDefaults) + foreach_ptr(RawColumnDefault, colDef, newColDefaults) { - RawColumnDefault *colDef = (RawColumnDefault *) lfirst(cell); Form_pg_attribute atp = TupleDescAttr(rel->rd_att, colDef->attnum - 1); Oid defOid; @@ -2407,10 +2348,8 @@ AddRelationNewConstraints(Relation rel, */ numchecks = numoldchecks; checknames = NIL; - nnnames = NIL; - foreach(cell, newConstraints) + foreach_node(Constraint, cdef, newConstraints) { - Constraint *cdef = (Constraint *) lfirst(cell); Oid constrOid; if (cdef->contype == CONSTR_CHECK) @@ -2444,14 +2383,12 @@ AddRelationNewConstraints(Relation rel, */ if (cdef->conname != NULL) { - ListCell *cell2; - ccname = cdef->conname; /* Check against other new constraints */ /* Needed because we don't do CommandCounterIncrement in loop */ - foreach(cell2, checknames) + foreach_ptr(char, chkname, checknames) { - if (strcmp((char *) lfirst(cell2), ccname) == 0) + if (strcmp(chkname, ccname) == 0) ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("check constraint \"%s\" already exists", @@ -2465,7 +2402,7 @@ AddRelationNewConstraints(Relation rel, * Check against pre-existing constraints. If we are allowed * to merge with an existing constraint, there's no more to do * here. (We omit the duplicate constraint from the result, - * which is what ATAddCheckNNConstraint wants.) + * which is what ATAddCheckConstraint wants.) */ if (MergeWithExistingConstraint(rel, ccname, expr, allow_merge, is_local, @@ -2534,107 +2471,6 @@ AddRelationNewConstraints(Relation rel, cooked->is_no_inherit = cdef->is_no_inherit; cookedConstraints = lappend(cookedConstraints, cooked); } - else if (cdef->contype == CONSTR_NOTNULL) - { - CookedConstraint *nncooked; - AttrNumber colnum; - char *nnname; - int existing; - - /* Determine which column to modify */ - colnum = get_attnum(RelationGetRelid(rel), strVal(linitial(cdef->keys))); - if (colnum == InvalidAttrNumber) /* shouldn't happen */ - elog(ERROR, "cache lookup failed for attribute \"%s\" of relation %u", - strVal(linitial(cdef->keys)), RelationGetRelid(rel)); - - /* - * If the column already has an inheritable not-null constraint, - * we need only adjust its coninhcount and we're done. In certain - * cases (see below), if the constraint is there but marked NO - * INHERIT, then we mark it as no longer such and coninhcount - * updated, plus we must also recurse to the children (if any) to - * add the constraint there. - * - * We only allow the inheritability status to change during binary - * upgrade (where it's used to add the not-null constraints for - * children of tables with primary keys), or when we're recursing - * processing a table down an inheritance hierarchy; directly - * allowing a constraint to change from NO INHERIT to INHERIT - * during ALTER TABLE ADD CONSTRAINT would be far too surprising - * behavior. - */ - existing = AdjustNotNullInheritance1(RelationGetRelid(rel), colnum, - cdef->inhcount, cdef->is_no_inherit, - IsBinaryUpgrade || allow_merge); - if (existing == 1) - continue; /* all done! */ - else if (existing == -1) - { - List *children; - - children = find_inheritance_children(RelationGetRelid(rel), NoLock); - foreach_oid(childoid, children) - { - Relation childrel = table_open(childoid, NoLock); - - AddRelationNewConstraints(childrel, - NIL, - list_make1(copyObject(cdef)), - allow_merge, - is_local, - is_internal, - queryString); - /* these constraints are not in the return list -- good? */ - - table_close(childrel, NoLock); - } - - continue; - } - - /* - * If a constraint name is specified, check that it isn't already - * used. Otherwise, choose a non-conflicting one ourselves. - */ - if (cdef->conname) - { - if (ConstraintNameIsUsed(CONSTRAINT_RELATION, - RelationGetRelid(rel), - cdef->conname)) - ereport(ERROR, - errcode(ERRCODE_DUPLICATE_OBJECT), - errmsg("constraint \"%s\" for relation \"%s\" already exists", - cdef->conname, RelationGetRelationName(rel))); - nnname = cdef->conname; - } - else - nnname = ChooseConstraintName(RelationGetRelationName(rel), - strVal(linitial(cdef->keys)), - "not_null", - RelationGetNamespace(rel), - nnnames); - nnnames = lappend(nnnames, nnname); - - constrOid = - StoreRelNotNull(rel, nnname, colnum, - cdef->initially_valid, - cdef->inhcount == 0, - cdef->inhcount, - cdef->is_no_inherit); - - nncooked = (CookedConstraint *) palloc(sizeof(CookedConstraint)); - nncooked->contype = CONSTR_NOTNULL; - nncooked->conoid = constrOid; - nncooked->name = nnname; - nncooked->attnum = colnum; - nncooked->expr = NULL; - nncooked->skip_validation = cdef->skip_validation; - nncooked->is_local = is_local; - nncooked->inhcount = cdef->inhcount; - nncooked->is_no_inherit = cdef->is_no_inherit; - - cookedConstraints = lappend(cookedConstraints, nncooked); - } } /* @@ -2804,218 +2640,6 @@ MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, return found; } -/* list_sort comparator to sort CookedConstraint by attnum */ -static int -list_cookedconstr_attnum_cmp(const ListCell *p1, const ListCell *p2) -{ - AttrNumber v1 = ((CookedConstraint *) lfirst(p1))->attnum; - AttrNumber v2 = ((CookedConstraint *) lfirst(p2))->attnum; - - return pg_cmp_s16(v1, v2); -} - -/* - * Create the not-null constraints when creating a new relation - * - * These come from two sources: the 'constraints' list (of Constraint) is - * specified directly by the user; the 'old_notnulls' list (of - * CookedConstraint) comes from inheritance. We create one constraint - * for each column, giving priority to user-specified ones, and setting - * inhcount according to how many parents cause each column to get a - * not-null constraint. If a user-specified name clashes with another - * user-specified name, an error is raised. - * - * Note that inherited constraints have two shapes: those coming from another - * not-null constraint in the parent, which have a name already, and those - * coming from a primary key in the parent, which don't. Any name specified - * in a parent is disregarded in case of a conflict. - * - * Returns a list of AttrNumber for columns that need to have the attnotnull - * flag set. - */ -List * -AddRelationNotNullConstraints(Relation rel, List *constraints, - List *old_notnulls) -{ - List *givennames; - List *nnnames; - List *nncols = NIL; - ListCell *lc; - - /* - * We track two lists of names: nnnames keeps all the constraint names, - * givennames tracks user-generated names. The distinction is important, - * because we must raise error for user-generated name conflicts, but for - * system-generated name conflicts we just generate another. - */ - nnnames = NIL; - givennames = NIL; - - /* - * First, create all not-null constraints that are directly specified by - * the user. Note that inheritance might have given us another source for - * each, so we must scan the old_notnulls list and increment inhcount for - * each element with identical attnum. We delete from there any element - * that we process. - */ - foreach(lc, constraints) - { - Constraint *constr = lfirst_node(Constraint, lc); - AttrNumber attnum; - char *conname; - bool is_local = true; - int inhcount = 0; - ListCell *lc2; - - Assert(constr->contype == CONSTR_NOTNULL); - - attnum = get_attnum(RelationGetRelid(rel), - strVal(linitial(constr->keys))); - - /* - * Search in the list of inherited constraints for any entries on the - * same column. - */ - foreach(lc2, old_notnulls) - { - CookedConstraint *old = (CookedConstraint *) lfirst(lc2); - - if (old->attnum == attnum) - { - /* - * If we get a constraint from the parent, having a local NO - * INHERIT one doesn't work. - */ - if (constr->is_no_inherit) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("cannot define not-null constraint on column \"%s\" with NO INHERIT", - strVal(linitial(constr->keys))), - errdetail("The column has an inherited not-null constraint."))); - - inhcount++; - old_notnulls = foreach_delete_current(old_notnulls, lc2); - } - } - - /* - * Determine a constraint name, which may have been specified by the - * user, or raise an error if a conflict exists with another - * user-specified name. - */ - if (constr->conname) - { - foreach(lc2, givennames) - { - if (strcmp(lfirst(lc2), constr->conname) == 0) - ereport(ERROR, - errcode(ERRCODE_DUPLICATE_OBJECT), - errmsg("constraint \"%s\" for relation \"%s\" already exists", - constr->conname, - RelationGetRelationName(rel))); - } - - conname = constr->conname; - givennames = lappend(givennames, conname); - } - else - conname = ChooseConstraintName(RelationGetRelationName(rel), - get_attname(RelationGetRelid(rel), - attnum, false), - "not_null", - RelationGetNamespace(rel), - nnnames); - nnnames = lappend(nnnames, conname); - - StoreRelNotNull(rel, conname, - attnum, true, is_local, - inhcount, constr->is_no_inherit); - - nncols = lappend_int(nncols, attnum); - } - - /* - * If any column remains in the old_notnulls list, we must create a not- - * null constraint marked not-local. Because multiple parents could - * specify a not-null constraint for the same column, we must count how - * many there are and add to the original inhcount accordingly, deleting - * elements we've already processed. We sort the list to make it easy. - * - * We don't use foreach() here because we have two nested loops over the - * constraint list, with possible element deletions in the inner one. If - * we used foreach_delete_current() it could only fix up the state of one - * of the loops, so it seems cleaner to use looping over list indexes for - * both loops. Note that any deletion will happen beyond where the outer - * loop is, so its index never needs adjustment. - */ - list_sort(old_notnulls, list_cookedconstr_attnum_cmp); - for (int outerpos = 0; outerpos < list_length(old_notnulls); outerpos++) - { - CookedConstraint *cooked; - char *conname = NULL; - int add_inhcount = 0; - ListCell *lc2; - - cooked = (CookedConstraint *) list_nth(old_notnulls, outerpos); - Assert(cooked->contype == CONSTR_NOTNULL); - - /* - * Preserve the first non-conflicting constraint name we come across, - * if any - */ - if (conname == NULL && cooked->name) - conname = cooked->name; - - for (int restpos = outerpos + 1; restpos < list_length(old_notnulls);) - { - CookedConstraint *other; - - other = (CookedConstraint *) list_nth(old_notnulls, restpos); - if (other->attnum == cooked->attnum) - { - if (conname == NULL && other->name) - conname = other->name; - - add_inhcount++; - old_notnulls = list_delete_nth_cell(old_notnulls, restpos); - } - else - restpos++; - } - - /* If we got a name, make sure it isn't one we've already used */ - if (conname != NULL) - { - foreach(lc2, nnnames) - { - if (strcmp(lfirst(lc2), conname) == 0) - { - conname = NULL; - break; - } - } - } - - /* and choose a name, if needed */ - if (conname == NULL) - conname = ChooseConstraintName(RelationGetRelationName(rel), - get_attname(RelationGetRelid(rel), - cooked->attnum, false), - "not_null", - RelationGetNamespace(rel), - nnnames); - nnnames = lappend(nnnames, conname); - - StoreRelNotNull(rel, conname, cooked->attnum, true, - cooked->is_local, cooked->inhcount + add_inhcount, - cooked->is_no_inherit); - - nncols = lappend_int(nncols, cooked->attnum); - } - - return nncols; -} - /* * Update the count of constraints in the relation's pg_class tuple. * diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 76c78c0d18..c4145131ce 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -440,8 +440,9 @@ CREATE VIEW check_constraints AS WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE') AND con.contype = 'c' - UNION ALL - -- not-null constraints + UNION + -- not-null constraints on domains + SELECT current_database()::information_schema.sql_identifier AS constraint_catalog, rs.nspname::information_schema.sql_identifier AS constraint_schema, con.conname::information_schema.sql_identifier AS constraint_name, @@ -452,7 +453,24 @@ CREATE VIEW check_constraints AS LEFT JOIN pg_type t ON t.oid = con.contypid LEFT JOIN pg_attribute at ON (con.conrelid = at.attrelid AND con.conkey[1] = at.attnum) WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE'::text) - AND con.contype = 'n'; + AND con.contype = 'n' + + UNION + -- not-null constraints on relations + + SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, + CAST(n.nspname AS sql_identifier) AS constraint_schema, + CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX + CAST(a.attname || ' IS NOT NULL' AS character_data) + AS check_clause + FROM pg_namespace n, pg_class r, pg_attribute a + WHERE n.oid = r.relnamespace + AND r.oid = a.attrelid + AND a.attnum > 0 + AND NOT a.attisdropped + AND a.attnotnull + AND r.relkind IN ('r', 'p') + AND pg_has_role(r.relowner, 'USAGE'); GRANT SELECT ON check_constraints TO PUBLIC; @@ -821,20 +839,6 @@ CREATE VIEW constraint_column_usage AS UNION ALL - /* not-null constraints */ - SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname - FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c - WHERE nr.oid = r.relnamespace - AND r.oid = a.attrelid - AND r.oid = c.conrelid - AND a.attnum = c.conkey[1] - AND c.connamespace = nc.oid - AND c.contype = 'n' - AND r.relkind in ('r', 'p') - AND not a.attisdropped - - UNION ALL - /* unique/primary key/foreign key constraints */ SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, @@ -1835,7 +1839,6 @@ CREATE VIEW table_constraints AS CAST(r.relname AS sql_identifier) AS table_name, CAST( CASE c.contype WHEN 'c' THEN 'CHECK' - WHEN 'n' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END @@ -1860,6 +1863,38 @@ CREATE VIEW table_constraints AS AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints AND r.relkind IN ('r', 'p') AND (NOT pg_is_other_temp_schema(nr.oid)) + AND (pg_has_role(r.relowner, 'USAGE') + -- SELECT privilege omitted, per SQL standard + OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') ) + + UNION ALL + + -- not-null constraints + + SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, + CAST(nr.nspname AS sql_identifier) AS constraint_schema, + CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX + CAST(current_database() AS sql_identifier) AS table_catalog, + CAST(nr.nspname AS sql_identifier) AS table_schema, + CAST(r.relname AS sql_identifier) AS table_name, + CAST('CHECK' AS character_data) AS constraint_type, + CAST('NO' AS yes_or_no) AS is_deferrable, + CAST('NO' AS yes_or_no) AS initially_deferred, + CAST('YES' AS yes_or_no) AS enforced, + CAST(NULL AS yes_or_no) AS nulls_distinct + + FROM pg_namespace nr, + pg_class r, + pg_attribute a + + WHERE nr.oid = r.relnamespace + AND r.oid = a.attrelid + AND a.attnotnull + AND a.attnum > 0 + AND NOT a.attisdropped + AND r.relkind IN ('r', 'p') + AND (NOT pg_is_other_temp_schema(nr.oid)) AND (pg_has_role(r.relowner, 'USAGE') -- SELECT privilege omitted, per SQL standard OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 12a73d5a30..b10e458b44 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -19,10 +19,8 @@ #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" #include "catalog/indexing.h" #include "catalog/objectaccess.h" #include "catalog/pg_constraint.h" @@ -565,72 +563,6 @@ ChooseConstraintName(const char *name1, const char *name2, return conname; } -/* - * Find and return a copy of the pg_constraint tuple that implements a - * validated not-null constraint for the given column of the given relation. - * - * XXX This would be easier if we had pg_attribute.notnullconstr with the OID - * of the constraint that implements the not-null constraint for that column. - * I'm not sure it's worth the catalog bloat and de-normalization, however. - */ -HeapTuple -findNotNullConstraintAttnum(Oid relid, AttrNumber attnum) -{ - Relation pg_constraint; - HeapTuple conTup, - retval = NULL; - SysScanDesc scan; - ScanKeyData key; - - pg_constraint = table_open(ConstraintRelationId, AccessShareLock); - ScanKeyInit(&key, - Anum_pg_constraint_conrelid, - BTEqualStrategyNumber, F_OIDEQ, - ObjectIdGetDatum(relid)); - scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, - true, NULL, 1, &key); - - while (HeapTupleIsValid(conTup = systable_getnext(scan))) - { - Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup); - AttrNumber conkey; - - /* - * We're looking for a NOTNULL constraint that's marked validated, - * with the column we're looking for as the sole element in conkey. - */ - if (con->contype != CONSTRAINT_NOTNULL) - continue; - if (!con->convalidated) - continue; - - conkey = extractNotNullColumn(conTup); - if (conkey != attnum) - continue; - - /* Found it */ - retval = heap_copytuple(conTup); - break; - } - - systable_endscan(scan); - table_close(pg_constraint, AccessShareLock); - - return retval; -} - -/* - * Find and return the pg_constraint tuple that implements a validated - * not-null constraint for the given column of the given relation. - */ -HeapTuple -findNotNullConstraint(Oid relid, const char *colname) -{ - AttrNumber attnum = get_attnum(relid, colname); - - return findNotNullConstraintAttnum(relid, attnum); -} - /* * Find and return the pg_constraint tuple that implements a validated * not-null constraint for the given domain. @@ -675,263 +607,6 @@ findDomainNotNullConstraint(Oid typid) return retval; } -/* - * Given a pg_constraint tuple for a not-null constraint, return the column - * number it is for. - */ -AttrNumber -extractNotNullColumn(HeapTuple constrTup) -{ - AttrNumber colnum; - Datum adatum; - ArrayType *arr; - - /* only tuples for not-null constraints should be given */ - Assert(((Form_pg_constraint) GETSTRUCT(constrTup))->contype == CONSTRAINT_NOTNULL); - - adatum = SysCacheGetAttrNotNull(CONSTROID, constrTup, - Anum_pg_constraint_conkey); - arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */ - if (ARR_NDIM(arr) != 1 || - ARR_HASNULL(arr) || - ARR_ELEMTYPE(arr) != INT2OID || - ARR_DIMS(arr)[0] != 1) - elog(ERROR, "conkey is not a 1-D smallint array"); - - memcpy(&colnum, ARR_DATA_PTR(arr), sizeof(AttrNumber)); - - if ((Pointer) arr != DatumGetPointer(adatum)) - pfree(arr); /* free de-toasted copy, if any */ - - return colnum; -} - -/* - * AdjustNotNullInheritance1 - * Adjust inheritance count for a single not-null constraint - * - * If no not-null constraint is found for the column, return 0. - * Caller can create one. - * If the constraint does exist and it's inheritable, adjust its - * inheritance count (and possibly islocal status) and return 1. - * No further action needs to be taken. - * If the constraint exists but is marked NO INHERIT, adjust it as above - * and reset connoinherit to false, and return -1. Caller is - * responsible for adding the same constraint to the children, if any. - */ -int -AdjustNotNullInheritance1(Oid relid, AttrNumber attnum, int count, - bool is_no_inherit, bool allow_noinherit_change) -{ - HeapTuple tup; - - Assert(count >= 0); - - tup = findNotNullConstraintAttnum(relid, attnum); - if (HeapTupleIsValid(tup)) - { - Relation pg_constraint; - Form_pg_constraint conform; - int retval = 1; - - pg_constraint = table_open(ConstraintRelationId, RowExclusiveLock); - conform = (Form_pg_constraint) GETSTRUCT(tup); - - /* - * If we're asked for a NO INHERIT constraint and this relation - * already has an inheritable one, throw an error. - */ - if (is_no_inherit && !conform->connoinherit) - ereport(ERROR, - errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), - errmsg("cannot change NO INHERIT status of NOT NULL constraint \"%s\" on relation \"%s\"", - NameStr(conform->conname), get_rel_name(relid))); - - /* - * If the constraint already exists in this relation but it's marked - * NO INHERIT, we can just remove that flag (provided caller allows - * such a change), and instruct caller to recurse to add the - * constraint to children. - */ - if (!is_no_inherit && conform->connoinherit) - { - if (!allow_noinherit_change) - ereport(ERROR, - errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), - errmsg("cannot change NO INHERIT status of NOT NULL constraint \"%s\" on relation \"%s\"", - NameStr(conform->conname), get_rel_name(relid))); - - conform->connoinherit = false; - retval = -1; /* caller must add constraint on child rels */ - } - - if (count > 0) - conform->coninhcount += count; - - /* sanity check */ - if (conform->coninhcount < 0) - elog(ERROR, "invalid inhcount %d for constraint \"%s\" on relation \"%s\"", - conform->coninhcount, NameStr(conform->conname), - get_rel_name(relid)); - - /* - * If the constraint is no longer inherited, mark it local. It's - * arguable that we should drop it instead, but it's hard to see that - * being better. The user can drop it manually later. - */ - if (conform->coninhcount == 0) - conform->conislocal = true; - - CatalogTupleUpdate(pg_constraint, &tup->t_self, tup); - - table_close(pg_constraint, RowExclusiveLock); - - return retval; - } - - return 0; -} - -/* - * AdjustNotNullInheritance - * Adjust not-null constraints' inhcount/islocal for - * ALTER TABLE [NO] INHERITS - * - * Mark the NOT NULL constraints for the given relation columns as - * inherited, so that they can't be dropped. - * - * Caller must have checked beforehand that attnotnull was set for all - * columns. However, some of those could be set because of a primary - * key, so throw a proper user-visible error if one is not found. - */ -void -AdjustNotNullInheritance(Oid relid, Bitmapset *columns, int count) -{ - Relation pg_constraint; - int attnum; - - pg_constraint = table_open(ConstraintRelationId, RowExclusiveLock); - - /* - * Scan the set of columns and bump inhcount for each. - */ - attnum = -1; - while ((attnum = bms_next_member(columns, attnum)) >= 0) - { - HeapTuple tup; - Form_pg_constraint conform; - - tup = findNotNullConstraintAttnum(relid, attnum); - if (!HeapTupleIsValid(tup)) - ereport(ERROR, - errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("column \"%s\" in child table must be marked NOT NULL", - get_attname(relid, attnum, - false))); - - conform = (Form_pg_constraint) GETSTRUCT(tup); - conform->coninhcount += count; - if (conform->coninhcount < 0) - elog(ERROR, "invalid inhcount %d for constraint \"%s\" on relation \"%s\"", - conform->coninhcount, NameStr(conform->conname), - get_rel_name(relid)); - - /* - * If the constraints are no longer inherited, mark them local. It's - * arguable that we should drop them instead, but it's hard to see - * that being better. The user can drop it manually later. - */ - if (conform->coninhcount == 0) - conform->conislocal = true; - - CatalogTupleUpdate(pg_constraint, &tup->t_self, tup); - } - - table_close(pg_constraint, RowExclusiveLock); -} - -/* - * RelationGetNotNullConstraints - * Return the list of not-null constraints for the given rel - * - * Caller can request cooked constraints, or raw. - * - * This is seldom needed, so we just scan pg_constraint each time. - * - * XXX This is only used to create derived tables, so NO INHERIT constraints - * are always skipped. - */ -List * -RelationGetNotNullConstraints(Oid relid, bool cooked) -{ - List *notnulls = NIL; - Relation constrRel; - HeapTuple htup; - SysScanDesc conscan; - ScanKeyData skey; - - constrRel = table_open(ConstraintRelationId, AccessShareLock); - ScanKeyInit(&skey, - Anum_pg_constraint_conrelid, - BTEqualStrategyNumber, F_OIDEQ, - ObjectIdGetDatum(relid)); - conscan = systable_beginscan(constrRel, ConstraintRelidTypidNameIndexId, true, - NULL, 1, &skey); - - while (HeapTupleIsValid(htup = systable_getnext(conscan))) - { - Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(htup); - AttrNumber colnum; - - if (conForm->contype != CONSTRAINT_NOTNULL) - continue; - if (conForm->connoinherit) - continue; - - colnum = extractNotNullColumn(htup); - - if (cooked) - { - CookedConstraint *cooked; - - cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint)); - - cooked->contype = CONSTR_NOTNULL; - cooked->name = pstrdup(NameStr(conForm->conname)); - cooked->attnum = colnum; - cooked->expr = NULL; - cooked->skip_validation = false; - cooked->is_local = true; - cooked->inhcount = 0; - cooked->is_no_inherit = conForm->connoinherit; - - notnulls = lappend(notnulls, cooked); - } - else - { - Constraint *constr; - - constr = makeNode(Constraint); - constr->contype = CONSTR_NOTNULL; - constr->conname = pstrdup(NameStr(conForm->conname)); - constr->deferrable = false; - constr->initdeferred = false; - constr->location = -1; - constr->keys = list_make1(makeString(get_attname(relid, colnum, - false))); - constr->skip_validation = false; - constr->initially_valid = true; - notnulls = lappend(notnulls, constr); - } - } - - systable_endscan(conscan); - table_close(constrRel, AccessShareLock); - - return notnulls; -} - - /* * Delete a single constraint record. */ @@ -941,8 +616,6 @@ RemoveConstraintById(Oid conId) Relation conDesc; HeapTuple tup; Form_pg_constraint con; - bool dropping_pk = false; - List *unconstrained_cols = NIL; conDesc = table_open(ConstraintRelationId, RowExclusiveLock); @@ -967,9 +640,7 @@ 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. For not-null and primary key constraints, - * obtain the list of columns affected, so that we can reset their - * attnotnull flags below. + * entries when we commit. */ if (con->contype == CONSTRAINT_CHECK) { @@ -996,36 +667,6 @@ 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); @@ -1045,86 +686,6 @@ 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 != NIL) - { - 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 setting this column as - * not-nullable; if we find any, no need to reset attnotnull. - */ - if (bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber, - pkcols)) - continue; - contup = findNotNullConstraintAttnum(con->conrelid, attnum); - if (contup) - continue; - - /* - * Also no reset 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); - 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 de0d911b46..79c9c03183 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -151,7 +151,6 @@ 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 */ @@ -362,8 +361,7 @@ static void truncate_check_activity(Relation rel); static void RangeVarCallbackForTruncate(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); static List *MergeAttributes(List *columns, const List *supers, char relpersistence, - bool is_partition, List **supconstr, - List **supnotnulls); + bool is_partition, List **supconstr); static List *MergeCheckConstraint(List *constraints, const char *name, Node *expr); static void MergeChildAttribute(List *inh_columns, int exist_attno, int newcol_attno, const ColumnDef *newdef); static ColumnDef *MergeInheritedAttribute(List *inh_columns, int exist_attno, const ColumnDef *newdef); @@ -447,16 +445,16 @@ static bool check_for_column_name_collision(Relation rel, const char *colname, bool if_not_exists); static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid); static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid); -static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, bool recurse, - LOCKMODE lockmode); -static bool set_attnotnull(List **wqueue, Relation rel, - AttrNumber attnum, bool recurse, LOCKMODE lockmode); -static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel, - char *constrname, char *colName, - bool recurse, bool recursing, - List **readyRels, LOCKMODE lockmode); -static ObjectAddress ATExecSetAttNotNull(List **wqueue, Relation rel, - const char *colName, LOCKMODE lockmode); +static void ATPrepDropNotNull(Relation rel, bool recurse, bool recursing); +static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode); +static void ATPrepSetNotNull(List **wqueue, Relation rel, + AlterTableCmd *cmd, bool recurse, bool recursing, + LOCKMODE lockmode, + AlterTableUtilityContext *context); +static ObjectAddress ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, + const char *colName, LOCKMODE lockmode); +static void ATExecCheckNotNull(AlteredTableInfo *tab, Relation rel, + const char *colName, LOCKMODE lockmode); static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr); static bool ConstraintImpliedByRelConstraint(Relation scanrel, List *testConstraint, List *provenConstraint); @@ -488,8 +486,6 @@ static ObjectAddress ATExecDropColumn(List **wqueue, Relation rel, const char *c bool recurse, bool recursing, bool missing_ok, LOCKMODE lockmode, ObjectAddresses *addrs); -static void ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd, - LOCKMODE lockmode, AlterTableUtilityContext *context); static ObjectAddress ATExecAddIndex(AlteredTableInfo *tab, Relation rel, IndexStmt *stmt, bool is_rebuild, LOCKMODE lockmode); static ObjectAddress ATExecAddStatistics(AlteredTableInfo *tab, Relation rel, @@ -501,11 +497,11 @@ static ObjectAddress ATExecAddConstraint(List **wqueue, static char *ChooseForeignKeyConstraintNameAddition(List *colnames); static ObjectAddress ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation rel, IndexStmt *stmt, LOCKMODE lockmode); -static ObjectAddress ATAddCheckNNConstraint(List **wqueue, - AlteredTableInfo *tab, Relation rel, - Constraint *constr, - bool recurse, bool recursing, bool is_readd, - LOCKMODE lockmode); +static ObjectAddress ATAddCheckConstraint(List **wqueue, + AlteredTableInfo *tab, Relation rel, + Constraint *constr, + bool recurse, bool recursing, bool is_readd, + LOCKMODE lockmode); static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, Constraint *fkconstraint, bool recurse, bool recursing, @@ -562,13 +558,9 @@ static void GetForeignKeyCheckTriggers(Relation trigrel, Oid *insertTriggerOid, Oid *updateTriggerOid); static void ATExecDropConstraint(Relation rel, const char *constrName, - DropBehavior behavior, bool recurse, + DropBehavior behavior, + bool recurse, bool recursing, bool missing_ok, LOCKMODE lockmode); -static ObjectAddress dropconstraint_internal(Relation rel, - HeapTuple constraintTup, DropBehavior behavior, - bool recurse, bool recursing, - bool missing_ok, List **readyRels, - LOCKMODE lockmode); static void ATPrepAlterColumnType(List **wqueue, AlteredTableInfo *tab, Relation rel, bool recurse, bool recursing, @@ -644,8 +636,6 @@ static void ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partPa static void CreateInheritance(Relation child_rel, Relation parent_rel, bool ispartition); static void RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached); -static void ATInheritAdjustNotNulls(Relation parent_rel, Relation child_rel, - int inhcount); static ObjectAddress ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd, AlterTableUtilityContext *context); @@ -667,7 +657,6 @@ static ObjectAddress ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, static void validatePartitionedIndex(Relation partedIdx, Relation partedTbl); static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx, Relation partitionTbl); -static void verifyPartitionIndexNotNull(IndexInfo *iinfo, Relation partIdx); static List *GetParentedForeignKeyRefs(Relation partition); static void ATDetachCheckNoForeignKeyRefs(Relation partition); static char GetAttributeCompression(Oid atttypid, const char *compression); @@ -710,10 +699,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, TupleDesc descriptor; List *inheritOids; List *old_constraints; - List *old_notnulls; List *rawDefaults; List *cookedDefaults; - List *nncols; Datum reloptions; ListCell *listptr; AttrNumber attnum; @@ -898,13 +885,12 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, MergeAttributes(stmt->tableElts, inheritOids, stmt->relation->relpersistence, stmt->partbound != NULL, - &old_constraints, &old_notnulls); + &old_constraints); /* * Create a tuple descriptor from the relation schema. Note that this - * deals with column names, types, and in-descriptor NOT NULL flags, but - * not default values, NOT NULL or CHECK constraints; we handle those - * below. + * deals with column names, types, and not-null constraints, but not + * default values or CHECK constraints; we handle those below. */ descriptor = BuildDescForRelation(stmt->tableElts); @@ -1276,17 +1262,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, AddRelationNewConstraints(rel, NIL, stmt->constraints, true, true, false, queryString); - /* - * Finally, merge the not-null constraints that are declared directly with - * those that come from parent relations (making sure to count inheritance - * appropriately for each), create them, and set the attnotnull flag on - * columns that don't yet have it. - */ - nncols = AddRelationNotNullConstraints(rel, stmt->nnconstraints, - old_notnulls); - foreach(listptr, nncols) - set_attnotnull(NULL, rel, lfirst_int(listptr), false, NoLock); - ObjectAddressSet(address, RelationRelationId, relationId); /* @@ -2437,8 +2412,6 @@ storage_name(char c) * Output arguments: * 'supconstr' receives a list of constraints belonging to the parents, * updated as necessary to be valid for the child. - * 'supnotnulls' receives a list of CookedConstraints that corresponds to - * constraints coming from inheritance parents. * * Return value: * Completed schema list. @@ -2469,10 +2442,7 @@ storage_name(char c) * * Constraints (including not-null constraints) for the child table * are the union of all relevant constraints, from both the child schema - * and parent tables. In addition, in legacy inheritance, each column that - * appears in a primary key in any of the parents also gets a NOT NULL - * constraint (partitioning doesn't need this, because the PK itself gets - * inherited.) + * and parent tables. * * The default value for a child column is defined as: * (1) If the child schema specifies a default, that value is used. @@ -2491,11 +2461,10 @@ storage_name(char c) */ static List * MergeAttributes(List *columns, const List *supers, char relpersistence, - bool is_partition, List **supconstr, List **supnotnulls) + bool is_partition, List **supconstr) { List *inh_columns = NIL; List *constraints = NIL; - List *nnconstraints = NIL; bool have_bogus_defaults = false; int child_attno; static Node bogus_marker = {0}; /* marks conflicting defaults */ @@ -2606,11 +2575,8 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, AttrMap *newattmap; List *inherited_defaults; List *cols_with_defaults; - List *nnconstrs; ListCell *lc1; ListCell *lc2; - Bitmapset *pkattrs; - Bitmapset *nncols = NULL; /* caller already got lock */ relation = table_open(parent, NoLock); @@ -2698,20 +2664,6 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, /* We can't process inherited defaults until newattmap is complete. */ inherited_defaults = cols_with_defaults = NIL; - /* - * All columns that are part of the parent's primary key need to be - * NOT NULL; if partition just the attnotnull bit, otherwise a full - * constraint (if they don't have one already). Also, we request - * attnotnull on columns that have a not-null constraint that's not - * marked NO INHERIT. - */ - pkattrs = RelationGetIndexAttrBitmap(relation, - INDEX_ATTR_BITMAP_PRIMARY_KEY); - nnconstrs = RelationGetNotNullConstraints(RelationGetRelid(relation), true); - foreach(lc1, nnconstrs) - nncols = bms_add_member(nncols, - ((CookedConstraint *) lfirst(lc1))->attnum); - for (AttrNumber parent_attno = 1; parent_attno <= tupleDesc->natts; parent_attno++) { @@ -2733,6 +2685,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, */ newdef = makeColumnDef(attributeName, attribute->atttypid, attribute->atttypmod, attribute->attcollation); + newdef->is_not_null = attribute->attnotnull; newdef->storage = attribute->attstorage; newdef->generated = attribute->attgenerated; if (CompressionMethodIsValid(attribute->attcompression)) @@ -2777,44 +2730,9 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, inh_columns = lappend(inh_columns, newdef); newattmap->attnums[parent_attno - 1] = ++child_attno; - mergeddef = newdef; } - /* - * mark attnotnull if parent has it and it's not NO INHERIT - */ - if (bms_is_member(parent_attno, nncols) || - bms_is_member(parent_attno - FirstLowInvalidHeapAttributeNumber, - pkattrs)) - mergeddef->is_not_null = true; - - /* - * In regular inheritance, columns in the parent's primary key get - * an extra not-null constraint. Partitioning doesn't need this, - * because the PK itself is going to be cloned to the partition. - */ - if (!is_partition && - bms_is_member(parent_attno - - FirstLowInvalidHeapAttributeNumber, - pkattrs)) - { - CookedConstraint *nn; - - nn = palloc(sizeof(CookedConstraint)); - nn->contype = CONSTR_NOTNULL; - nn->conoid = InvalidOid; - nn->name = NULL; - nn->attnum = newattmap->attnums[parent_attno - 1]; - nn->expr = NULL; - nn->skip_validation = false; - nn->is_local = false; - nn->inhcount = 1; - nn->is_no_inherit = false; - - nnconstraints = lappend(nnconstraints, nn); - } - /* * Locate default/generation expression if any */ @@ -2926,23 +2844,6 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, } } - /* - * Also copy the not-null constraints from this parent. The - * attnotnull markings were already installed above. - */ - foreach(lc1, nnconstrs) - { - CookedConstraint *nn = lfirst(lc1); - - Assert(nn->contype == CONSTR_NOTNULL); - - nn->attnum = newattmap->attnums[nn->attnum - 1]; - nn->is_local = false; - nn->inhcount = 1; - - nnconstraints = lappend(nnconstraints, nn); - } - free_attrmap(newattmap); /* @@ -3013,7 +2914,8 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, /* * Now that we have the column definition list for a partition, we can * check whether the columns referenced in the column constraint specs - * actually exist. Also, merge column defaults. + * actually exist. Also, we merge parent's not-null constraints and + * defaults into each corresponding column definition. */ if (is_partition) { @@ -3030,6 +2932,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, if (strcmp(coldef->colname, restdef->colname) == 0) { found = true; + coldef->is_not_null |= restdef->is_not_null; /* * Check for conflicts related to generated columns. @@ -3118,7 +3021,6 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, } *supconstr = constraints; - *supnotnulls = nnconstraints; return columns; } @@ -3399,6 +3301,11 @@ MergeInheritedAttribute(List *inh_columns, format_type_with_typemod(prevtypeid, prevtypmod), format_type_with_typemod(newtypeid, newtypmod)))); + /* + * Merge of not-null constraints = OR 'em together + */ + prevdef->is_not_null |= newdef->is_not_null; + /* * Must have the same collation */ @@ -4022,10 +3929,7 @@ rename_constraint_internal(Oid myrelid, constraintOid); con = (Form_pg_constraint) GETSTRUCT(tuple); - if (myrelid && - (con->contype == CONSTRAINT_CHECK || - con->contype == CONSTRAINT_NOTNULL) && - !con->connoinherit) + if (myrelid && con->contype == CONSTRAINT_CHECK && !con->connoinherit) { if (recurse) { @@ -4610,7 +4514,6 @@ AlterTableGetLockLevel(List *cmds) case AT_AddIndexConstraint: case AT_ReplicaIdentity: case AT_SetNotNull: - case AT_SetAttNotNull: case AT_EnableRowSecurity: case AT_DisableRowSecurity: case AT_ForceRowSecurity: @@ -4758,6 +4661,15 @@ AlterTableGetLockLevel(List *cmds) cmd_lockmode = AccessExclusiveLock; break; + case AT_CheckNotNull: + + /* + * This only examines the table's schema; but lock must be + * strong enough to prevent concurrent DROP NOT NULL. + */ + cmd_lockmode = AccessShareLock; + break; + default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); @@ -4915,23 +4827,21 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE); - /* Set up recursion for phase 2; no other prep needed */ - if (recurse) - cmd->recurse = true; + ATPrepDropNotNull(rel, recurse, recursing); + ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); pass = AT_PASS_DROP; break; case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE); - /* Set up recursion for phase 2; no other prep needed */ - if (recurse) - cmd->recurse = true; + /* Need command-specific recursion decision */ + ATPrepSetNotNull(wqueue, rel, cmd, recurse, recursing, + lockmode, context); pass = AT_PASS_COL_ATTRS; break; - case AT_SetAttNotNull: /* set pg_attribute.attnotnull without adding - * a constraint */ + case AT_CheckNotNull: /* check column is already marked NOT NULL */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE); - /* Need command-specific recursion decision */ ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); + /* No command-specific prep needed */ pass = AT_PASS_COL_ATTRS; break; case AT_SetExpression: /* ALTER COLUMN SET EXPRESSION */ @@ -4985,12 +4895,10 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_AddConstraint: /* ADD CONSTRAINT */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE); + /* Recursion occurs during execution phase */ + /* No command-specific prep needed except saving recurse flag */ if (recurse) - { - /* recurses at exec time; lock descendants and set flag */ - (void) find_all_inheritors(RelationGetRelid(rel), lockmode, NULL); cmd->recurse = true; - } pass = AT_PASS_ADD_CONSTR; break; case AT_AddIndexConstraint: /* ADD CONSTRAINT USING INDEX */ @@ -5320,14 +5228,13 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, address = ATExecDropIdentity(rel, cmd->name, cmd->missing_ok, lockmode, cmd->recurse, false); break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ - address = ATExecDropNotNull(rel, cmd->name, cmd->recurse, lockmode); + address = ATExecDropNotNull(rel, cmd->name, lockmode); break; case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */ - address = ATExecSetNotNull(wqueue, rel, NULL, cmd->name, - cmd->recurse, false, NULL, lockmode); + address = ATExecSetNotNull(tab, rel, cmd->name, lockmode); break; - case AT_SetAttNotNull: /* set pg_attribute.attnotnull */ - address = ATExecSetAttNotNull(wqueue, rel, cmd->name, lockmode); + case AT_CheckNotNull: /* check column is already marked NOT NULL */ + ATExecCheckNotNull(tab, rel, cmd->name, lockmode); break; case AT_SetExpression: address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode); @@ -5410,7 +5317,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, break; case AT_DropConstraint: /* DROP CONSTRAINT */ ATExecDropConstraint(rel, cmd->name, cmd->behavior, - cmd->recurse, + cmd->recurse, false, cmd->missing_ok, lockmode); break; case AT_AlterColumnType: /* ALTER COLUMN TYPE */ @@ -5689,23 +5596,21 @@ ATParseTransformCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, */ switch (cmd2->subtype) { - case AT_SetAttNotNull: - ATSimpleRecursion(wqueue, rel, cmd2, recurse, lockmode, context); + case AT_SetNotNull: + /* Need command-specific recursion decision */ + ATPrepSetNotNull(wqueue, rel, cmd2, + recurse, false, + lockmode, context); pass = AT_PASS_COL_ATTRS; break; case AT_AddIndex: - - /* - * A primary key on an inheritance parent needs supporting NOT - * NULL constraint on its children; enqueue commands to create - * those or mark them inherited if they already exist. - */ - ATPrepAddPrimaryKey(wqueue, rel, cmd2, lockmode, context); + /* This command never recurses */ + /* No command-specific prep needed */ pass = AT_PASS_ADD_INDEX; break; case AT_AddIndexConstraint: - /* as above */ - ATPrepAddPrimaryKey(wqueue, rel, cmd2, lockmode, context); + /* This command never recurses */ + /* No command-specific prep needed */ pass = AT_PASS_ADD_INDEXCONSTR; break; case AT_AddConstraint: @@ -6372,7 +6277,6 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) RelationGetRelationName(oldrel)), errtableconstraint(oldrel, con->name))); break; - case CONSTR_NOTNULL: case CONSTR_FOREIGN: /* Nothing to do here */ break; @@ -6482,12 +6386,12 @@ alter_table_type_to_string(AlterTableType cmdtype) return "ALTER COLUMN ... DROP NOT NULL"; case AT_SetNotNull: return "ALTER COLUMN ... SET NOT NULL"; - case AT_SetAttNotNull: - return NULL; /* not real grammar */ case AT_SetExpression: return "ALTER COLUMN ... SET EXPRESSION"; case AT_DropExpression: return "ALTER COLUMN ... DROP EXPRESSION"; + case AT_CheckNotNull: + return NULL; /* not real grammar */ case AT_SetStatistics: return "ALTER COLUMN ... SET STATISTICS"; case AT_SetOptions: @@ -7570,21 +7474,41 @@ add_column_collation_dependency(Oid relid, int32 attnum, Oid collid) /* * ALTER TABLE ALTER COLUMN DROP NOT NULL - * + */ + +static void +ATPrepDropNotNull(Relation rel, bool recurse, bool recursing) +{ + /* + * If the parent is a partitioned table, like check constraints, we do not + * support removing the NOT NULL while partitions exist. + */ + if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + { + PartitionDesc partdesc = RelationGetPartitionDesc(rel, true); + + Assert(partdesc != NULL); + if (partdesc->nparts > 0 && !recurse && !recursing) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot remove constraint from only the partitioned table when partitions exist"), + errhint("Do not specify the ONLY keyword."))); + } +} + +/* * Return the address of the modified column. If the column was already * nullable, InvalidObjectAddress is returned. */ static ObjectAddress -ATExecDropNotNull(Relation rel, const char *colName, bool recurse, - LOCKMODE lockmode) +ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) { HeapTuple tuple; - HeapTuple conTup; Form_pg_attribute attTup; AttrNumber attnum; Relation attr_rel; + List *indexoidlist; ObjectAddress address; - List *readyRels; /* * lookup the attribute @@ -7599,15 +7523,6 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse, colName, RelationGetRelationName(rel)))); attTup = (Form_pg_attribute) GETSTRUCT(tuple); attnum = attTup->attnum; - ObjectAddressSubSet(address, RelationRelationId, - RelationGetRelid(rel), attnum); - - /* If the column is already nullable there's nothing to do. */ - if (!attTup->attnotnull) - { - table_close(attr_rel, RowExclusiveLock); - return InvalidObjectAddress; - } /* Prevent them from altering a system attribute */ if (attnum <= 0) @@ -7623,37 +7538,60 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse, colName, RelationGetRelationName(rel)))); /* - * It's not OK to remove a constraint only for the parent and leave it in - * the children, so disallow that. + * Check that the attribute is not in a primary key or in an index used as + * a replica identity. + * + * Note: we'll throw error even if the pkey index is not valid. */ - if (!recurse) + + /* Loop over all indexes on the relation */ + indexoidlist = RelationGetIndexList(rel); + + foreach_oid(indexoid, indexoidlist) { - if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) - { - PartitionDesc partdesc; + HeapTuple indexTuple; + Form_pg_index indexStruct; - partdesc = RelationGetPartitionDesc(rel, true); + indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid)); + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "cache lookup failed for index %u", indexoid); + indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); - if (partdesc->nparts > 0) - ereport(ERROR, - errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot remove constraint from only the partitioned table when partitions exist"), - errhint("Do not specify the ONLY keyword.")); - } - else if (rel->rd_rel->relhassubclass && - find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL) + /* + * If the index is not a primary key or an index used as replica + * identity, skip the check. + */ + if (indexStruct->indisprimary || indexStruct->indisreplident) { - ereport(ERROR, - errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("not-null constraint on column \"%s\" must be removed in child tables too", - colName), - errhint("Do not specify the ONLY keyword.")); + /* + * Loop over each attribute in the primary key or the index used + * as replica identity and see if it matches the to-be-altered + * attribute. + */ + for (int i = 0; i < indexStruct->indnkeyatts; i++) + { + if (indexStruct->indkey.values[i] == attnum) + { + if (indexStruct->indisprimary) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("column \"%s\" is in a primary key", + colName))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("column \"%s\" is in index used as replica identity", + colName))); + } + } } + + ReleaseSysCache(indexTuple); } - /* - * If rel is partition, shouldn't drop NOT NULL if parent has the same. - */ + list_free(indexoidlist); + + /* If rel is partition, shouldn't drop NOT NULL if parent has the same */ if (rel->rd_rel->relispartition) { Oid parentId = get_partition_parent(RelationGetRelid(rel), false); @@ -7671,52 +7609,19 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse, } /* - * 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. + * Okay, actually perform the catalog change ... if needed */ - conTup = findNotNullConstraint(RelationGetRelid(rel), colName); - if (conTup == NULL) + if (attTup->attnotnull) { - Bitmapset *pkcols; - Bitmapset *ircols; - - /* - * 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, - pkcols)) - ereport(ERROR, - errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("column \"%s\" is in a primary key", colName)); - - /* 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); - heap_freetuple(conTup); + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); } + else + address = InvalidObjectAddress; InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), attnum); @@ -7727,147 +7632,102 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse, } /* - * Helper to set pg_attribute.attnotnull if it isn't set, and to tell phase 3 - * to verify it; recurses to apply the same to children. - * - * When called to alter an existing table, 'wqueue' must be given so that we can - * queue a check that existing tuples pass the constraint. When called from - * table creation, 'wqueue' should be passed as NULL. - * - * Returns true if the flag was set in any table, otherwise false. + * ALTER TABLE ALTER COLUMN SET NOT NULL */ -static bool -set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum, bool recurse, - LOCKMODE lockmode) -{ - HeapTuple tuple; - Form_pg_attribute attForm; - bool retval = false; - /* Guard against stack overflow due to overly deep inheritance tree. */ - check_stack_depth(); +static void +ATPrepSetNotNull(List **wqueue, Relation rel, + AlterTableCmd *cmd, bool recurse, bool recursing, + LOCKMODE lockmode, AlterTableUtilityContext *context) +{ + /* + * If we're already recursing, there's nothing to do; the topmost + * invocation of ATSimpleRecursion already visited all children. + */ + if (recursing) + return; - tuple = SearchSysCacheCopyAttNum(RelationGetRelid(rel), attnum); - if (!HeapTupleIsValid(tuple)) - elog(ERROR, "cache lookup failed for attribute %d of relation %u", - attnum, RelationGetRelid(rel)); - attForm = (Form_pg_attribute) GETSTRUCT(tuple); - if (!attForm->attnotnull) + /* + * If the target column is already marked NOT NULL, we can skip recursing + * to children, because their columns should already be marked NOT NULL as + * well. But there's no point in checking here unless the relation has + * some children; else we can just wait till execution to check. (If it + * does have children, however, this can save taking per-child locks + * unnecessarily. This greatly improves concurrency in some parallel + * restore scenarios.) + * + * Unfortunately, we can only apply this optimization to partitioned + * tables, because traditional inheritance doesn't enforce that child + * columns be NOT NULL when their parent is. (That's a bug that should + * get fixed someday.) + */ + if (rel->rd_rel->relhassubclass && + rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { - Relation attr_rel; - - attr_rel = table_open(AttributeRelationId, RowExclusiveLock); - - attForm->attnotnull = true; - CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple); - - table_close(attr_rel, RowExclusiveLock); + HeapTuple tuple; + bool attnotnull; - /* - * And set up for existing values to be checked, unless another - * constraint already proves this. - */ - if (wqueue && !NotNullImpliedByRelConstraints(rel, attForm)) - { - AlteredTableInfo *tab; + tuple = SearchSysCacheAttName(RelationGetRelid(rel), cmd->name); - tab = ATGetQueueEntry(wqueue, rel); - tab->verify_new_notnull = true; - } + /* Might as well throw the error now, if name is bad */ + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + cmd->name, RelationGetRelationName(rel)))); - retval = true; + attnotnull = ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull; + ReleaseSysCache(tuple); + if (attnotnull) + return; } - if (recurse) + /* + * If we have ALTER TABLE ONLY ... SET NOT NULL on a partitioned table, + * apply ALTER TABLE ... CHECK NOT NULL to every child. Otherwise, use + * normal recursion logic. + */ + if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE && + !recurse) { - List *children; - ListCell *lc; - - /* Make above update visible, for multiple inheritance cases */ - if (retval) - CommandCounterIncrement(); - - children = find_inheritance_children(RelationGetRelid(rel), lockmode); - foreach(lc, children) - { - Oid childrelid = lfirst_oid(lc); - Relation childrel; - AttrNumber childattno; - - /* find_inheritance_children already got lock */ - childrel = table_open(childrelid, NoLock); - CheckTableNotInUse(childrel, "ALTER TABLE"); + AlterTableCmd *newcmd = makeNode(AlterTableCmd); - childattno = get_attnum(RelationGetRelid(childrel), - get_attname(RelationGetRelid(rel), attnum, - false)); - retval |= set_attnotnull(wqueue, childrel, childattno, - recurse, lockmode); - table_close(childrel, NoLock); - } + newcmd->subtype = AT_CheckNotNull; + newcmd->name = pstrdup(cmd->name); + ATSimpleRecursion(wqueue, rel, newcmd, true, lockmode, context); } - - return retval; + else + ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); } /* - * ALTER TABLE ALTER COLUMN SET NOT NULL - * - * Add a not-null constraint to a single table and its children. Returns - * the address of the constraint added to the parent relation, if one gets - * added, or InvalidObjectAddress otherwise. - * - * We must recurse to child tables during execution, rather than using - * ALTER TABLE's normal prep-time recursion. + * Return the address of the modified column. If the column was already NOT + * NULL, InvalidObjectAddress is returned. */ static ObjectAddress -ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, - bool recurse, bool recursing, List **readyRels, - LOCKMODE lockmode) +ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, + const char *colName, LOCKMODE lockmode) { HeapTuple tuple; - Relation constr_rel; - ScanKeyData skey; - SysScanDesc conscan; AttrNumber attnum; + Relation attr_rel; ObjectAddress address; - Constraint *constraint; - CookedConstraint *ccon; - List *cooked; - bool is_no_inherit = false; - List *ready = NIL; - - /* Guard against stack overflow due to overly deep inheritance tree. */ - check_stack_depth(); /* - * In cases of multiple inheritance, we might visit the same child more - * than once. In the topmost call, set up a list that we fill with all - * visited relations, to skip those. + * lookup the attribute */ - if (readyRels == NULL) - { - Assert(!recursing); - readyRels = &ready; - } - if (list_member_oid(*readyRels, RelationGetRelid(rel))) - return InvalidObjectAddress; - *readyRels = lappend_oid(*readyRels, RelationGetRelid(rel)); + attr_rel = table_open(AttributeRelationId, RowExclusiveLock); - /* At top level, permission check was done in ATPrepCmd, else do it */ - if (recursing) - { - ATSimplePermissions(AT_AddConstraint, rel, ATT_TABLE | ATT_FOREIGN_TABLE); - Assert(conName != NULL); - } + tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName); - attnum = get_attnum(RelationGetRelid(rel), colName); - if (attnum == InvalidAttrNumber) + if (!HeapTupleIsValid(tuple)) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_COLUMN), errmsg("column \"%s\" of relation \"%s\" does not exist", colName, RelationGetRelationName(rel)))); + attnum = ((Form_pg_attribute) GETSTRUCT(tuple))->attnum; + /* Prevent them from altering a system attribute */ if (attnum <= 0) ereport(ERROR, @@ -7875,188 +7735,80 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, errmsg("cannot alter system column \"%s\"", colName))); - /* See if there's already a constraint */ - constr_rel = table_open(ConstraintRelationId, RowExclusiveLock); - ScanKeyInit(&skey, - Anum_pg_constraint_conrelid, - BTEqualStrategyNumber, F_OIDEQ, - ObjectIdGetDatum(RelationGetRelid(rel))); - conscan = systable_beginscan(constr_rel, ConstraintRelidTypidNameIndexId, true, - NULL, 1, &skey); - - while (HeapTupleIsValid(tuple = systable_getnext(conscan))) + /* + * Okay, actually perform the catalog change ... if needed + */ + if (!((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull) { - Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple); - bool changed = false; - HeapTuple copytup; + ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull = true; - if (conForm->contype != CONSTRAINT_NOTNULL) - continue; - - if (extractNotNullColumn(tuple) != attnum) - continue; - - copytup = heap_copytuple(tuple); - conForm = (Form_pg_constraint) GETSTRUCT(copytup); - - /* - * Don't let a NO INHERIT constraint be changed into inherit. - */ - if (conForm->connoinherit && recurse) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot change NO INHERIT status of NOT NULL constraint \"%s\" on relation \"%s\"", - NameStr(conForm->conname), - RelationGetRelationName(rel))); + CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple); /* - * If we find an appropriate constraint, we're almost done, but just - * need to change some properties on it: if we're recursing, increment - * coninhcount; if not, set conislocal if not already set. + * Ordinarily phase 3 must ensure that no NULLs exist in columns that + * are set NOT NULL; however, if we can find a constraint which proves + * this then we can skip that. We needn't bother looking if we've + * already found that we must verify some other not-null constraint. */ - if (recursing) - { - conForm->coninhcount++; - changed = true; - } - else if (!conForm->conislocal) + if (!tab->verify_new_notnull && + !NotNullImpliedByRelConstraints(rel, (Form_pg_attribute) GETSTRUCT(tuple))) { - conForm->conislocal = true; - changed = true; - } - - if (changed) - { - CatalogTupleUpdate(constr_rel, ©tup->t_self, copytup); - ObjectAddressSet(address, ConstraintRelationId, conForm->oid); + /* Tell Phase 3 it needs to test the constraint */ + tab->verify_new_notnull = true; } - systable_endscan(conscan); - table_close(constr_rel, RowExclusiveLock); - - if (changed) - return address; - else - return InvalidObjectAddress; - } - - systable_endscan(conscan); - table_close(constr_rel, RowExclusiveLock); - - /* - * If we're asked not to recurse, and children exist, raise an error for - * partitioned tables. For inheritance, we act as if NO INHERIT had been - * specified. - */ - if (!recurse && - find_inheritance_children(RelationGetRelid(rel), - NoLock) != NIL) - { - if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) - ereport(ERROR, - errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("constraint must be added to child tables too"), - errhint("Do not specify the ONLY keyword.")); - else - is_no_inherit = true; + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); } - - /* - * No constraint exists; we must add one. First determine a name to use, - * if we haven't already. - */ - if (!recursing) - { - Assert(conName == NULL); - conName = ChooseConstraintName(RelationGetRelationName(rel), - colName, "not_null", - RelationGetNamespace(rel), - NIL); - } - constraint = makeNode(Constraint); - constraint->contype = CONSTR_NOTNULL; - constraint->conname = conName; - constraint->deferrable = false; - constraint->initdeferred = false; - constraint->location = -1; - constraint->keys = list_make1(makeString(colName)); - constraint->is_no_inherit = is_no_inherit; - constraint->inhcount = recursing ? 1 : 0; - constraint->skip_validation = false; - constraint->initially_valid = true; - - /* and do it */ - cooked = AddRelationNewConstraints(rel, NIL, list_make1(constraint), - false, !recursing, false, NULL); - ccon = linitial(cooked); - ObjectAddressSet(address, ConstraintRelationId, ccon->conoid); + else + address = InvalidObjectAddress; InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), attnum); - /* - * Mark pg_attribute.attnotnull for the column. Tell that function not to - * recurse, because we're going to do it here. - */ - set_attnotnull(wqueue, rel, attnum, false, lockmode); - - /* - * Recurse to propagate the constraint to children that don't have one. - */ - if (recurse) - { - List *children; - ListCell *lc; - - children = find_inheritance_children(RelationGetRelid(rel), - lockmode); - - foreach(lc, children) - { - Relation childrel; - - childrel = table_open(lfirst_oid(lc), NoLock); - - ATExecSetNotNull(wqueue, childrel, - conName, colName, recurse, true, - readyRels, lockmode); - - table_close(childrel, NoLock); - } - } + table_close(attr_rel, RowExclusiveLock); return address; } /* - * ALTER TABLE ALTER COLUMN SET ATTNOTNULL + * ALTER TABLE ALTER COLUMN CHECK NOT NULL * - * This doesn't exist in the grammar; it's used when creating a - * primary key and the column is not already marked attnotnull. + * This doesn't exist in the grammar, but we generate AT_CheckNotNull + * commands against the partitions of a partitioned table if the user + * writes ALTER TABLE ONLY ... SET NOT NULL on the partitioned table, + * or tries to create a primary key on it (which internally creates + * AT_SetNotNull on the partitioned table). Such a command doesn't + * allow us to actually modify any partition, but we want to let it + * go through if the partitions are already properly marked. + * + * In future, this might need to adjust the child table's state, likely + * by incrementing an inheritance count for the attnotnull constraint. + * For now we need only check for the presence of the flag. */ -static ObjectAddress -ATExecSetAttNotNull(List **wqueue, Relation rel, - const char *colName, LOCKMODE lockmode) +static void +ATExecCheckNotNull(AlteredTableInfo *tab, Relation rel, + const char *colName, LOCKMODE lockmode) { - AttrNumber attnum; - ObjectAddress address = InvalidObjectAddress; + HeapTuple tuple; - attnum = get_attnum(RelationGetRelid(rel), colName); - if (attnum == InvalidAttrNumber) + tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName); + + if (!HeapTupleIsValid(tuple)) ereport(ERROR, errcode(ERRCODE_UNDEFINED_COLUMN), errmsg("column \"%s\" of relation \"%s\" does not exist", colName, RelationGetRelationName(rel))); - /* - * Make the change, if necessary, and only if so report the column as - * changed - */ - if (set_attnotnull(wqueue, rel, attnum, false, lockmode)) - ObjectAddressSubSet(address, RelationRelationId, - RelationGetRelid(rel), attnum); + if (!((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("constraint must be added to child tables too"), + errdetail("Column \"%s\" of relation \"%s\" is not already NOT NULL.", + colName, RelationGetRelationName(rel)), + errhint("Do not specify the ONLY keyword."))); - return address; + ReleaseSysCache(tuple); } /* @@ -9362,85 +9114,6 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName, return object; } -/* - * Prepare to add a primary key on an inheritance parent, by adding NOT NULL - * constraint on its children. - */ -static void -ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd, - LOCKMODE lockmode, AlterTableUtilityContext *context) -{ - List *children; - List *newconstrs = NIL; - IndexStmt *indexstmt; - - /* No work if not creating a primary key */ - if (!IsA(cmd->def, IndexStmt)) - return; - indexstmt = castNode(IndexStmt, cmd->def); - if (!indexstmt->primary) - return; - - /* No work if no legacy inheritance children are present */ - if (rel->rd_rel->relkind != RELKIND_RELATION || - !rel->rd_rel->relhassubclass) - return; - - /* - * Acquire locks all the way down the hierarchy. The recursion to lower - * levels occurs at execution time as necessary, so we don't need to do it - * here, and we don't need the returned list either. - */ - (void) find_all_inheritors(RelationGetRelid(rel), lockmode, NULL); - - /* - * Construct the list of constraints that we need to add to each child - * relation. - */ - foreach_node(IndexElem, elem, indexstmt->indexParams) - { - Constraint *nnconstr; - - Assert(elem->expr == NULL); - - nnconstr = makeNode(Constraint); - nnconstr->contype = CONSTR_NOTNULL; - nnconstr->conname = NULL; /* XXX use PK name? */ - nnconstr->inhcount = 1; - nnconstr->deferrable = false; - nnconstr->initdeferred = false; - nnconstr->location = -1; - nnconstr->keys = list_make1(makeString(elem->name)); - nnconstr->skip_validation = false; - nnconstr->initially_valid = true; - - newconstrs = lappend(newconstrs, nnconstr); - } - - /* Finally, add AT subcommands to add each constraint to each child. */ - children = find_inheritance_children(RelationGetRelid(rel), NoLock); - foreach_oid(childrelid, children) - { - Relation childrel = table_open(childrelid, NoLock); - AlterTableCmd *newcmd = makeNode(AlterTableCmd); - ListCell *lc2; - - newcmd->subtype = AT_AddConstraint; - newcmd->recurse = true; - - foreach(lc2, newconstrs) - { - /* ATPrepCmd copies newcmd, so we can scribble on it here */ - newcmd->def = lfirst(lc2); - - ATPrepCmd(wqueue, childrel, newcmd, - true, false, lockmode, context); - } - - table_close(childrel, NoLock); - } -} - /* * ALTER TABLE ADD INDEX * @@ -9636,18 +9309,17 @@ ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, Assert(IsA(newConstraint, Constraint)); /* - * Currently, we only expect to see CONSTR_CHECK, CONSTR_NOTNULL and - * CONSTR_FOREIGN nodes arriving here (see the preprocessing done in - * parse_utilcmd.c). + * Currently, we only expect to see CONSTR_CHECK and CONSTR_FOREIGN nodes + * arriving here (see the preprocessing done in parse_utilcmd.c). Use a + * switch anyway to make it easier to add more code later. */ switch (newConstraint->contype) { case CONSTR_CHECK: - case CONSTR_NOTNULL: address = - ATAddCheckNNConstraint(wqueue, tab, rel, - newConstraint, recurse, false, is_readd, - lockmode); + ATAddCheckConstraint(wqueue, tab, rel, + newConstraint, recurse, false, is_readd, + lockmode); break; case CONSTR_FOREIGN: @@ -9728,9 +9400,9 @@ ChooseForeignKeyConstraintNameAddition(List *colnames) } /* - * Add a check or not-null constraint to a single table and its children. - * Returns the address of the constraint added to the parent relation, - * if one gets added, or InvalidObjectAddress otherwise. + * Add a check constraint to a single table and its children. Returns the + * address of the constraint added to the parent relation, if one gets added, + * or InvalidObjectAddress otherwise. * * Subroutine for ATExecAddConstraint. * @@ -9743,9 +9415,9 @@ ChooseForeignKeyConstraintNameAddition(List *colnames) * the parent table and pass that down. */ static ObjectAddress -ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, - Constraint *constr, bool recurse, bool recursing, - bool is_readd, LOCKMODE lockmode) +ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, + Constraint *constr, bool recurse, bool recursing, + bool is_readd, LOCKMODE lockmode) { List *newcons; ListCell *lcon; @@ -9753,9 +9425,6 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, ListCell *child; ObjectAddress address = InvalidObjectAddress; - /* Guard against stack overflow due to overly deep inheritance tree. */ - check_stack_depth(); - /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ATSimplePermissions(AT_AddConstraint, rel, ATT_TABLE | ATT_FOREIGN_TABLE); @@ -9786,7 +9455,7 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, { CookedConstraint *ccon = (CookedConstraint *) lfirst(lcon); - if (!ccon->skip_validation && ccon->contype != CONSTR_NOTNULL) + if (!ccon->skip_validation) { NewConstraint *newcon; @@ -9802,19 +9471,11 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, if (constr->conname == NULL) constr->conname = ccon->name; - /* - * If adding a not-null constraint, set the pg_attribute flag and tell - * phase 3 to verify existing rows, if needed. - */ - if (constr->contype == CONSTR_NOTNULL) - set_attnotnull(wqueue, rel, ccon->attnum, - !ccon->is_no_inherit, lockmode); - ObjectAddressSet(address, ConstraintRelationId, ccon->conoid); } /* At this point we must have a locked-down name to use */ - Assert(newcons == NIL || constr->conname != NULL); + Assert(constr->conname != NULL); /* Advance command counter in case same table is visited multiple times */ CommandCounterIncrement(); @@ -9852,12 +9513,6 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, (errcode(ERRCODE_INVALID_TABLE_DEFINITION), errmsg("constraint must be added to child tables too"))); - /* - * The constraint must appear as inherited in children, so create a - * modified constraint object to use. - */ - constr = copyObject(constr); - constr->inhcount = 1; foreach(child, children) { Oid childrelid = lfirst_oid(child); @@ -9871,13 +9526,9 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, /* Find or create work queue entry for this table */ childtab = ATGetQueueEntry(wqueue, childrel); - /* - * Recurse to child. XXX if we didn't create a constraint on the - * parent because it already existed, and we do create one on a child, - * should we return that child's constraint ObjectAddress here? - */ - ATAddCheckNNConstraint(wqueue, childtab, childrel, - constr, recurse, true, is_readd, lockmode); + /* Recurse to child */ + ATAddCheckConstraint(wqueue, childtab, childrel, + constr, recurse, true, is_readd, lockmode); table_close(childrel, NoLock); } @@ -12889,14 +12540,23 @@ createForeignKeyCheckTriggers(Oid myRelOid, Oid refRelOid, */ static void ATExecDropConstraint(Relation rel, const char *constrName, - DropBehavior behavior, bool recurse, + DropBehavior behavior, + bool recurse, bool recursing, bool missing_ok, LOCKMODE lockmode) { + List *children; Relation conrel; + Form_pg_constraint con; SysScanDesc scan; ScanKeyData skey[3]; HeapTuple tuple; bool found = false; + bool is_no_inherit_constraint = false; + char contype; + + /* At top level, permission check was done in ATPrepCmd, else do it */ + if (recursing) + ATSimplePermissions(AT_DropConstraint, rel, ATT_TABLE | ATT_FOREIGN_TABLE); conrel = table_open(ConstraintRelationId, RowExclusiveLock); @@ -12921,260 +12581,80 @@ ATExecDropConstraint(Relation rel, const char *constrName, /* There can be at most one matching row */ if (HeapTupleIsValid(tuple = systable_getnext(scan))) { - List *readyRels = NIL; + ObjectAddress conobj; - dropconstraint_internal(rel, tuple, behavior, recurse, false, - missing_ok, &readyRels, lockmode); - found = true; - } - - systable_endscan(scan); + con = (Form_pg_constraint) GETSTRUCT(tuple); - if (!found) - { - if (!missing_ok) + /* Don't drop inherited constraints */ + if (con->coninhcount > 0 && !recursing) ereport(ERROR, - errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("constraint \"%s\" of relation \"%s\" does not exist", - constrName, RelationGetRelationName(rel))); - else - ereport(NOTICE, - errmsg("constraint \"%s\" of relation \"%s\" does not exist, skipping", - constrName, RelationGetRelationName(rel))); - } - - table_close(conrel, RowExclusiveLock); -} - -/* - * Remove a constraint, using its pg_constraint tuple - * - * Implementation for ALTER TABLE DROP CONSTRAINT and ALTER TABLE ALTER COLUMN - * DROP NOT NULL. - * - * Returns the address of the constraint being removed. - */ -static ObjectAddress -dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior behavior, - bool recurse, bool recursing, bool missing_ok, List **readyRels, - LOCKMODE lockmode) -{ - Relation conrel; - Form_pg_constraint con; - ObjectAddress conobj; - List *children; - bool is_no_inherit_constraint = false; - char *constrName; - List *unconstrained_cols = NIL; - char *colname = NULL; - bool dropping_pk = false; - - if (list_member_oid(*readyRels, RelationGetRelid(rel))) - return InvalidObjectAddress; - *readyRels = lappend_oid(*readyRels, RelationGetRelid(rel)); - - /* Guard against stack overflow due to overly deep inheritance tree. */ - check_stack_depth(); - - /* At top level, permission check was done in ATPrepCmd, else do it */ - if (recursing) - ATSimplePermissions(AT_DropConstraint, rel, ATT_TABLE | ATT_FOREIGN_TABLE); - - conrel = table_open(ConstraintRelationId, RowExclusiveLock); - - con = (Form_pg_constraint) GETSTRUCT(constraintTup); - constrName = NameStr(con->conname); - - /* - * If we're asked to drop a constraint which is both defined locally and - * inherited, we can simply mark it as no longer having a local - * definition, and no further changes are required. - * - * XXX We do this for not-null constraints only, not CHECK, because the - * latter have historically not behaved this way and it might be confusing - * to change the behavior now. - */ - if (con->contype == CONSTRAINT_NOTNULL && - con->conislocal && con->coninhcount > 0) - { - HeapTuple copytup; - - copytup = heap_copytuple(constraintTup); - con = (Form_pg_constraint) GETSTRUCT(copytup); - con->conislocal = false; - CatalogTupleUpdate(conrel, ©tup->t_self, copytup); - ObjectAddressSet(conobj, ConstraintRelationId, con->oid); - - CommandCounterIncrement(); - table_close(conrel, RowExclusiveLock); - return conobj; - } - - /* Don't allow drop of inherited constraints */ - if (con->coninhcount > 0 && !recursing) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop inherited constraint \"%s\" of relation \"%s\"", - constrName, RelationGetRelationName(rel)))); - - /* - * See if we have a not-null constraint or a PRIMARY KEY. If so, we have - * more checks and actions below, so obtain the list of columns that are - * constrained by the constraint being dropped. - */ - if (con->contype == CONSTRAINT_NOTNULL) - { - AttrNumber colnum; - - colnum = extractNotNullColumn(constraintTup); - unconstrained_cols = list_make1_int(colnum); - colname = NameStr(TupleDescAttr(RelationGetDescr(rel), - colnum - 1)->attname); - } - else if (con->contype == CONSTRAINT_PRIMARY) - { - Datum adatum; - ArrayType *arr; - int numkeys; - bool isNull; - int16 *attnums; + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop inherited constraint \"%s\" of relation \"%s\"", + constrName, RelationGetRelationName(rel)))); - dropping_pk = true; + is_no_inherit_constraint = con->connoinherit; + contype = con->contype; - adatum = heap_getattr(constraintTup, Anum_pg_constraint_conkey, - RelationGetDescr(conrel), &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); + /* + * If it's a foreign-key constraint, we'd better lock the referenced + * table and check that that's not in use, just as we've already done + * for the constrained table (else we might, eg, be dropping a trigger + * that has unfired events). But we can/must skip that in the + * self-referential case. + */ + if (contype == CONSTRAINT_FOREIGN && + con->confrelid != RelationGetRelid(rel)) + { + Relation frel; - for (int i = 0; i < numkeys; i++) - unconstrained_cols = lappend_int(unconstrained_cols, attnums[i]); - } + /* Must match lock taken by RemoveTriggerById: */ + frel = table_open(con->confrelid, AccessExclusiveLock); + CheckTableNotInUse(frel, "ALTER TABLE"); + table_close(frel, NoLock); + } - is_no_inherit_constraint = con->connoinherit; + /* + * Perform the actual constraint deletion + */ + conobj.classId = ConstraintRelationId; + conobj.objectId = con->oid; + conobj.objectSubId = 0; - /* - * If it's a foreign-key constraint, we'd better lock the referenced table - * and check that that's not in use, just as we've already done for the - * constrained table (else we might, eg, be dropping a trigger that has - * unfired events). But we can/must skip that in the self-referential - * case. - */ - if (con->contype == CONSTRAINT_FOREIGN && - con->confrelid != RelationGetRelid(rel)) - { - Relation frel; + performDeletion(&conobj, behavior, 0); - /* Must match lock taken by RemoveTriggerById: */ - frel = table_open(con->confrelid, AccessExclusiveLock); - CheckTableNotInUse(frel, "ALTER TABLE"); - table_close(frel, NoLock); + found = true; } - /* - * Perform the actual constraint deletion - */ - ObjectAddressSet(conobj, ConstraintRelationId, con->oid); - performDeletion(&conobj, behavior, 0); + systable_endscan(scan); - /* - * 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 != NIL) + if (!found) { - Relation attrel; - Bitmapset *pkcols; - Bitmapset *ircols; - - /* Make implicit attnotnull changes visible */ - CommandCounterIncrement(); - - 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(rel, - INDEX_ATTR_BITMAP_PRIMARY_KEY); - ircols = RelationGetIndexAttrBitmap(rel, INDEX_ATTR_BITMAP_IDENTITY_KEY); - - foreach_int(attnum, unconstrained_cols) + if (!missing_ok) { - HeapTuple atttup; - HeapTuple contup; - Form_pg_attribute attForm; - char attidentity; - - /* - * Obtain pg_attribute tuple and verify conditions on it. - */ - 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: if none and this is a generated identity column or the - * replica identity, abort the whole thing. - */ - contup = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum); - if (contup || - bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber, - pkcols)) - continue; - - /* - * It's not valid to drop the not-null constraint for a GENERATED - * AS IDENTITY column. - */ - if (attidentity != '\0') - ereport(ERROR, - errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), - errmsg("column \"%s\" of relation \"%s\" is an identity column", - get_attname(RelationGetRelid(rel), attnum, - false), - RelationGetRelationName(rel))); - - /* - * It's not valid to drop the not-null constraint for a column in - * the replica identity index, either. (FULL is not affected.) - */ - 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))); + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("constraint \"%s\" of relation \"%s\" does not exist", + constrName, RelationGetRelationName(rel)))); + } + else + { + ereport(NOTICE, + (errmsg("constraint \"%s\" of relation \"%s\" does not exist, skipping", + constrName, RelationGetRelationName(rel)))); + table_close(conrel, RowExclusiveLock); + return; } - table_close(attrel, RowExclusiveLock); } /* - * For partitioned tables, non-CHECK, non-NOT-NULL inherited constraints - * are dropped via the dependency mechanism, so we're done here. + * For partitioned tables, non-CHECK inherited constraints are dropped via + * the dependency mechanism, so we're done here. */ - if (con->contype != CONSTRAINT_CHECK && - con->contype != CONSTRAINT_NOTNULL && + if (contype != CONSTRAINT_CHECK && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { table_close(conrel, RowExclusiveLock); - return conobj; + return; } /* @@ -13202,68 +12682,48 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha foreach_oid(childrelid, children) { Relation childrel; - HeapTuple tuple; - Form_pg_constraint childcon; - - if (list_member_oid(*readyRels, childrelid)) - continue; /* child already processed */ + HeapTuple copy_tuple; /* find_inheritance_children already got lock */ childrel = table_open(childrelid, NoLock); CheckTableNotInUse(childrel, "ALTER TABLE"); - /* - * We search for not-null constraints by column name, and others by - * constraint name. - */ - if (con->contype == CONSTRAINT_NOTNULL) - { - tuple = findNotNullConstraint(childrelid, colname); - if (!HeapTupleIsValid(tuple)) - elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u", - colname, RelationGetRelid(childrel)); - } - else - { - SysScanDesc scan; - ScanKeyData skey[3]; - - ScanKeyInit(&skey[0], - Anum_pg_constraint_conrelid, - BTEqualStrategyNumber, F_OIDEQ, - ObjectIdGetDatum(childrelid)); - ScanKeyInit(&skey[1], - Anum_pg_constraint_contypid, - BTEqualStrategyNumber, F_OIDEQ, - ObjectIdGetDatum(InvalidOid)); - ScanKeyInit(&skey[2], - Anum_pg_constraint_conname, - BTEqualStrategyNumber, F_NAMEEQ, - CStringGetDatum(constrName)); - scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, - true, NULL, 3, skey); - /* There can only be one, so no need to loop */ - tuple = systable_getnext(scan); - if (!HeapTupleIsValid(tuple)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("constraint \"%s\" of relation \"%s\" does not exist", - constrName, - RelationGetRelationName(childrel)))); - tuple = heap_copytuple(tuple); - systable_endscan(scan); - } + ScanKeyInit(&skey[0], + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(childrelid)); + ScanKeyInit(&skey[1], + Anum_pg_constraint_contypid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(InvalidOid)); + ScanKeyInit(&skey[2], + Anum_pg_constraint_conname, + BTEqualStrategyNumber, F_NAMEEQ, + CStringGetDatum(constrName)); + scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, + true, NULL, 3, skey); + + /* There can be at most one matching row */ + if (!HeapTupleIsValid(tuple = systable_getnext(scan))) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("constraint \"%s\" of relation \"%s\" does not exist", + constrName, + RelationGetRelationName(childrel)))); + + copy_tuple = heap_copytuple(tuple); - childcon = (Form_pg_constraint) GETSTRUCT(tuple); + systable_endscan(scan); - /* Right now only CHECK and not-null constraints can be inherited */ - if (childcon->contype != CONSTRAINT_CHECK && - childcon->contype != CONSTRAINT_NOTNULL) - elog(ERROR, "inherited constraint is not a CHECK or not-null constraint"); + con = (Form_pg_constraint) GETSTRUCT(copy_tuple); - if (childcon->coninhcount <= 0) /* shouldn't happen */ + /* Right now only CHECK constraints can be inherited */ + if (con->contype != CONSTRAINT_CHECK) + elog(ERROR, "inherited constraint is not a CHECK constraint"); + + if (con->coninhcount <= 0) /* shouldn't happen */ elog(ERROR, "relation %u has non-inherited constraint \"%s\"", - childrelid, NameStr(childcon->conname)); + childrelid, constrName); if (recurse) { @@ -13271,18 +12731,18 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha * If the child constraint has other definition sources, just * decrement its inheritance count; if not, recurse to delete it. */ - if (childcon->coninhcount == 1 && !childcon->conislocal) + if (con->coninhcount == 1 && !con->conislocal) { /* Time to delete this child constraint, too */ - dropconstraint_internal(childrel, tuple, behavior, - recurse, true, missing_ok, readyRels, - lockmode); + ATExecDropConstraint(childrel, constrName, behavior, + true, true, + false, lockmode); } else { /* Child constraint must survive my deletion */ - childcon->coninhcount--; - CatalogTupleUpdate(conrel, &tuple->t_self, tuple); + con->coninhcount--; + CatalogTupleUpdate(conrel, ©_tuple->t_self, copy_tuple); /* Make update visible */ CommandCounterIncrement(); @@ -13291,91 +12751,25 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha else { /* - * If we were told to drop ONLY in this table (no recursion) and - * there are no further parents for this constraint, we need to - * mark the inheritors' constraints as locally defined rather than - * inherited. + * If we were told to drop ONLY in this table (no recursion), we + * need to mark the inheritors' constraints as locally defined + * rather than inherited. */ - childcon->coninhcount--; - if (childcon->coninhcount == 0) - childcon->conislocal = true; + con->coninhcount--; + con->conislocal = true; - CatalogTupleUpdate(conrel, &tuple->t_self, tuple); + CatalogTupleUpdate(conrel, ©_tuple->t_self, copy_tuple); /* Make update visible */ CommandCounterIncrement(); } - heap_freetuple(tuple); + heap_freetuple(copy_tuple); table_close(childrel, NoLock); } - /* - * In addition, when dropping a primary key from a legacy-inheritance - * parent table, we must recurse to children to mark the corresponding NOT - * NULL constraint as no longer inherited, or drop it if this its last - * reference. - */ - if (con->contype == CONSTRAINT_PRIMARY && - rel->rd_rel->relkind == RELKIND_RELATION && - rel->rd_rel->relhassubclass) - { - List *colnames = NIL; - List *pkready = NIL; - - /* - * Because primary keys are always marked as NO INHERIT, we don't have - * a list of children yet, so obtain one now. - */ - children = find_inheritance_children(RelationGetRelid(rel), lockmode); - - /* - * Find out the list of column names to process. Fortunately, we - * already have the list of column numbers. - */ - foreach_int(attnum, unconstrained_cols) - { - colnames = lappend(colnames, get_attname(RelationGetRelid(rel), - attnum, false)); - } - - foreach_oid(childrelid, children) - { - Relation childrel; - - if (list_member_oid(pkready, childrelid)) - continue; /* child already processed */ - - /* find_inheritance_children already got lock */ - childrel = table_open(childrelid, NoLock); - CheckTableNotInUse(childrel, "ALTER TABLE"); - - foreach_ptr(char, colName, colnames) - { - HeapTuple contup; - - contup = findNotNullConstraint(childrelid, colName); - if (contup == NULL) - elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\", relation \"%s\"", - colName, RelationGetRelationName(childrel)); - - dropconstraint_internal(childrel, contup, - DROP_RESTRICT, true, true, - false, &pkready, - lockmode); - pkready = NIL; - } - - table_close(childrel, NoLock); - - pkready = lappend_oid(pkready, childrelid); - } - } - table_close(conrel, RowExclusiveLock); - - return conobj; } /* @@ -14479,10 +13873,9 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) /* * If the constraint is inherited (only), we don't want to inject a - * new definition here; it'll get recreated when - * ATAddCheckNNConstraint recurses from adding the parent table's - * constraint. But we had to carry the info this far so that we can - * drop the constraint below. + * new definition here; it'll get recreated when ATAddCheckConstraint + * recurses from adding the parent table's constraint. But we had to + * carry the info this far so that we can drop the constraint below. */ if (!conislocal) continue; @@ -14729,19 +14122,15 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd, NIL, con->conname); } - else if (cmd->subtype == AT_SetAttNotNull) + else if (cmd->subtype == AT_SetNotNull) { /* - * We see this subtype when a primary key is created - * internally, for example when it is replaced with a new - * constraint (say because one of the columns changes - * type); in this case we need to reinstate attnotnull, - * because it was removed because of the drop of the old - * PK. Schedule this subcommand to an upcoming AT pass. + * The parser will create AT_SetNotNull 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. */ - 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", @@ -16316,13 +15705,6 @@ ATExecAddInherit(Relation child_rel, RangeVar *parent, LOCKMODE lockmode) /* OK to create inheritance */ CreateInheritance(child_rel, parent_rel, false); - /* - * If parent_rel has a primary key, then child_rel has not-null - * constraints that make these columns as non nullable. Make those - * constraints as inherited. - */ - ATInheritAdjustNotNulls(parent_rel, child_rel, 1); - ObjectAddressSet(address, RelationRelationId, RelationGetRelid(parent_rel)); @@ -16501,24 +15883,14 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispart RelationGetRelationName(child_rel), parent_attname))); /* - * If the parent has a not-null constraint that's not NO INHERIT, - * make sure the child has one too. - * - * Other constraints are checked elsewhere. + * Check child doesn't discard NOT NULL property. (Other + * constraints are checked elsewhere.) */ if (parent_att->attnotnull && !child_att->attnotnull) - { - HeapTuple contup; - - contup = findNotNullConstraintAttnum(RelationGetRelid(parent_rel), - parent_att->attnum); - if (HeapTupleIsValid(contup) && - !((Form_pg_constraint) GETSTRUCT(contup))->connoinherit) - ereport(ERROR, - errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("column \"%s\" in child table must be marked NOT NULL", - parent_attname)); - } + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" in child table must be marked NOT NULL", + parent_attname))); /* * Child column must be generated if and only if parent column is. @@ -16619,8 +15991,7 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) HeapTuple child_tuple; bool found = false; - if (parent_con->contype != CONSTRAINT_CHECK && - parent_con->contype != CONSTRAINT_NOTNULL) + if (parent_con->contype != CONSTRAINT_CHECK) continue; /* if the parent's constraint is marked NO INHERIT, it's not inherited */ @@ -16640,50 +16011,21 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) Form_pg_constraint child_con = (Form_pg_constraint) GETSTRUCT(child_tuple); HeapTuple child_copy; - if (child_con->contype != parent_con->contype) + if (child_con->contype != CONSTRAINT_CHECK) continue; - /* - * CHECK constraint are matched by name, NOT NULL ones by - * attribute number - */ - if (child_con->contype == CONSTRAINT_CHECK) - { - if (strcmp(NameStr(parent_con->conname), - NameStr(child_con->conname)) != 0) - continue; - } - else if (child_con->contype == CONSTRAINT_NOTNULL) - { - AttrNumber parent_attno = extractNotNullColumn(parent_tuple); - AttrNumber child_attno = extractNotNullColumn(child_tuple); - - if (strcmp(get_attname(parent_relid, parent_attno, false), - get_attname(RelationGetRelid(child_rel), child_attno, - false)) != 0) - continue; - } + if (strcmp(NameStr(parent_con->conname), + NameStr(child_con->conname)) != 0) + continue; - if (child_con->contype == CONSTRAINT_CHECK && - !constraints_equivalent(parent_tuple, child_tuple, RelationGetDescr(constraintrel))) + if (!constraints_equivalent(parent_tuple, child_tuple, RelationGetDescr(constraintrel))) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("child table \"%s\" has different definition for check constraint \"%s\"", RelationGetRelationName(child_rel), NameStr(parent_con->conname)))); - /* - * If the CHECK child constraint is "no inherit" then cannot - * merge. - * - * This is not desirable for not-null constraints, mostly because - * it breaks our pg_upgrade strategy, but it also makes sense on - * its own: if a child has its own not-null constraint and then - * acquires a parent with the same constraint, then we start to - * enforce that constraint for all the descendants of that child - * too, if any. - */ - if (child_con->contype == CONSTRAINT_CHECK && - child_con->connoinherit) + /* If the child constraint is "no inherit" then cannot merge */ + if (child_con->connoinherit) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg("constraint \"%s\" conflicts with non-inherited constraint on child table \"%s\"", @@ -16710,27 +16052,6 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) ereport(ERROR, errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), errmsg("too many inheritance parents")); - if (child_con->contype == CONSTRAINT_NOTNULL && - child_con->connoinherit) - { - /* - * If the child has children, it's not possible to turn a NO - * INHERIT constraint into an inheritable one: we would need - * to recurse to create constraints in those children, but - * this is not a good place to do that. - */ - if (child_rel->rd_rel->relhassubclass) - ereport(ERROR, - errmsg("cannot add NOT NULL constraint to column \"%s\" of relation \"%s\" with inheritance children", - get_attname(RelationGetRelid(child_rel), - extractNotNullColumn(child_tuple), - false), - RelationGetRelationName(child_rel)), - errdetail("Existing constraint \"%s\" is marked NO INHERIT.", - NameStr(child_con->conname))); - - child_con->connoinherit = false; - } /* * In case of partitions, an inherited constraint must be @@ -16753,20 +16074,10 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) systable_endscan(child_scan); if (!found) - { - if (parent_con->contype == CONSTRAINT_NOTNULL) - ereport(ERROR, - errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("column \"%s\" in child table must be marked NOT NULL", - get_attname(parent_relid, - extractNotNullColumn(parent_tuple), - false))); - ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("child table is missing constraint \"%s\"", NameStr(parent_con->conname)))); - } } systable_endscan(parent_scan); @@ -16804,18 +16115,6 @@ ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) /* Off to RemoveInheritance() where most of the work happens */ RemoveInheritance(rel, parent_rel, false); - /* - * If parent_rel has a primary key, then child_rel has not-null - * constraints that make these columns as non nullable. Mark those - * constraints as no longer inherited by this parent. - */ - ATInheritAdjustNotNulls(parent_rel, rel, -1); - - /* - * If the parent has a primary key, then we decrement counts for all NOT - * NULL constraints - */ - ObjectAddressSet(address, RelationRelationId, RelationGetRelid(parent_rel)); @@ -16924,7 +16223,6 @@ RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached) HeapTuple attributeTuple, constraintTuple; List *connames; - List *nncolumns; bool found; bool is_partitioning; @@ -16993,8 +16291,6 @@ RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached) * this, we first need a list of the names of the parent's check * constraints. (We cheat a bit by only checking for name matches, * assuming that the expressions will match.) - * - * For NOT NULL columns, we store column numbers to match. */ catalogRelation = table_open(ConstraintRelationId, RowExclusiveLock); ScanKeyInit(&key[0], @@ -17005,7 +16301,6 @@ RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached) true, NULL, 1, key); connames = NIL; - nncolumns = NIL; while (HeapTupleIsValid(constraintTuple = systable_getnext(scan))) { @@ -17013,8 +16308,6 @@ RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached) if (con->contype == CONSTRAINT_CHECK) connames = lappend(connames, pstrdup(NameStr(con->conname))); - if (con->contype == CONSTRAINT_NOTNULL) - nncolumns = lappend_int(nncolumns, extractNotNullColumn(constraintTuple)); } systable_endscan(scan); @@ -17030,40 +16323,20 @@ RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached) while (HeapTupleIsValid(constraintTuple = systable_getnext(scan))) { Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(constraintTuple); - bool match = false; - ListCell *lc; + bool match; - /* - * Match CHECK constraints by name, not-null constraints by column - * number, and ignore all others. - */ - if (con->contype == CONSTRAINT_CHECK) - { - foreach(lc, connames) - { - if (con->contype == CONSTRAINT_CHECK && - strcmp(NameStr(con->conname), (char *) lfirst(lc)) == 0) - { - match = true; - break; - } - } - } - else if (con->contype == CONSTRAINT_NOTNULL) - { - AttrNumber child_attno = extractNotNullColumn(constraintTuple); + if (con->contype != CONSTRAINT_CHECK) + continue; - foreach(lc, nncolumns) + match = false; + foreach_ptr(char, chkname, connames) + { + if (strcmp(NameStr(con->conname), chkname) == 0) { - if (lfirst_int(lc) == child_attno) - { - match = true; - break; - } + match = true; + break; } } - else - continue; if (match) { @@ -17102,54 +16375,6 @@ RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached) RelationGetRelid(parent_rel), false); } -/* - * Adjust coninhcount of not-null constraints upwards or downwards when a - * table is marked as inheriting or no longer doing so a table with a primary - * key. - * - * Note: these constraints are not dropped, even if their inhcount goes to zero - * and conislocal is false. Instead we mark the constraints as locally defined. - * This is seen as more useful behavior, with no downsides. The user can always - * drop them afterwards. - */ -static void -ATInheritAdjustNotNulls(Relation parent_rel, Relation child_rel, int inhcount) -{ - Bitmapset *pkattnos; - - /* Quick exit when parent has no PK */ - if (!parent_rel->rd_rel->relhasindex) - return; - - pkattnos = RelationGetIndexAttrBitmap(parent_rel, - INDEX_ATTR_BITMAP_PRIMARY_KEY); - if (pkattnos != NULL) - { - Bitmapset *childattnums = NULL; - AttrMap *attmap; - int i; - - attmap = build_attrmap_by_name(RelationGetDescr(parent_rel), - RelationGetDescr(child_rel), true); - - i = -1; - while ((i = bms_next_member(pkattnos, i)) >= 0) - { - childattnums = bms_add_member(childattnums, - attmap->attnums[i + FirstLowInvalidHeapAttributeNumber - 1]); - } - - /* - * CCI is needed in case there's a NOT NULL PRIMARY KEY column in the - * parent: the relevant not-null constraint in the child already had - * its inhcount modified earlier. - */ - CommandCounterIncrement(); - AdjustNotNullInheritance(RelationGetRelid(child_rel), childattnums, - inhcount); - } -} - /* * Drop the dependency created by StoreCatalogInheritance1 (CREATE TABLE * INHERITS/ALTER TABLE INHERIT -- refclassid will be RelationRelationId) or @@ -19557,10 +18782,9 @@ AttachPartitionEnsureIndexes(List **wqueue, Relation rel, Relation attachrel) attachInfos = palloc(sizeof(IndexInfo *) * list_length(attachRelIdxs)); /* Build arrays of all existing indexes and their IndexInfos */ - foreach(cell, attachRelIdxs) + foreach_oid(cldIdxId, attachRelIdxs) { - Oid cldIdxId = lfirst_oid(cell); - int i = foreach_current_index(cell); + int i = foreach_current_index(cldIdxId); attachrelIdxRels[i] = index_open(cldIdxId, AccessShareLock); attachInfos[i] = BuildIndexInfo(attachrelIdxRels[i]); @@ -19694,28 +18918,6 @@ AttachPartitionEnsureIndexes(List **wqueue, Relation rel, Relation attachrel) stmt = generateClonedIndexStmt(NULL, idxRel, attmap, &conOid); - - /* - * If the index is a primary key, mark all columns as NOT NULL if - * they aren't already. - */ - if (stmt->primary) - { - MemoryContextSwitchTo(oldcxt); - for (int j = 0; j < info->ii_NumIndexKeyAttrs; j++) - { - AttrNumber childattno; - - childattno = get_attnum(RelationGetRelid(attachrel), - get_attname(RelationGetRelid(rel), - info->ii_IndexAttrNumbers[j], - false)); - set_attnotnull(wqueue, attachrel, childattno, - true, AccessExclusiveLock); - } - MemoryContextSwitchTo(cxt); - } - DefineIndex(RelationGetRelid(attachrel), stmt, InvalidOid, RelationGetRelid(idxRel), conOid, @@ -20338,7 +19540,7 @@ ATExecDetachPartitionFinalize(Relation rel, RangeVar *name) * DetachAddConstraintIfNeeded * Subroutine for ATExecDetachPartition. Create a constraint that * takes the place of the partition constraint, but avoid creating - * a dupe if a constraint already exists which implies the needed + * a dupe if an constraint already exists which implies the needed * constraint. */ static void @@ -20371,8 +19573,8 @@ DetachAddConstraintIfNeeded(List **wqueue, Relation partRel) n->initially_valid = true; n->skip_validation = true; /* It's a re-add, since it nominally already exists */ - ATAddCheckNNConstraint(wqueue, tab, partRel, n, - true, false, true, ShareUpdateExclusiveLock); + ATAddCheckConstraint(wqueue, tab, partRel, n, + true, false, true, ShareUpdateExclusiveLock); } } @@ -20641,13 +19843,6 @@ ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, RangeVar *name) RelationGetRelationName(partIdx)))); } - /* - * If it's a primary key, make sure the columns in the partition are - * NOT NULL. - */ - if (parentIdx->rd_index->indisprimary) - verifyPartitionIndexNotNull(childInfo, partTbl); - /* All good -- do it */ IndexSetParentIndex(partIdx, RelationGetRelid(parentIdx)); if (OidIsValid(constraintOid)) @@ -20791,29 +19986,6 @@ validatePartitionedIndex(Relation partedIdx, Relation partedTbl) } } -/* - * When attaching an index as a partition of a partitioned index which is a - * primary key, verify that all the columns in the partition are marked NOT - * NULL. - */ -static void -verifyPartitionIndexNotNull(IndexInfo *iinfo, Relation partition) -{ - for (int i = 0; i < iinfo->ii_NumIndexKeyAttrs; i++) - { - Form_pg_attribute att = TupleDescAttr(RelationGetDescr(partition), - iinfo->ii_IndexAttrNumbers[i] - 1); - - if (!att->attnotnull) - ereport(ERROR, - errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("invalid primary key definition"), - errdetail("Column \"%s\" of relation \"%s\" is not marked NOT NULL.", - NameStr(att->attname), - RelationGetRelationName(partition))); - } -} - /* * Return an OID list of constraints that reference the given relation * that are marked as having a parent constraints. diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 775c3e26cd..26f8de7713 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -1680,8 +1680,6 @@ relation_excluded_by_constraints(PlannerInfo *root, * Currently, attnotnull constraints must be treated as NO INHERIT unless * this is a partitioned table. In future we might track their * inheritance status more accurately, allowing this to be refined. - * - * XXX do we need/want to change this? */ include_notnull = (!rte->inh || rte->relkind == RELKIND_PARTITIONED_TABLE); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index e8b619926e..18a0a2dc2b 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -3953,15 +3953,12 @@ ColConstraint: * or be part of a_expr NOT LIKE or similar constructs). */ ColConstraintElem: - NOT NULL_P opt_no_inherit + NOT NULL_P { Constraint *n = makeNode(Constraint); n->contype = CONSTR_NOTNULL; n->location = @1; - n->is_no_inherit = $3; - n->skip_validation = false; - n->initially_valid = true; $$ = (Node *) n; } | NULL_P @@ -4198,20 +4195,6 @@ ConstraintElem: n->initially_valid = !n->skip_validation; $$ = (Node *) n; } - | NOT NULL_P ColId ConstraintAttributeSpec - { - Constraint *n = makeNode(Constraint); - - n->contype = CONSTR_NOTNULL; - n->location = @1; - n->keys = list_make1(makeString($3)); - /* no NOT VALID support yet */ - processCASbits($4, @4, "NOT NULL", - NULL, NULL, NULL, - &n->is_no_inherit, yyscanner); - n->initially_valid = true; - $$ = (Node *) n; - } | UNIQUE opt_unique_null_treatment '(' columnList opt_without_overlaps ')' opt_c_include opt_definition OptConsTableSpace ConstraintAttributeSpec { diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 0598e897d9..b692d25152 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -84,7 +84,6 @@ typedef struct bool isalter; /* true if altering existing table */ List *columns; /* ColumnDef items */ List *ckconstraints; /* CHECK constraints */ - List *nnconstraints; /* NOT NULL constraints */ List *fkconstraints; /* FOREIGN KEY constraints */ List *ixconstraints; /* index-creating constraints */ List *likeclauses; /* LIKE clauses that need post-processing */ @@ -244,7 +243,6 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) cxt.isalter = false; cxt.columns = NIL; cxt.ckconstraints = NIL; - cxt.nnconstraints = NIL; cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; cxt.likeclauses = NIL; @@ -351,7 +349,6 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) */ stmt->tableElts = cxt.columns; stmt->constraints = cxt.ckconstraints; - stmt->nnconstraints = cxt.nnconstraints; result = lappend(cxt.blist, stmt); result = list_concat(result, cxt.alist); @@ -550,7 +547,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) bool saw_default; bool saw_identity; bool saw_generated; - bool need_notnull = false; ListCell *clist; cxt->columns = lappend(cxt->columns, column); @@ -648,8 +644,10 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) constraint->cooked_expr = NULL; column->constraints = lappend(column->constraints, constraint); - /* have a not-null constraint added later */ - need_notnull = true; + constraint = makeNode(Constraint); + constraint->contype = CONSTR_NOTNULL; + constraint->location = -1; + column->constraints = lappend(column->constraints, constraint); } /* Process column constraints, if any... */ @@ -667,7 +665,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) switch (constraint->contype) { case CONSTR_NULL: - if ((saw_nullable && column->is_not_null) || need_notnull) + if (saw_nullable && column->is_not_null) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\"", @@ -679,14 +677,6 @@ 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 - */ if (saw_nullable && !column->is_not_null) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -694,25 +684,8 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) column->colname, cxt->relation->relname), parser_errposition(cxt->pstate, constraint->location))); - /* Ignore redundant NOT NULL markings */ - - /* - * If this is the first time we see this column being marked - * not null, add the constraint entry; and get rid of any - * previous markings to mark the column NOT NULL. - */ - if (!column->is_not_null) - { - column->is_not_null = true; - saw_nullable = true; - - constraint->keys = list_make1(makeString(column->colname)); - cxt->nnconstraints = lappend(cxt->nnconstraints, constraint); - - /* Don't need this anymore, if we had it */ - need_notnull = false; - } - + column->is_not_null = true; + saw_nullable = true; break; case CONSTR_DEFAULT: @@ -762,19 +735,16 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) column->identity = constraint->generated_when; saw_identity = true; - /* - * Identity columns are always NOT NULL, but we may have a - * constraint already. - */ - if (!saw_nullable) - need_notnull = true; - else if (!column->is_not_null) + /* An identity column is implicitly NOT NULL */ + if (saw_nullable && !column->is_not_null) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\"", column->colname, cxt->relation->relname), parser_errposition(cxt->pstate, constraint->location))); + column->is_not_null = true; + saw_nullable = true; break; } @@ -880,29 +850,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) constraint->location))); } - /* - * If we need a not-null constraint for SERIAL or IDENTITY, and one was - * not explicitly specified, add one now. - */ - if (need_notnull && !(saw_nullable && column->is_not_null)) - { - Constraint *notnull; - - column->is_not_null = true; - - notnull = makeNode(Constraint); - notnull->contype = CONSTR_NOTNULL; - notnull->conname = NULL; - notnull->deferrable = false; - notnull->initdeferred = false; - notnull->location = -1; - notnull->keys = list_make1(makeString(column->colname)); - notnull->skip_validation = false; - notnull->initially_valid = true; - - cxt->nnconstraints = lappend(cxt->nnconstraints, notnull); - } - /* * If needed, generate ALTER FOREIGN TABLE ALTER COLUMN statement to add * per-column foreign data wrapper options to this column after creation. @@ -972,16 +919,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) cxt->ckconstraints = lappend(cxt->ckconstraints, 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; - case CONSTR_FOREIGN: if (cxt->isforeign) ereport(ERROR, @@ -993,6 +930,7 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) break; case CONSTR_NULL: + case CONSTR_NOTNULL: case CONSTR_DEFAULT: case CONSTR_ATTR_DEFERRABLE: case CONSTR_ATTR_NOT_DEFERRABLE: @@ -1028,7 +966,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla AclResult aclresult; char *comment; ParseCallbackState pcbstate; - bool process_notnull_constraints = false; setup_parser_errposition_callback(&pcbstate, cxt->pstate, table_like_clause->relation->location); @@ -1097,18 +1034,14 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla continue; /* - * Create a new column definition + * Create a new column, which is marked as NOT inherited. + * + * For constraints, ONLY the not-null constraint is inherited by the + * new column definition per SQL99. */ def = makeColumnDef(NameStr(attribute->attname), attribute->atttypid, attribute->atttypmod, attribute->attcollation); - - /* - * For constraints, ONLY the not-null constraint is inherited by the - * new column definition per SQL99; however we cannot do that - * correctly here, so we leave it for expandTableLikeClause to handle. - */ - if (attribute->attnotnull) - process_notnull_constraints = true; + def->is_not_null = attribute->attnotnull; /* * Add to column list @@ -1182,77 +1115,19 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla * we don't yet know what column numbers the copied columns will have in * the finished table. If any of those options are specified, add the * LIKE clause to cxt->likeclauses so that expandTableLikeClause will be - * called after we do know that; in addition, do that if there are any NOT - * NULL constraints, because those must be propagated even if not - * explicitly requested. - * - * In order for this to work, we remember the relation OID so that + * called after we do know that. Also, remember the relation OID so that * expandTableLikeClause is certain to open the same table. */ - if ((table_like_clause->options & - (CREATE_TABLE_LIKE_DEFAULTS | - CREATE_TABLE_LIKE_GENERATED | - CREATE_TABLE_LIKE_CONSTRAINTS | - CREATE_TABLE_LIKE_INDEXES)) || - process_notnull_constraints) + if (table_like_clause->options & + (CREATE_TABLE_LIKE_DEFAULTS | + CREATE_TABLE_LIKE_GENERATED | + CREATE_TABLE_LIKE_CONSTRAINTS | + CREATE_TABLE_LIKE_INDEXES)) { table_like_clause->relationOid = RelationGetRelid(relation); cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause); } - /* - * If INCLUDING INDEXES is not given and a primary key exists, we need to - * add not-null constraints to the columns covered by the PK (except those - * that already have one.) This is required for backwards compatibility. - */ - if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) == 0) - { - Bitmapset *pkcols; - int x = -1; - Bitmapset *donecols = NULL; - ListCell *lc; - - /* - * Obtain a bitmapset of columns on which we'll add not-null - * constraints in expandTableLikeClause, so that we skip this for - * those. - */ - foreach(lc, RelationGetNotNullConstraints(RelationGetRelid(relation), true)) - { - CookedConstraint *cooked = (CookedConstraint *) lfirst(lc); - - donecols = bms_add_member(donecols, cooked->attnum); - } - - pkcols = RelationGetIndexAttrBitmap(relation, - INDEX_ATTR_BITMAP_PRIMARY_KEY); - while ((x = bms_next_member(pkcols, x)) >= 0) - { - Constraint *notnull; - AttrNumber attnum = x + FirstLowInvalidHeapAttributeNumber; - Form_pg_attribute attForm; - - /* ignore if we already have one for this column */ - if (bms_is_member(attnum, donecols)) - continue; - - attForm = TupleDescAttr(tupleDesc, attnum - 1); - - notnull = makeNode(Constraint); - notnull->contype = CONSTR_NOTNULL; - notnull->conname = NULL; - notnull->is_no_inherit = false; - notnull->deferrable = false; - notnull->initdeferred = false; - notnull->location = -1; - notnull->keys = list_make1(makeString(pstrdup(NameStr(attForm->attname)))); - notnull->skip_validation = false; - notnull->initially_valid = true; - - cxt->nnconstraints = lappend(cxt->nnconstraints, notnull); - } - } - /* * We may copy extended statistics if requested, since the representation * of CreateStatsStmt doesn't depend on column numbers. @@ -1319,8 +1194,6 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) TupleConstr *constr; AttrMap *attmap; char *comment; - bool at_pushed = false; - ListCell *lc; /* * Open the relation referenced by the LIKE clause. We should still have @@ -1491,20 +1364,6 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) } } - /* - * Copy not-null constraints, too (these do not require any option to have - * been given). - */ - foreach(lc, RelationGetNotNullConstraints(RelationGetRelid(relation), false)) - { - AlterTableCmd *atsubcmd; - - atsubcmd = makeNode(AlterTableCmd); - atsubcmd->subtype = AT_AddConstraint; - atsubcmd->def = (Node *) lfirst_node(Constraint, lc); - atsubcmds = lappend(atsubcmds, atsubcmd); - } - /* * If we generated any ALTER TABLE actions above, wrap them into a single * ALTER TABLE command. Stick it at the front of the result, so it runs @@ -1519,8 +1378,6 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) atcmd->objtype = OBJECT_TABLE; atcmd->missing_ok = false; result = lcons(atcmd, result); - - at_pushed = true; } /* @@ -1548,39 +1405,6 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) attmap, NULL); - /* - * The PK columns might not yet non-nullable, so make sure they - * become so. - */ - if (index_stmt->primary) - { - foreach(lc, index_stmt->indexParams) - { - IndexElem *col = lfirst_node(IndexElem, lc); - AlterTableCmd *notnullcmd = makeNode(AlterTableCmd); - - notnullcmd->subtype = AT_SetAttNotNull; - notnullcmd->name = pstrdup(col->name); - /* Luckily we can still add more AT-subcmds here */ - atsubcmds = lappend(atsubcmds, notnullcmd); - } - - /* - * If we had already put the AlterTableStmt into the output - * list, we don't need to do so again; otherwise do it. - */ - if (!at_pushed) - { - AlterTableStmt *atcmd = makeNode(AlterTableStmt); - - atcmd->relation = copyObject(heapRel); - atcmd->cmds = atsubcmds; - atcmd->objtype = OBJECT_TABLE; - atcmd->missing_ok = false; - result = lcons(atcmd, result); - } - } - /* Copy comment on index, if requested */ if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS) { @@ -1661,8 +1485,8 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename) * with the index there. * * Unlike transformIndexConstraint, we don't make any effort to force primary - * key columns to be not-null. The larger cloning process this is part of - * should have cloned their not-null status separately (and DefineIndex will + * key columns to be NOT NULL. The larger cloning process this is part of + * should have cloned their NOT NULL status separately (and DefineIndex will * complain if that fails to happen). */ IndexStmt * @@ -2210,12 +2034,10 @@ transformIndexConstraints(CreateStmtContext *cxt) ListCell *lc; /* - * Run through the constraints that need to generate an index, and do so. - * - * For PRIMARY KEY, in addition we set each column's attnotnull flag true. - * We do not create a separate not-null constraint, as that would be - * redundant: the PRIMARY KEY constraint itself fulfills that role. Other - * constraint types don't need any not-null markings. + * Run through the constraints that need to generate an index. For PRIMARY + * KEY, mark each column as NOT NULL and create an index. For UNIQUE or + * EXCLUDE, create an index as for PRIMARY KEY, but do not insist on NOT + * NULL. */ foreach(lc, cxt->ixconstraints) { @@ -2289,7 +2111,9 @@ transformIndexConstraints(CreateStmtContext *cxt) } /* - * Now append all the IndexStmts to cxt->alist. + * Now append all the IndexStmts to cxt->alist. If we generated an ALTER + * TABLE SET NOT NULL statement to support a primary key, it's already in + * cxt->alist. */ cxt->alist = list_concat(cxt->alist, finalindexlist); } @@ -2297,10 +2121,12 @@ transformIndexConstraints(CreateStmtContext *cxt) /* * transformIndexConstraint * Transform one UNIQUE, PRIMARY KEY, or EXCLUDE constraint for - * transformIndexConstraints. An IndexStmt is returned. + * transformIndexConstraints. * - * For a PRIMARY KEY constraint, we additionally force the columns to be - * marked as not-null, without producing a not-null constraint. + * We return an IndexStmt. For a PRIMARY KEY constraint, we additionally + * produce not-null constraints, either by marking ColumnDefs in cxt->columns + * as is_not_null or by adding an ALTER TABLE SET NOT NULL command to + * cxt->alist. */ static IndexStmt * transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) @@ -2564,7 +2390,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) * For UNIQUE and PRIMARY KEY, we just have a list of column names. * * Make sure referenced keys exist. If we are making a PRIMARY KEY index, - * also make sure they are not-null. + * also make sure they are NOT NULL. */ else { @@ -2572,6 +2398,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) { char *key = strVal(lfirst(lc)); bool found = false; + bool forced_not_null = false; ColumnDef *column = NULL; ListCell *columns; IndexElem *iparam; @@ -2592,14 +2419,13 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) * column is defined in the new table. For PRIMARY KEY, we * can apply the not-null constraint cheaply here ... unless * the column is marked is_from_type, in which case marking it - * here would be ineffective (see MergeAttributes). Note that - * this isn't effective in ALTER TABLE either, unless the - * column is being added in the same command. + * here would be ineffective (see MergeAttributes). */ if (constraint->contype == CONSTR_PRIMARY && !column->is_from_type) { column->is_not_null = true; + forced_not_null = true; } } else if (SystemAttributeByName(key) != NULL) @@ -2607,7 +2433,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) /* * column will be a system column in the new table, so accept * it. System columns can't ever be null, so no need to worry - * about PRIMARY/NOT NULL constraint. + * about PRIMARY/not-null constraint. */ found = true; } @@ -2642,6 +2468,14 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) if (strcmp(key, inhname) == 0) { found = true; + + /* + * It's tempting to set forced_not_null if the + * parent column is already NOT NULL, but that + * seems unsafe because the column's NOT NULL + * marking might disappear between now and + * execution. Do the runtime check to be safe. + */ break; } } @@ -2695,11 +2529,15 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) iparam->nulls_ordering = SORTBY_NULLS_DEFAULT; index->indexParams = lappend(index->indexParams, iparam); - if (constraint->contype == CONSTR_PRIMARY) + /* + * For a primary-key column, also create an item for ALTER TABLE + * SET NOT NULL if we couldn't ensure it via is_not_null above. + */ + if (constraint->contype == CONSTR_PRIMARY && !forced_not_null) { AlterTableCmd *notnullcmd = makeNode(AlterTableCmd); - notnullcmd->subtype = AT_SetAttNotNull; + notnullcmd->subtype = AT_SetNotNull; notnullcmd->name = pstrdup(key); notnullcmds = lappend(notnullcmds, notnullcmd); } @@ -3642,7 +3480,6 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, cxt.isalter = true; cxt.columns = NIL; cxt.ckconstraints = NIL; - cxt.nnconstraints = NIL; cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; cxt.likeclauses = NIL; @@ -3912,8 +3749,8 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, /* * We assume here that cxt.alist contains only IndexStmts and possibly - * AT_SetAttNotNull statements generated from primary key constraints. - * We absorb the subcommands of the latter directly. + * ALTER TABLE SET NOT NULL statements generated from primary key + * constraints. We absorb the subcommands of the latter directly. */ if (IsA(istmt, IndexStmt)) { @@ -3936,7 +3773,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, } cxt.alist = NIL; - /* Append any CHECK, NOT NULL or FK constraints to the commands list */ + /* Append any CHECK or FK constraints to the commands list */ foreach(l, cxt.ckconstraints) { newcmd = makeNode(AlterTableCmd); @@ -3944,13 +3781,6 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, newcmd->def = (Node *) lfirst_node(Constraint, l); newcmds = lappend(newcmds, newcmd); } - foreach(l, cxt.nnconstraints) - { - newcmd = makeNode(AlterTableCmd); - newcmd->subtype = AT_AddConstraint; - newcmd->def = (Node *) lfirst_node(Constraint, l); - newcmds = lappend(newcmds, newcmd); - } foreach(l, cxt.fkconstraints) { newcmd = makeNode(AlterTableCmd); diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 302cd8e7f3..9a6d372414 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2506,28 +2506,6 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, conForm->connoinherit ? " NO INHERIT" : ""); break; } - case CONSTRAINT_NOTNULL: - { - if (conForm->conrelid) - { - AttrNumber attnum; - - attnum = extractNotNullColumn(tup); - - appendStringInfo(&buf, "NOT NULL %s", - quote_identifier(get_attname(conForm->conrelid, - attnum, false))); - if (((Form_pg_constraint) GETSTRUCT(tup))->connoinherit) - appendStringInfoString(&buf, " NO INHERIT"); - } - else if (conForm->contypid) - { - /* conkey is null for domain not-null constraints */ - appendStringInfoString(&buf, "NOT NULL"); - } - break; - } - case CONSTRAINT_TRIGGER: /* diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 262c9878dd..e6072cbdd9 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4810,46 +4810,18 @@ RelationGetIndexList(Relation relation) result = lappend_oid(result, index->indexrelid); /* - * Non-unique or predicate indexes aren't interesting for either oid - * indexes or replication identity indexes, so don't check them. - * Deferred ones are not useful for replication identity either; but - * we do include them if they are PKs. + * Invalid, non-unique, non-immediate or predicate indexes aren't + * interesting for either oid indexes or replication identity indexes, + * so don't check them. */ - if (!index->indisunique || + if (!index->indisvalid || !index->indisunique || + !index->indimmediate || !heap_attisnull(htup, Anum_pg_index_indpred, NULL)) continue; - /* - * Remember primary key index, if any. We do this only if the index - * is valid; but if the table is partitioned, then we do it even if - * it's invalid. - * - * The reason for returning invalid primary keys for foreign tables is - * because of pg_dump of NOT NULL constraints, and the fact that PKs - * remain marked invalid until the partitions' PKs are attached to it. - * If we make rd_pkindex invalid, then the attnotnull flag is reset - * after the PK is created, which causes the ALTER INDEX ATTACH - * PARTITION to fail with 'column ... is not marked NOT NULL'. With - * this, dropconstraint_internal() will believe that the columns must - * not have attnotnull reset, so the PKs-on-partitions can be attached - * correctly, until finally the PK-on-parent is marked valid. - * - * Also, this doesn't harm anything, because rd_pkindex is not a - * "real" index anyway, but a RELKIND_PARTITIONED_INDEX. - */ - if (index->indisprimary && - (index->indisvalid || - relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)) - { + /* remember primary key index if any */ + if (index->indisprimary) pkeyIndex = index->indexrelid; - pkdeferrable = !index->indimmediate; - } - - if (!index->indimmediate) - continue; - - if (!index->indisvalid) - continue; /* remember explicitly chosen replica index */ if (index->indisreplident) diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index ba53c66098..64e7dc89f1 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -85,8 +85,7 @@ static catalogid_hash *catalogIdHash = NULL; static void flagInhTables(Archive *fout, TableInfo *tblinfo, int numTables, InhInfo *inhinfo, int numInherits); static void flagInhIndexes(Archive *fout, TableInfo *tblinfo, int numTables); -static void flagInhAttrs(Archive *fout, DumpOptions *dopt, TableInfo *tblinfo, - int numTables); +static void flagInhAttrs(Archive *fout, TableInfo *tblinfo, int numTables); static int strInArray(const char *pattern, char **arr, int arr_size); static IndxInfo *findIndexByOid(Oid oid); @@ -230,7 +229,7 @@ getSchemaData(Archive *fout, int *numTablesPtr) getTableAttrs(fout, tblinfo, numTables); pg_log_info("flagging inherited columns in subtables"); - flagInhAttrs(fout, fout->dopt, tblinfo, numTables); + flagInhAttrs(fout, tblinfo, numTables); pg_log_info("reading partitioning data"); getPartitioningInfo(fout); @@ -478,8 +477,7 @@ flagInhIndexes(Archive *fout, TableInfo tblinfo[], int numTables) * What we need to do here is: * * - Detect child columns that inherit NOT NULL bits from their parents, so - * that we needn't specify that again for the child. (Versions >= 17 no - * longer need this.) + * that we needn't specify that again for the child. * * - Detect child columns that have DEFAULT NULL when their parents had some * non-null default. In this case, we make up a dummy AttrDefInfo object so @@ -499,8 +497,9 @@ flagInhIndexes(Archive *fout, TableInfo tblinfo[], int numTables) * modifies tblinfo */ static void -flagInhAttrs(Archive *fout, DumpOptions *dopt, TableInfo *tblinfo, int numTables) +flagInhAttrs(Archive *fout, TableInfo *tblinfo, int numTables) { + DumpOptions *dopt = fout->dopt; int i, j, k; @@ -562,8 +561,7 @@ flagInhAttrs(Archive *fout, DumpOptions *dopt, TableInfo *tblinfo, int numTables { AttrDefInfo *parentDef = parent->attrdefs[inhAttrInd]; - foundNotNull |= (parent->notnull_constrs[inhAttrInd] != NULL && - !parent->notnull_noinh[inhAttrInd]); + foundNotNull |= parent->notnull[inhAttrInd]; foundDefault |= (parentDef != NULL && strcmp(parentDef->adef_expr, "NULL") != 0 && !parent->attgenerated[inhAttrInd]); @@ -581,9 +579,8 @@ flagInhAttrs(Archive *fout, DumpOptions *dopt, TableInfo *tblinfo, int numTables } } - /* In versions < 17, remember if we found inherited NOT NULL */ - if (fout->remoteVersion < 170000) - tbinfo->notnull_inh[j] = foundNotNull; + /* Remember if we found inherited NOT NULL */ + tbinfo->inhNotNull[j] = foundNotNull; /* * Manufacture a DEFAULT NULL clause if necessary. This breaks diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 5f005a2f14..ac920f64c7 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -8702,10 +8702,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) int i_attlen; int i_attalign; int i_attislocal; - int i_notnull_name; - int i_notnull_noinherit; - int i_notnull_is_pk; - int i_notnull_inh; + int i_attnotnull; int i_attoptions; int i_attcollation; int i_attcompression; @@ -8715,13 +8712,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) /* * We want to perform just one query against pg_attribute, and then just - * one against pg_attrdef (for DEFAULTs) and two against pg_constraint - * (for CHECK constraints and for NOT NULL constraints). However, we - * mustn't try to select every row of those catalogs and then sort it out - * on the client side, because some of the server-side functions we need - * would be unsafe to apply to tables we don't have lock on. Hence, we - * build an array of the OIDs of tables we care about (and now have lock - * on!), and use a WHERE clause to constrain which rows are selected. + * one against pg_attrdef (for DEFAULTs) and one against pg_constraint + * (for CHECK constraints). However, we mustn't try to select every row + * of those catalogs and then sort it out on the client side, because some + * of the server-side functions we need would be unsafe to apply to tables + * we don't have lock on. Hence, we build an array of the OIDs of tables + * we care about (and now have lock on!), and use a WHERE clause to + * constrain which rows are selected. */ appendPQExpBufferChar(tbloids, '{'); appendPQExpBufferChar(checkoids, '{'); @@ -8768,6 +8765,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) "a.attstattarget,\n" "a.attstorage,\n" "t.typstorage,\n" + "a.attnotnull,\n" "a.atthasdef,\n" "a.attisdropped,\n" "a.attlen,\n" @@ -8784,48 +8782,6 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) "ORDER BY option_name" "), E',\n ') AS attfdwoptions,\n"); - /* - * 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 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 - * no explicit constraint, to avoid the table having to be scanned for - * 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, - "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" - "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" - "false AS notnull_noinherit,\n" - "copk.conname IS NOT NULL AS notnull_is_pk,\n" - "NOT a.attislocal AS notnull_inh,\n"); - if (fout->remoteVersion >= 140000) appendPQExpBufferStr(q, "a.attcompression AS attcompression,\n"); @@ -8860,29 +8816,11 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) "FROM unnest('%s'::pg_catalog.oid[]) AS src(tbloid)\n" "JOIN pg_catalog.pg_attribute a ON (src.tbloid = a.attrelid) " "LEFT JOIN pg_catalog.pg_type t " - "ON (a.atttypid = t.oid)\n", + "ON (a.atttypid = t.oid)\n" + "WHERE a.attnum > 0::pg_catalog.int2\n" + "ORDER BY a.attrelid, a.attnum", tbloids->data); - /* - * In versions 17 and up, we need pg_constraint for explicit NOT NULL - * entries. Also, we need to know if the NOT NULL for each column is - * backing a primary key. - */ - if (fout->remoteVersion >= 170000) - appendPQExpBufferStr(q, - " LEFT JOIN pg_catalog.pg_constraint co ON " - "(a.attrelid = co.conrelid\n" - " AND co.contype = 'n' AND " - "co.conkey = array[a.attnum])\n"); - - appendPQExpBufferStr(q, - "LEFT JOIN pg_catalog.pg_constraint copk ON " - "(copk.conrelid = src.tbloid\n" - " AND copk.contype = 'p' AND " - "copk.conkey @> array[a.attnum])\n" - "WHERE a.attnum > 0::pg_catalog.int2\n" - "ORDER BY a.attrelid, a.attnum"); - res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK); ntups = PQntuples(res); @@ -8900,10 +8838,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) i_attlen = PQfnumber(res, "attlen"); i_attalign = PQfnumber(res, "attalign"); i_attislocal = PQfnumber(res, "attislocal"); - i_notnull_name = PQfnumber(res, "notnull_name"); - i_notnull_noinherit = PQfnumber(res, "notnull_noinherit"); - i_notnull_is_pk = PQfnumber(res, "notnull_is_pk"); - i_notnull_inh = PQfnumber(res, "notnull_inh"); + i_attnotnull = PQfnumber(res, "attnotnull"); i_attoptions = PQfnumber(res, "attoptions"); i_attcollation = PQfnumber(res, "attcollation"); i_attcompression = PQfnumber(res, "attcompression"); @@ -8926,7 +8861,6 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) TableInfo *tbinfo = NULL; int numatts; bool hasdefaults; - int notnullcount; /* Count rows for this table */ for (numatts = 1; numatts < ntups - r; numatts++) @@ -8951,8 +8885,6 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) pg_fatal("unexpected column data for table \"%s\"", tbinfo->dobj.name); - notnullcount = 0; - /* Save data for this table */ tbinfo->numatts = numatts; tbinfo->attnames = (char **) pg_malloc(numatts * sizeof(char *)); @@ -8971,19 +8903,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attcompression = (char *) pg_malloc(numatts * sizeof(char)); tbinfo->attfdwoptions = (char **) pg_malloc(numatts * sizeof(char *)); tbinfo->attmissingval = (char **) pg_malloc(numatts * sizeof(char *)); - tbinfo->notnull_constrs = (char **) pg_malloc(numatts * sizeof(char *)); - tbinfo->notnull_noinh = (bool *) pg_malloc(numatts * sizeof(bool)); - tbinfo->notnull_throwaway = (bool *) pg_malloc(numatts * sizeof(bool)); - tbinfo->notnull_inh = (bool *) pg_malloc(numatts * sizeof(bool)); + tbinfo->notnull = (bool *) pg_malloc(numatts * sizeof(bool)); + tbinfo->inhNotNull = (bool *) pg_malloc(numatts * sizeof(bool)); tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(numatts * sizeof(AttrDefInfo *)); hasdefaults = false; for (int j = 0; j < numatts; j++, r++) { - bool use_named_notnull = false; - bool use_unnamed_notnull = false; - bool use_throwaway_notnull = false; - if (j + 1 != atoi(PQgetvalue(res, r, i_attnum))) pg_fatal("invalid column numbering in table \"%s\"", tbinfo->dobj.name); @@ -9002,144 +8928,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attlen[j] = atoi(PQgetvalue(res, r, i_attlen)); tbinfo->attalign[j] = *(PQgetvalue(res, r, i_attalign)); tbinfo->attislocal[j] = (PQgetvalue(res, r, i_attislocal)[0] == 't'); - - /* - * Not-null constraints require a jumping through a few hoops. - * First, if the user has specified a constraint name that's not - * the system-assigned default name, then we need to preserve - * that. But if they haven't, then we don't want to use the - * verbose syntax in the dump output. (Also, in versions prior to - * 17, there was no constraint name at all.) - * - * (XXX Comparing the name this way to a supposed default name is - * a bit of a hack, but it beats having to store a boolean flag in - * pg_constraint just for this, or having to compute the knowledge - * at pg_dump time from the server.) - * - * We also need to know if a column is part of the primary key. In - * that case, we want to mark the column as not-null at table - * creation time, so that the table doesn't have to be scanned to - * check for nulls when the PK is created afterwards; this is - * especially critical during pg_upgrade (where the data would not - * be scanned at all otherwise.) If the column is part of the PK - * and does not have any other not-null constraint, then we - * fabricate a throwaway constraint name that we later use to - * remove the constraint after the PK has been created. - * - * For inheritance child tables, we don't want to print not-null - * when the constraint was defined at the parent level instead of - * locally. - */ - - /* - * We use notnull_inh to suppress unwanted not-null constraints in - * inheritance children, when said constraints come from the - * parent(s). - */ - tbinfo->notnull_inh[j] = PQgetvalue(res, r, i_notnull_inh)[0] == 't'; - - if (fout->remoteVersion < 170000) - { - if (!PQgetisnull(res, r, i_notnull_name) && - dopt->binary_upgrade && - !tbinfo->ispartition && - tbinfo->notnull_inh[j]) - { - use_named_notnull = true; - /* XXX should match ChooseConstraintName better */ - tbinfo->notnull_constrs[j] = - psprintf("%s_%s_not_null", tbinfo->dobj.name, - tbinfo->attnames[j]); - } - else if (PQgetvalue(res, r, i_notnull_is_pk)[0] == 't') - { - /* - * 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; - } - else - { - if (!PQgetisnull(res, r, i_notnull_name)) - { - /* - * In binary upgrade of inheritance child tables, must - * have a constraint name that we can UPDATE later. - */ - if (dopt->binary_upgrade && - !tbinfo->ispartition && - tbinfo->notnull_inh[j]) - { - use_named_notnull = true; - tbinfo->notnull_constrs[j] = - pstrdup(PQgetvalue(res, r, i_notnull_name)); - - } - else - { - char *default_name; - - /* XXX should match ChooseConstraintName better */ - default_name = psprintf("%s_%s_not_null", tbinfo->dobj.name, - tbinfo->attnames[j]); - if (strcmp(default_name, - PQgetvalue(res, r, i_notnull_name)) == 0) - use_unnamed_notnull = true; - else - { - use_named_notnull = true; - tbinfo->notnull_constrs[j] = - pstrdup(PQgetvalue(res, r, i_notnull_name)); - } - } - } - else if (PQgetvalue(res, r, i_notnull_is_pk)[0] == 't') - { - /* see above */ - if (tbinfo->relkind != RELKIND_PARTITIONED_TABLE) - use_throwaway_notnull = true; - } - } - - if (use_unnamed_notnull) - { - tbinfo->notnull_constrs[j] = ""; - tbinfo->notnull_throwaway[j] = false; - } - else if (use_named_notnull) - { - /* The name itself has already been determined */ - tbinfo->notnull_throwaway[j] = false; - } - else if (use_throwaway_notnull) - { - /* - * Give this constraint a throwaway name. - */ - tbinfo->notnull_constrs[j] = - psprintf("pgdump_throwaway_notnull_%d", notnullcount++); - tbinfo->notnull_throwaway[j] = true; - tbinfo->notnull_inh[j] = false; - } - else - { - tbinfo->notnull_constrs[j] = NULL; - tbinfo->notnull_throwaway[j] = false; - } - - /* - * Throwaway constraints must always be NO INHERIT; otherwise do - * what the catalog says. - */ - tbinfo->notnull_noinh[j] = use_throwaway_notnull || - PQgetvalue(res, r, i_notnull_noinherit)[0] == 't'; - + tbinfo->notnull[j] = (PQgetvalue(res, r, i_attnotnull)[0] == 't'); tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, r, i_attoptions)); tbinfo->attcollation[j] = atooid(PQgetvalue(res, r, i_attcollation)); tbinfo->attcompression[j] = *(PQgetvalue(res, r, i_attcompression)); @@ -9148,6 +8937,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attrdefs[j] = NULL; /* fix below */ if (PQgetvalue(res, r, i_atthasdef)[0] == 't') hasdefaults = true; + /* these flags will be set in flagInhAttrs() */ + tbinfo->inhNotNull[j] = false; } if (hasdefaults) @@ -16166,14 +15957,13 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) !tbinfo->attrdefs[j]->separate); /* - * Not Null constraint --- suppress unless it is locally - * defined, except if partition, or in binary-upgrade case - * where that won't work. + * Not Null constraint --- suppress if inherited, except + * if partition, or in binary-upgrade case where that + * won't work. */ - print_notnull = - (tbinfo->notnull_constrs[j] != NULL && - (!tbinfo->notnull_inh[j] || tbinfo->ispartition || - dopt->binary_upgrade)); + print_notnull = (tbinfo->notnull[j] && + (!tbinfo->inhNotNull[j] || + tbinfo->ispartition || dopt->binary_upgrade)); /* * Skip column if fully defined by reloftype, except in @@ -16231,16 +16021,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) if (print_notnull) - { - if (tbinfo->notnull_constrs[j][0] == '\0') - appendPQExpBufferStr(q, " NOT NULL"); - else - appendPQExpBuffer(q, " CONSTRAINT %s NOT NULL", - fmtId(tbinfo->notnull_constrs[j])); - - if (tbinfo->notnull_noinh[j]) - appendPQExpBufferStr(q, " NO INHERIT"); - } + appendPQExpBufferStr(q, " NOT NULL"); /* Add collation if not default for the type */ if (OidIsValid(tbinfo->attcollation[j])) @@ -16453,25 +16234,6 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) appendPQExpBufferStr(q, "\n AND attrelid = "); appendStringLiteralAH(q, qualrelname, fout); appendPQExpBufferStr(q, "::pg_catalog.regclass;\n"); - - /* - * If a not-null constraint comes from inheritance, reset - * conislocal. The inhcount is fixed later. - */ - if (tbinfo->notnull_constrs[j] != NULL && - !tbinfo->notnull_throwaway[j] && - tbinfo->notnull_inh[j] && - !tbinfo->ispartition) - { - appendPQExpBufferStr(q, "UPDATE pg_catalog.pg_constraint\n" - "SET conislocal = false\n" - "WHERE contype = 'n' AND conrelid = "); - appendStringLiteralAH(q, qualrelname, fout); - appendPQExpBufferStr(q, "::pg_catalog.regclass AND\n" - "conname = "); - appendStringLiteralAH(q, tbinfo->notnull_constrs[j], fout); - appendPQExpBufferStr(q, ";\n"); - } } } @@ -16593,22 +16355,11 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) * we have to mark it separately. */ if (!shouldPrintColumn(dopt, tbinfo, j) && - tbinfo->notnull_constrs[j] != NULL && - (!tbinfo->notnull_inh[j] && !tbinfo->ispartition && !dopt->binary_upgrade)) - { - /* No constraint name desired? */ - if (tbinfo->notnull_constrs[j][0] == '\0') - appendPQExpBuffer(q, - "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET NOT NULL;\n", - foreign, qualrelname, - fmtId(tbinfo->attnames[j])); - else - appendPQExpBuffer(q, - "ALTER %sTABLE ONLY %s ADD CONSTRAINT %s NOT NULL %s;\n", - foreign, qualrelname, - tbinfo->notnull_constrs[j], - fmtId(tbinfo->attnames[j])); - } + tbinfo->notnull[j] && !tbinfo->inhNotNull[j]) + appendPQExpBuffer(q, + "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET NOT NULL;\n", + foreign, qualrelname, + fmtId(tbinfo->attnames[j])); /* * Dump per-column statistics information. We only issue an ALTER @@ -17352,19 +17103,6 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo) * similar code in dumpIndex! */ - /* - * Drop any not-null constraints that were added to support the PK, - * but leave them alone if they have a definition coming from their - * parent. - */ - if (coninfo->contype == 'p') - for (int i = 0; i < tbinfo->numatts; i++) - if (tbinfo->notnull_throwaway[i] && - !tbinfo->notnull_inh[i]) - appendPQExpBuffer(q, "\nALTER TABLE ONLY %s DROP CONSTRAINT %s;", - fmtQualifiedDumpable(tbinfo), - tbinfo->notnull_constrs[i]); - /* If the index is clustered, we need to record that. */ if (indxinfo->indisclustered) { diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 2a7c5873a0..f518a1e6d2 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -346,13 +346,8 @@ typedef struct _tableInfo char *attcompression; /* per-attribute compression method */ char **attfdwoptions; /* per-attribute fdw options */ char **attmissingval; /* per attribute missing value */ - char **notnull_constrs; /* NOT NULL constraint names. If null, - * there isn't one on this column. If - * empty string, unnamed constraint - * (pre-v17) */ - bool *notnull_noinh; /* NOT NULL is NO INHERIT */ - bool *notnull_throwaway; /* drop the NOT NULL constraint later */ - bool *notnull_inh; /* true if NOT NULL has no local definition */ + bool *notnull; /* not-null constraints on attributes */ + bool *inhNotNull; /* true if NOT NULL is inherited */ struct _attrDefInfo **attrdefs; /* DEFAULT expressions */ struct _constraintInfo *checkexprs; /* CHECK constraints */ bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */ diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 7085053a2d..770139153f 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -3242,7 +3242,7 @@ my %tests = ( );', regexp => qr/^ \QCREATE TABLE dump_test.fk_reference_test_table (\E - \n\s+\Qcol1 integer CONSTRAINT \E[a-z0-9_]*\Q NOT NULL NO INHERIT\E + \n\s+\Qcol1 integer NOT NULL\E \n\); /xm, like => @@ -3340,8 +3340,8 @@ my %tests = ( FOR VALUES FROM (\'2006-02-01\') TO (\'2006-03-01\');', regexp => qr/^ \QCREATE TABLE dump_test_second_schema.measurement_y2006m2 (\E\n - \s+\Qcity_id integer DEFAULT nextval('dump_test.measurement_city_id_seq'::regclass) CONSTRAINT measurement_city_id_not_null NOT NULL,\E\n - \s+\Qlogdate date CONSTRAINT measurement_logdate_not_null NOT NULL,\E\n + \s+\Qcity_id integer DEFAULT nextval('dump_test.measurement_city_id_seq'::regclass) NOT NULL,\E\n + \s+\Qlogdate date NOT NULL,\E\n \s+\Qpeaktemp integer,\E\n \s+\Qunitsales integer DEFAULT 0,\E\n \s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer)),\E\n @@ -3635,7 +3635,7 @@ my %tests = ( );', regexp => qr/^ \QCREATE TABLE dump_test.test_table_generated (\E\n - \s+\Qcol1 integer CONSTRAINT \E[a-z0-9_]*\Q NOT NULL NO INHERIT,\E\n + \s+\Qcol1 integer NOT NULL,\E\n \s+\Qcol2 integer GENERATED ALWAYS AS ((col1 * 2)) STORED\E\n \); /xms, @@ -3749,7 +3749,7 @@ my %tests = ( ) INHERITS (dump_test.test_inheritance_parent);', regexp => qr/^ \QCREATE TABLE dump_test.test_inheritance_child (\E\n - \s+\Qcol1 integer NOT NULL,\E\n + \s+\Qcol1 integer,\E\n \s+\QCONSTRAINT test_inheritance_child CHECK ((col2 >= 142857))\E\n \)\n \QINHERITS (dump_test.test_inheritance_parent);\E\n diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 4a9ee4a54d..3af44acef1 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3058,50 +3058,6 @@ describeOneTableDetails(const char *schemaname, } PQclear(result); } - - /* If verbose, print NOT NULL constraints */ - if (verbose) - { - printfPQExpBuffer(&buf, - "SELECT co.conname, at.attname, co.connoinherit, co.conislocal,\n" - "co.coninhcount <> 0\n" - "FROM pg_catalog.pg_constraint co JOIN\n" - "pg_catalog.pg_attribute at ON\n" - "(at.attnum = co.conkey[1])\n" - "WHERE co.contype = 'n' AND\n" - "co.conrelid = '%s'::pg_catalog.regclass AND\n" - "at.attrelid = '%s'::pg_catalog.regclass\n" - "ORDER BY at.attnum", - oid, - oid); - - result = PSQLexec(buf.data); - if (!result) - goto error_return; - else - tuples = PQntuples(result); - - if (tuples > 0) - printTableAddFooter(&cont, _("Not-null constraints:")); - - /* Might be an empty set - that's ok */ - for (i = 0; i < tuples; i++) - { - bool islocal = PQgetvalue(result, i, 3)[0] == 't'; - bool inherited = PQgetvalue(result, i, 4)[0] == 't'; - - printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s", - PQgetvalue(result, i, 0), - PQgetvalue(result, i, 1), - PQgetvalue(result, i, 2)[0] == 't' ? - " NO INHERIT" : - islocal && inherited ? _(" (local, inherited)") : - inherited ? _(" (inherited)") : ""); - - printTableAddFooter(&cont, buf.data); - } - PQclear(result); - } } /* Get view_def if table is a view or materialized view */ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 8793a12a4d..4fb6087377 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202405061 +#define CATALOG_VERSION_NO 202405131 #endif diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index e446d49b3e..c512824cd1 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -34,11 +34,10 @@ typedef struct RawColumnDefault typedef struct CookedConstraint { - ConstrType contype; /* CONSTR_DEFAULT, CONSTR_CHECK, - * CONSTR_NOTNULL */ + ConstrType contype; /* CONSTR_DEFAULT or CONSTR_CHECK */ Oid conoid; /* constr OID if created, otherwise Invalid */ char *name; /* name, or NULL if none */ - AttrNumber attnum; /* which attr (only for NOTNULL, DEFAULT) */ + AttrNumber attnum; /* which attr (only for DEFAULT) */ Node *expr; /* transformed default or check expr */ bool skip_validation; /* skip validation? (only for CHECK) */ bool is_local; /* constraint has local (non-inherited) def */ @@ -114,9 +113,6 @@ extern List *AddRelationNewConstraints(Relation rel, bool is_local, bool is_internal, const char *queryString); -extern List *AddRelationNotNullConstraints(Relation rel, - List *constraints, - List *old_notnulls); extern void RelationClearMissing(Relation rel); extern void SetAttrMissing(Oid relid, char *attname, char *value); diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index 68bf55fdf7..115217a616 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -257,14 +257,7 @@ extern char *ChooseConstraintName(const char *name1, const char *name2, const char *label, Oid namespaceid, List *others); -extern HeapTuple findNotNullConstraintAttnum(Oid relid, AttrNumber attnum); -extern HeapTuple findNotNullConstraint(Oid relid, const char *colname); extern HeapTuple findDomainNotNullConstraint(Oid typid); -extern AttrNumber extractNotNullColumn(HeapTuple constrTup); -extern int AdjustNotNullInheritance1(Oid relid, AttrNumber attnum, int count, - bool is_no_inherit, bool allow_noinherit_change); -extern void AdjustNotNullInheritance(Oid relid, Bitmapset *columns, int count); -extern List *RelationGetNotNullConstraints(Oid relid, bool cooked); extern void RemoveConstraintById(Oid conId); extern void RenameConstraintById(Oid conId, const char *newname); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 3ca06fc3af..dcfd080dd5 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2356,9 +2356,9 @@ typedef enum AlterTableType AT_CookedColumnDefault, /* add a pre-cooked column default */ AT_DropNotNull, /* alter column drop not null */ AT_SetNotNull, /* alter column set not null */ - AT_SetAttNotNull, /* set attnotnull w/o a constraint */ AT_SetExpression, /* alter column set expression */ AT_DropExpression, /* alter column drop expression */ + AT_CheckNotNull, /* check column is already marked not null */ AT_SetStatistics, /* alter column set statistics */ AT_SetOptions, /* alter column set ( options ) */ AT_ResetOptions, /* alter column reset ( options ) */ @@ -2643,10 +2643,10 @@ typedef struct VariableShowStmt * Create Table Statement * * NOTE: in the raw gram.y output, ColumnDef and Constraint nodes are - * intermixed in tableElts, and constraints and nnconstraints are NIL. After - * parse analysis, tableElts contains just ColumnDefs, nnconstraints contains - * Constraint nodes of CONSTR_NOTNULL type from various sources, and - * constraints contains just CONSTR_CHECK Constraint nodes. + * intermixed in tableElts, and constraints is NIL. After parse analysis, + * tableElts contains just ColumnDefs, and constraints contains just + * Constraint nodes (in fact, only CONSTR_CHECK nodes, in the present + * implementation). * ---------------------- */ @@ -2661,7 +2661,6 @@ typedef struct CreateStmt PartitionSpec *partspec; /* PARTITION BY clause */ TypeName *ofTypename; /* OF typename */ List *constraints; /* constraints (list of Constraint nodes) */ - List *nnconstraints; /* NOT NULL constraints (ditto) */ List *options; /* options from WITH clause */ OnCommitAction oncommit; /* what do we do at COMMIT? */ char *tablespacename; /* table space to use, or NULL */ diff --git a/src/test/modules/test_ddl_deparse/expected/alter_table.out b/src/test/modules/test_ddl_deparse/expected/alter_table.out index b5e71af9aa..6daa186a84 100644 --- a/src/test/modules/test_ddl_deparse/expected/alter_table.out +++ b/src/test/modules/test_ddl_deparse/expected/alter_table.out @@ -28,7 +28,6 @@ ALTER TABLE parent ADD COLUMN b serial; NOTICE: DDL test: type simple, tag CREATE SEQUENCE NOTICE: DDL test: type alter table, tag ALTER TABLE NOTICE: subcommand: type ADD COLUMN (and recurse) desc column b of table parent -NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint parent_b_not_null on table parent NOTICE: DDL test: type simple, tag ALTER SEQUENCE ALTER TABLE parent RENAME COLUMN b TO c; NOTICE: DDL test: type simple, tag ALTER TABLE @@ -58,18 +57,24 @@ NOTICE: subcommand: type DETACH PARTITION desc table part2 DROP TABLE part2; ALTER TABLE part ADD PRIMARY KEY (a); NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc column a of table part -NOTICE: subcommand: type SET ATTNOTNULL desc column a of table part1 +NOTICE: subcommand: type SET NOT NULL desc column a of table part +NOTICE: subcommand: type SET NOT NULL desc column a of table part1 NOTICE: subcommand: type ADD INDEX desc index part_pkey ALTER TABLE parent ALTER COLUMN a SET NOT NULL; NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET NOT NULL (and recurse) desc constraint parent_a_not_null on table parent +NOTICE: subcommand: type SET NOT NULL desc column a of table parent +NOTICE: subcommand: type SET NOT NULL desc column a of table child +NOTICE: subcommand: type SET NOT NULL desc column a of table grandchild ALTER TABLE parent ALTER COLUMN a DROP NOT NULL; NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type DROP NOT NULL (and recurse) desc column a of table parent +NOTICE: subcommand: type DROP NOT NULL desc column a of table parent +NOTICE: subcommand: type DROP NOT NULL desc column a of table child +NOTICE: subcommand: type DROP NOT NULL desc column a of table grandchild ALTER TABLE parent ALTER COLUMN a SET NOT NULL; NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET NOT NULL (and recurse) desc constraint parent_a_not_null on table parent +NOTICE: subcommand: type SET NOT NULL desc column a of table parent +NOTICE: subcommand: type SET NOT NULL desc column a of table child +NOTICE: subcommand: type SET NOT NULL desc column a of table grandchild ALTER TABLE parent ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; NOTICE: DDL test: type simple, tag CREATE SEQUENCE NOTICE: DDL test: type simple, tag ALTER SEQUENCE @@ -111,7 +116,6 @@ NOTICE: DDL test: type alter table, tag ALTER TABLE NOTICE: subcommand: type ALTER COLUMN SET TYPE desc column c of table parent NOTICE: subcommand: type ALTER COLUMN SET TYPE desc column c of table child NOTICE: subcommand: type ALTER COLUMN SET TYPE desc column c of table grandchild -NOTICE: subcommand: type (re) ADD CONSTRAINT desc constraint parent_b_not_null on table parent NOTICE: subcommand: type (re) ADD STATS desc statistics object parent_stat ALTER TABLE parent ALTER COLUMN c SET DEFAULT 0; NOTICE: DDL test: type alter table, tag ALTER TABLE diff --git a/src/test/modules/test_ddl_deparse/expected/create_table.out b/src/test/modules/test_ddl_deparse/expected/create_table.out index 75b62aff4d..2178ce83e9 100644 --- a/src/test/modules/test_ddl_deparse/expected/create_table.out +++ b/src/test/modules/test_ddl_deparse/expected/create_table.out @@ -54,8 +54,6 @@ NOTICE: DDL test: type simple, tag CREATE SEQUENCE NOTICE: DDL test: type simple, tag CREATE SEQUENCE NOTICE: DDL test: type simple, tag CREATE SEQUENCE NOTICE: DDL test: type simple, tag CREATE TABLE -NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc NOTICE: DDL test: type simple, tag CREATE INDEX NOTICE: DDL test: type simple, tag CREATE INDEX NOTICE: DDL test: type simple, tag ALTER SEQUENCE @@ -76,8 +74,6 @@ CREATE TABLE IF NOT EXISTS fkey_table ( EXCLUDE USING btree (check_col_2 WITH =) ); NOTICE: DDL test: type simple, tag CREATE TABLE -NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc NOTICE: DDL test: type simple, tag CREATE INDEX NOTICE: DDL test: type simple, tag CREATE INDEX NOTICE: DDL test: type alter table, tag ALTER TABLE @@ -90,7 +86,7 @@ CREATE TABLE employees OF employee_type ( ); NOTICE: DDL test: type simple, tag CREATE TABLE NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc column name of table employees +NOTICE: subcommand: type SET NOT NULL desc column name of table employees NOTICE: DDL test: type simple, tag CREATE INDEX -- Inheritance CREATE TABLE person ( @@ -100,8 +96,6 @@ CREATE TABLE person ( location point ); NOTICE: DDL test: type simple, tag CREATE TABLE -NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc NOTICE: DDL test: type simple, tag CREATE INDEX CREATE TABLE emp ( salary int4, @@ -134,10 +128,6 @@ CREATE TABLE like_datatype_table ( EXCLUDING ALL ); NOTICE: DDL test: type simple, tag CREATE TABLE -NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint datatype_table_id_big_not_null on table like_datatype_table -NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint datatype_table_id_not_null on table like_datatype_table -NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint datatype_table_is_small_not_null on table like_datatype_table CREATE TABLE like_fkey_table ( LIKE fkey_table INCLUDING DEFAULTS @@ -146,13 +136,7 @@ CREATE TABLE like_fkey_table ( ); NOTICE: DDL test: type simple, tag CREATE TABLE NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc column id of table like_fkey_table NOTICE: subcommand: type ALTER COLUMN SET DEFAULT (precooked) desc column id of table like_fkey_table -NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_big_id_not_null on table like_fkey_table -NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_check_col_1_not_null on table like_fkey_table -NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_check_col_2_not_null on table like_fkey_table -NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_datatype_id_not_null on table like_fkey_table -NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_id_not_null on table like_fkey_table NOTICE: DDL test: type simple, tag CREATE INDEX NOTICE: DDL test: type simple, tag CREATE INDEX -- Volatile table types @@ -160,29 +144,21 @@ CREATE UNLOGGED TABLE unlogged_table ( id INT PRIMARY KEY ); NOTICE: DDL test: type simple, tag CREATE TABLE -NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc NOTICE: DDL test: type simple, tag CREATE INDEX CREATE TEMP TABLE temp_table ( id INT PRIMARY KEY ); NOTICE: DDL test: type simple, tag CREATE TABLE -NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc NOTICE: DDL test: type simple, tag CREATE INDEX CREATE TEMP TABLE temp_table_commit_delete ( id INT PRIMARY KEY ) ON COMMIT DELETE ROWS; NOTICE: DDL test: type simple, tag CREATE TABLE -NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc NOTICE: DDL test: type simple, tag CREATE INDEX CREATE TEMP TABLE temp_table_commit_drop ( id INT PRIMARY KEY ) ON COMMIT DROP; NOTICE: DDL test: type simple, tag CREATE TABLE -NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET ATTNOTNULL desc NOTICE: DDL test: type simple, tag CREATE INDEX diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c index 265ef2a547..67ff2b6367 100644 --- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c +++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c @@ -129,15 +129,15 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS) case AT_SetNotNull: strtype = "SET NOT NULL"; break; - case AT_SetAttNotNull: - strtype = "SET ATTNOTNULL"; - break; case AT_SetExpression: strtype = "SET EXPRESSION"; break; case AT_DropExpression: strtype = "DROP EXPRESSION"; break; + case AT_CheckNotNull: + strtype = "CHECK NOT NULL"; + break; case AT_SetStatistics: strtype = "SET STATS"; break; diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 7666c76238..673361e840 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -1127,6 +1127,7 @@ Indexes: "atacc1_pkey" PRIMARY KEY, btree (test) alter table atacc1 alter column test drop not null; +ERROR: column "test" is in a primary key \d atacc1 Table "public.atacc1" Column | Type | Collation | Nullable | Default @@ -1136,6 +1137,7 @@ Indexes: "atacc1_pkey" PRIMARY KEY, btree (test) alter table atacc1 drop constraint "atacc1_pkey"; +alter table atacc1 alter column test drop not null; \d atacc1 Table "public.atacc1" Column | Type | Collation | Nullable | Default @@ -1214,6 +1216,20 @@ alter table only parent alter a set not null; ERROR: column "a" of relation "parent" contains null values alter table child alter a set not null; ERROR: column "a" of relation "child" contains null values +delete from parent; +alter table only parent alter a set not null; +insert into parent values (NULL); +ERROR: null value in column "a" of relation "parent" violates not-null constraint +DETAIL: Failing row contains (null). +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); +ERROR: null value in column "a" of relation "child" violates not-null constraint +DETAIL: Failing row contains (null, foo). +delete from child; +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); +ERROR: null value in column "a" of relation "child" violates not-null constraint +DETAIL: Failing row contains (null, foo). drop table child; drop table parent; -- test setting and removing default values @@ -3844,9 +3860,6 @@ CREATE TABLE atnotnull1 (); ALTER TABLE atnotnull1 ADD COLUMN a INT, ALTER a SET NOT NULL; -ALTER TABLE atnotnull1 - ADD COLUMN b INT, - ADD NOT NULL b; ALTER TABLE atnotnull1 ADD COLUMN c INT, ADD PRIMARY KEY (c); @@ -3855,13 +3868,9 @@ ALTER TABLE atnotnull1 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- a | integer | | not null | | plain | | - b | integer | | not null | | plain | | c | integer | | not null | | plain | | Indexes: "atnotnull1_pkey" PRIMARY KEY, btree (c) -Not-null constraints: - "atnotnull1_a_not_null" NOT NULL "a" - "atnotnull1_b_not_null" NOT NULL "b" -- cannot drop column that is part of the partition key CREATE TABLE partitioned ( @@ -4380,6 +4389,7 @@ ERROR: cannot alter inherited column "b" -- partitions exist ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL; ERROR: constraint must be added to child tables too +DETAIL: Column "b" of relation "part_2" is not already NOT NULL. HINT: Do not specify the ONLY keyword. ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz'); ERROR: constraint must be added to child tables too diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out index 4d40a6809a..a13aafff0b 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -247,12 +247,11 @@ ERROR: insert or update on table "clstr_tst" violates foreign key constraint "c DETAIL: Key (b)=(1111) is not present in table "clstr_tst_s". SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass ORDER BY 1; - conname ----------------------- - clstr_tst_a_not_null + conname +---------------- clstr_tst_con clstr_tst_pkey -(3 rows) +(2 rows) SELECT relname, relkind, EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index ec7c9e53d0..e6f6602d95 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -288,100 +288,6 @@ ERROR: new row for relation "atacc1" violates check constraint "atacc1_test2_ch DETAIL: Failing row contains (null, 3). DROP TABLE ATACC1 CASCADE; NOTICE: drop cascades to table atacc2 --- NOT NULL NO INHERIT -CREATE TABLE ATACC1 (a int, not null a no inherit); -CREATE TABLE ATACC2 () INHERITS (ATACC1); -\d+ ATACC2 - Table "public.atacc2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -Inherits: atacc1 - -DROP TABLE ATACC1, ATACC2; -CREATE TABLE ATACC1 (a int); -ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT; -CREATE TABLE ATACC2 () INHERITS (ATACC1); -\d+ ATACC2 - Table "public.atacc2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -Inherits: atacc1 - -DROP TABLE ATACC1, ATACC2; -CREATE TABLE ATACC1 (a int); -CREATE TABLE ATACC2 () INHERITS (ATACC1); -ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT; -\d+ ATACC2 - Table "public.atacc2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -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); -CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); -NOTICE: merging column "a" with inherited definition -INSERT INTO ATACC3 VALUES (null); -- make sure we scan atacc3 -ALTER TABLE ATACC2 INHERIT ATACC1; -ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; -ERROR: column "a" of relation "atacc3" contains null values -DELETE FROM ATACC3; -ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; -\d+ ATACC[123] - Table "public.atacc1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "ditto" NOT NULL "a" -Child tables: atacc2 - - Table "public.atacc2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "a_is_not_null" NOT NULL "a" (local, inherited) -Inherits: atacc1 -Child tables: atacc3 - - Table "public.atacc3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "ditto" NOT NULL "a" (inherited) -Inherits: atacc2 - -ALTER TABLE ATACC2 DROP CONSTRAINT a_is_not_null; -ALTER TABLE ATACC1 DROP CONSTRAINT ditto; -\d+ ATACC3 - Table "public.atacc3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -Inherits: atacc2 - -DROP TABLE ATACC1, ATACC2, ATACC3; --- The same cannot be achieved this way -CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); -CREATE TABLE ATACC1 (a int, CONSTRAINT ditto NOT NULL a); -CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); -NOTICE: merging column "a" with inherited definition -ALTER TABLE ATACC2 INHERIT ATACC1; -ERROR: cannot add NOT NULL constraint to column "a" of relation "atacc2" with inheritance children -DETAIL: Existing constraint "a_is_not_null" is marked NO INHERIT. -DROP TABLE ATACC1, ATACC2, ATACC3; -- -- Check constraints on INSERT INTO -- @@ -848,430 +754,6 @@ ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =); ERROR: could not create exclusion constraint "deferred_excl_f1_excl" DETAIL: Key (f1)=(3) conflicts with key (f1)=(3). DROP TABLE deferred_excl; --- verify constraints created for NOT NULL clauses -CREATE TABLE notnull_tbl1 (a INTEGER NOT NULL NOT NULL); -\d+ notnull_tbl1 - Table "public.notnull_tbl1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "notnull_tbl1_a_not_null" NOT NULL "a" - -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; - conname | contype | conkey --------------------------+---------+-------- - notnull_tbl1_a_not_null | n | {1} -(1 row) - --- no-op -ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a; -\d+ notnull_tbl1 - Table "public.notnull_tbl1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "notnull_tbl1_a_not_null" NOT NULL "a" - --- duplicate name -ALTER TABLE notnull_tbl1 ADD COLUMN b INT CONSTRAINT notnull_tbl1_a_not_null NOT NULL; -ERROR: constraint "notnull_tbl1_a_not_null" for relation "notnull_tbl1" already exists --- DROP NOT NULL gets rid of both the attnotnull flag and the constraint itself -ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL; -\d notnull_tbl1 - Table "public.notnull_tbl1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; - conname | contype | conkey ----------+---------+-------- -(0 rows) - --- SET NOT NULL puts both back -ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; -\d notnull_tbl1 - Table "public.notnull_tbl1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | not null | - -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; - conname | contype | conkey --------------------------+---------+-------- - notnull_tbl1_a_not_null | n | {1} -(1 row) - --- Doing it twice doesn't create a redundant constraint -ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; - conname | contype | conkey --------------------------+---------+-------- - notnull_tbl1_a_not_null | n | {1} -(1 row) - --- Using the "table constraint" syntax also works -ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL; -ALTER TABLE notnull_tbl1 ADD CONSTRAINT foobar NOT NULL a; -\d notnull_tbl1 - Table "public.notnull_tbl1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | not null | - -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; - conname | contype | conkey ----------+---------+-------- - foobar | n | {1} -(1 row) - -DROP TABLE notnull_tbl1; --- 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 --- can't drop not-null in primary key -CREATE TABLE notnull_tbl2 (a INTEGER PRIMARY KEY); -ALTER TABLE notnull_tbl2 ALTER a DROP NOT NULL; -ERROR: column "a" is in a primary key -DROP TABLE notnull_tbl2; --- 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" - -DROP TABLE notnull_tbl1; -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 - -BEGIN; -/* make sure the table can be put right, but roll that back */ -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 - -ROLLBACK; --- Leave this table around for pg_upgrade testing -CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL)); -ALTER TABLE notnull_tbl3 ALTER A DROP NOT NULL; -ALTER TABLE notnull_tbl3 ADD b int, ADD CONSTRAINT pk PRIMARY KEY (a, b); -\d notnull_tbl3 - Table "public.notnull_tbl3" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | not null | - b | integer | | not null | -Indexes: - "pk" PRIMARY KEY, btree (a, b) -Check constraints: - "notnull_tbl3_a_check" CHECK (a IS NOT NULL) - -ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk; -\d notnull_tbl3 - Table "public.notnull_tbl3" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | - b | integer | | | -Check constraints: - "notnull_tbl3_a_check" CHECK (a IS NOT NULL) - --- Primary keys in parent table cause NOT NULL constraint to spawn on their --- children. Verify that they work correctly. -CREATE TABLE cnn_parent (a int, b int); -CREATE TABLE cnn_child () INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild (NOT NULL b) INHERITS (cnn_child); -CREATE TABLE cnn_child2 (NOT NULL a NO INHERIT) INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild2 () INHERITS (cnn_grandchild, cnn_child2); -NOTICE: merging multiple inherited definitions of column "a" -NOTICE: merging multiple inherited definitions of column "b" -ALTER TABLE cnn_parent ADD PRIMARY KEY (b); -\d+ cnn_grandchild - Table "public.cnn_grandchild" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | not null | | plain | | -Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (local, inherited) -Inherits: cnn_child -Child tables: cnn_grandchild2 - -\d+ cnn_grandchild2 - Table "public.cnn_grandchild2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | not null | | plain | | -Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (inherited) -Inherits: cnn_grandchild, - cnn_child2 - -ALTER TABLE cnn_parent DROP CONSTRAINT cnn_parent_pkey; -\set VERBOSITY terse -DROP TABLE cnn_parent CASCADE; -NOTICE: drop cascades to 4 other objects -\set VERBOSITY default --- As above, but create the primary key ahead of time -CREATE TABLE cnn_parent (a int, b int PRIMARY KEY); -CREATE TABLE cnn_child () INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild (NOT NULL b) INHERITS (cnn_child); -CREATE TABLE cnn_child2 (NOT NULL a NO INHERIT) INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild2 () INHERITS (cnn_grandchild, cnn_child2); -NOTICE: merging multiple inherited definitions of column "a" -NOTICE: merging multiple inherited definitions of column "b" -ALTER TABLE cnn_parent ADD PRIMARY KEY (b); -ERROR: multiple primary keys for table "cnn_parent" are not allowed -\d+ cnn_grandchild - Table "public.cnn_grandchild" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | not null | | plain | | -Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (local, inherited) -Inherits: cnn_child -Child tables: cnn_grandchild2 - -\d+ cnn_grandchild2 - Table "public.cnn_grandchild2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | not null | | plain | | -Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (inherited) -Inherits: cnn_grandchild, - cnn_child2 - -ALTER TABLE cnn_parent DROP CONSTRAINT cnn_parent_pkey; -\set VERBOSITY terse -DROP TABLE cnn_parent CASCADE; -NOTICE: drop cascades to 4 other objects -\set VERBOSITY default --- As above, but create the primary key using a UNIQUE index -CREATE TABLE cnn_parent (a int, b int); -CREATE TABLE cnn_child () INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild (NOT NULL b) INHERITS (cnn_child); -CREATE TABLE cnn_child2 (NOT NULL a NO INHERIT) INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild2 () INHERITS (cnn_grandchild, cnn_child2); -NOTICE: merging multiple inherited definitions of column "a" -NOTICE: merging multiple inherited definitions of column "b" -CREATE UNIQUE INDEX b_uq ON cnn_parent (b); -ALTER TABLE cnn_parent ADD PRIMARY KEY USING INDEX b_uq; -\d+ cnn_grandchild - Table "public.cnn_grandchild" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | not null | | plain | | -Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (local, inherited) -Inherits: cnn_child -Child tables: cnn_grandchild2 - -\d+ cnn_grandchild2 - Table "public.cnn_grandchild2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | not null | | plain | | -Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (inherited) -Inherits: cnn_grandchild, - cnn_child2 - -ALTER TABLE cnn_parent DROP CONSTRAINT cnn_parent_pkey; -ERROR: constraint "cnn_parent_pkey" of relation "cnn_parent" does not exist --- keeps these tables around, for pg_upgrade testing --- A primary key shouldn't attach to a unique constraint -create table cnn2_parted (a int primary key) partition by list (a); -create table cnn2_part1 (a int unique); -alter table cnn2_parted attach partition cnn2_part1 for values in (1); -\d+ cnn2_part1 - Table "public.cnn2_part1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Partition of: cnn2_parted FOR VALUES IN (1) -Partition constraint: ((a IS NOT NULL) AND (a = 1)) -Indexes: - "cnn2_part1_pkey" PRIMARY KEY, btree (a) - "cnn2_part1_a_key" UNIQUE CONSTRAINT, btree (a) - -drop table cnn2_parted; --- ensure columns in partitions are marked not-null -create table cnn2_parted(a int primary key) partition by list (a); -create table cnn2_part1(a int); -alter table cnn2_parted attach partition cnn2_part1 for values in (1); -insert into cnn2_part1 values (null); -ERROR: null value in column "a" of relation "cnn2_part1" violates not-null constraint -DETAIL: Failing row contains (null). -drop table cnn2_parted, cnn2_part1; -create table cnn2_parted(a int not null) partition by list (a); -create table cnn2_part1(a int primary key); -alter table cnn2_parted attach partition cnn2_part1 for values in (1); -ERROR: column "a" in child table must be marked NOT NULL -drop table cnn2_parted, cnn2_part1; -create table cnn2_parted(a int) partition by list (a); -create table cnn_part1 partition of cnn2_parted for values in (1, null); -insert into cnn_part1 values (null); -alter table cnn2_parted add primary key (a); -ERROR: column "a" of relation "cnn_part1" contains null values -drop table cnn2_parted; --- columns in regular and LIKE inheritance should be marked not-nullable --- for primary keys, even if those are deferred -CREATE TABLE notnull_tbl4 (a INTEGER PRIMARY KEY INITIALLY DEFERRED); -CREATE TABLE notnull_tbl4_lk (LIKE notnull_tbl4); -CREATE TABLE notnull_tbl4_lk2 (LIKE notnull_tbl4 INCLUDING INDEXES); -CREATE TABLE notnull_tbl4_lk3 (LIKE notnull_tbl4 INCLUDING INDEXES, CONSTRAINT a_nn NOT NULL a); -CREATE TABLE notnull_tbl4_cld () INHERITS (notnull_tbl4); -CREATE TABLE notnull_tbl4_cld2 (PRIMARY KEY (a) DEFERRABLE) INHERITS (notnull_tbl4); -CREATE TABLE notnull_tbl4_cld3 (PRIMARY KEY (a) DEFERRABLE, CONSTRAINT a_nn NOT NULL a) INHERITS (notnull_tbl4); -\d+ notnull_tbl4 - Table "public.notnull_tbl4" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Indexes: - "notnull_tbl4_pkey" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED -Child tables: notnull_tbl4_cld, - notnull_tbl4_cld2, - notnull_tbl4_cld3 - -\d+ notnull_tbl4_lk - Table "public.notnull_tbl4_lk" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "notnull_tbl4_lk_a_not_null" NOT NULL "a" - -\d+ notnull_tbl4_lk2 - Table "public.notnull_tbl4_lk2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Indexes: - "notnull_tbl4_lk2_pkey" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED - -\d+ notnull_tbl4_lk3 - Table "public.notnull_tbl4_lk3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Indexes: - "notnull_tbl4_lk3_pkey" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED -Not-null constraints: - "a_nn" NOT NULL "a" - -\d+ notnull_tbl4_cld - Table "public.notnull_tbl4_cld" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "notnull_tbl4_cld_a_not_null" NOT NULL "a" (inherited) -Inherits: notnull_tbl4 - -\d+ notnull_tbl4_cld2 - Table "public.notnull_tbl4_cld2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Indexes: - "notnull_tbl4_cld2_pkey" PRIMARY KEY, btree (a) DEFERRABLE -Not-null constraints: - "notnull_tbl4_cld2_a_not_null" NOT NULL "a" (inherited) -Inherits: notnull_tbl4 - -\d+ notnull_tbl4_cld3 - Table "public.notnull_tbl4_cld3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Indexes: - "notnull_tbl4_cld3_pkey" PRIMARY KEY, btree (a) DEFERRABLE -Not-null constraints: - "a_nn" NOT NULL "a" (local, inherited) -Inherits: notnull_tbl4 - --- leave these tables around for pg_upgrade testing --- also, if a NOT NULL is dropped underneath a deferrable PK, the column --- should still be nullable afterwards. This mimics what pg_dump does. -CREATE TABLE notnull_tbl5 (a INTEGER CONSTRAINT a_nn NOT NULL); -ALTER TABLE notnull_tbl5 ADD PRIMARY KEY (a) DEFERRABLE; -ALTER TABLE notnull_tbl5 DROP CONSTRAINT a_nn; -\d+ notnull_tbl5 - Table "public.notnull_tbl5" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Indexes: - "notnull_tbl5_pkey" PRIMARY KEY, btree (a) DEFERRABLE - -DROP TABLE notnull_tbl5; -- Comments -- Setup a low-level role to enforce non-superuser checks. CREATE ROLE regress_constraint_comments; diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 344d05233a..284a7fb85c 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -759,23 +759,21 @@ CREATE TABLE part_b PARTITION OF parted ( NOTICE: merging constraint "check_a" with inherited definition -- conislocal should be false for any merged constraints, true otherwise SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname; - conname | conislocal | coninhcount --------------------+------------+------------- - check_a | f | 1 - part_b_b_not_null | t | 1 - check_b | t | 0 -(3 rows) + conname | conislocal | coninhcount +---------+------------+------------- + check_a | f | 1 + check_b | t | 0 +(2 rows) -- Once check_b is added to the parent, it should be made non-local for part_b ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0); NOTICE: merging constraint "check_b" with inherited definition SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname; - conname | conislocal | coninhcount --------------------+------------+------------- - check_a | f | 1 - check_b | f | 1 - part_b_b_not_null | t | 1 -(3 rows) + conname | conislocal | coninhcount +---------+------------+------------- + check_a | f | 1 + check_b | f | 1 +(2 rows) -- Neither check_a nor check_b are droppable from part_b ALTER TABLE part_b DROP CONSTRAINT check_a; @@ -787,10 +785,9 @@ ERROR: cannot drop inherited constraint "check_b" of relation "part_b" -- be local constraints. ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b; SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname; - conname | conislocal | coninhcount --------------------+------------+------------- - part_b_b_not_null | t | 1 -(1 row) + conname | conislocal | coninhcount +---------+------------+------------- +(0 rows) -- specify PARTITION BY for a partition CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c); @@ -854,8 +851,6 @@ drop table test_part_coll_posix; b | integer | | not null | 1 | plain | | Partition of: parted FOR VALUES IN ('b') Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text)) -Not-null constraints: - "part_b_b_not_null" NOT NULL "b" (local, inherited) -- Both partition bound and partition key in describe output \d+ part_c @@ -867,8 +862,6 @@ Not-null constraints: Partition of: parted FOR VALUES IN ('c') Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text)) Partition key: RANGE (b) -Not-null constraints: - "part_c_b_not_null" NOT NULL "b" (local, inherited) Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) -- a level-2 partition's constraint will include the parent's expressions @@ -880,8 +873,6 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) b | integer | | not null | 0 | plain | | Partition of: part_c FOR VALUES FROM (1) TO (10) Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10)) -Not-null constraints: - "part_c_b_not_null" NOT NULL "b" (inherited) -- Show partition count in the parent's describe output -- Tempted to include \d+ output listing partitions with bound info but diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 61956773ff..0ed94f1d2f 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -333,8 +333,6 @@ CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING a | text | | not null | | main | | b | text | | | | extended | | c | text | | | | external | | -Not-null constraints: - "ctlt12_storage_a_not_null" NOT NULL "a" CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); \d+ ctlt12_comments @@ -344,8 +342,6 @@ CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDIN a | text | | not null | | extended | | A b | text | | | | extended | | B c | text | | | | extended | | C -Not-null constraints: - "ctlt12_comments_a_not_null" NOT NULL "a" CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); NOTICE: merging column "a" with inherited definition @@ -359,8 +355,6 @@ NOTICE: merging constraint "ctlt1_a_check" with inherited definition b | text | | | | extended | | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) -Not-null constraints: - "ctlt1_inh_a_not_null" NOT NULL "a" (local, inherited) Inherits: ctlt1 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass; @@ -382,8 +376,6 @@ Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) -Not-null constraints: - "ctlt13_inh_a_not_null" NOT NULL "a" (inherited) Inherits: ctlt1, ctlt3 @@ -402,8 +394,6 @@ Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) -Not-null constraints: - "ctlt13_like_a_not_null" NOT NULL "a" (inherited) Inherits: ctlt1 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass; diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out index 97fa1793ba..7b2198eac6 100644 --- a/src/test/regress/expected/event_trigger.out +++ b/src/test/regress/expected/event_trigger.out @@ -408,7 +408,6 @@ NOTICE: END: command_tag=CREATE SCHEMA type=schema identity=evttrig NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_a_seq NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_c_seq NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.one -NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_pkey NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_a_seq NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_c_seq @@ -423,7 +422,6 @@ CREATE TABLE evttrig.parted ( id int PRIMARY KEY) PARTITION BY RANGE (id); NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.parted -NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.parted NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.parted_pkey CREATE TABLE evttrig.part_1_10 PARTITION OF evttrig.parted (id) FOR VALUES FROM (1) TO (10); diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 3940a515b5..6ed50fdcfa 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -742,8 +742,6 @@ COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) -Not-null constraints: - "ft1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -866,9 +864,6 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN; Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) -Not-null constraints: - "ft1_c1_not_null" NOT NULL "c1" - "ft1_c6_not_null" NOT NULL "c6" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1414,8 +1409,6 @@ CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1) c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 @@ -1425,8 +1418,6 @@ Child tables: ft2, FOREIGN c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1439,8 +1430,6 @@ DROP FOREIGN TABLE ft2; c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" CREATE FOREIGN TABLE ft2 ( c1 integer NOT NULL, @@ -1454,8 +1443,6 @@ CREATE FOREIGN TABLE ft2 ( c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1467,8 +1454,6 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 @@ -1478,8 +1463,6 @@ Child tables: ft2, FOREIGN c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1501,8 +1484,6 @@ NOTICE: merging column "c3" with inherited definition c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1516,8 +1497,6 @@ Child tables: ct3, c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (inherited) Inherits: ft2 \d+ ft3 @@ -1527,8 +1506,6 @@ Inherits: ft2 c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not-null constraints: - "ft3_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 Inherits: ft2 @@ -1550,9 +1527,6 @@ ALTER TABLE fd_pt1 ADD COLUMN c8 integer; c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" - "fd_pt1_c7_not_null" NOT NULL "c7" Child tables: ft2, FOREIGN \d+ ft2 @@ -1567,9 +1541,6 @@ Child tables: ft2, FOREIGN c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) - "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1588,9 +1559,6 @@ Child tables: ct3, c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (inherited) - "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) Inherits: ft2 \d+ ft3 @@ -1605,9 +1573,6 @@ Inherits: ft2 c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | -Not-null constraints: - "ft3_c1_not_null" NOT NULL "c1" (local, inherited) - "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) Server: s0 Inherits: ft2 @@ -1636,9 +1601,6 @@ ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; c6 | integer | | not null | | plain | | c7 | integer | | | | plain | | c8 | text | | | | external | | -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" - "fd_pt1_c6_not_null" NOT NULL "c6" Child tables: ft2, FOREIGN \d+ ft2 @@ -1653,9 +1615,6 @@ Child tables: ft2, FOREIGN c6 | integer | | not null | | | plain | | c7 | integer | | | | | plain | | c8 | text | | | | | external | | -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) - "fd_pt1_c6_not_null" NOT NULL "c6" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1675,8 +1634,6 @@ ALTER TABLE fd_pt1 DROP COLUMN c8; c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 @@ -1686,8 +1643,6 @@ Child tables: ft2, FOREIGN c1 | integer | | not null | | | plain | 10000 | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1702,12 +1657,11 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid) WHERE pc.relname = 'fd_pt1' ORDER BY 1,2; - relname | conname | contype | conislocal | coninhcount | connoinherit ----------+--------------------+---------+------------+-------------+-------------- - fd_pt1 | fd_pt1_c1_not_null | n | t | 0 | f - fd_pt1 | fd_pt1chk1 | c | t | 0 | t - fd_pt1 | fd_pt1chk2 | c | t | 0 | f -(3 rows) + relname | conname | contype | conislocal | coninhcount | connoinherit +---------+------------+---------+------------+-------------+-------------- + fd_pt1 | fd_pt1chk1 | c | t | 0 | t + fd_pt1 | fd_pt1chk2 | c | t | 0 | f +(2 rows) -- child does not inherit NO INHERIT constraints \d+ fd_pt1 @@ -1720,8 +1674,6 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 @@ -1733,8 +1685,6 @@ Child tables: ft2, FOREIGN c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1771,8 +1721,6 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 @@ -1784,8 +1732,6 @@ Child tables: ft2, FOREIGN c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1805,8 +1751,6 @@ ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID; c3 | date | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 @@ -1819,8 +1763,6 @@ Child tables: ft2, FOREIGN Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1836,8 +1778,6 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; c3 | date | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 @@ -1850,8 +1790,6 @@ Child tables: ft2, FOREIGN Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) -Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1871,8 +1809,6 @@ ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check; f3 | date | | | | plain | | Check constraints: "f2_check" CHECK (f2 <> ''::text) -Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "f1" Child tables: ft2, FOREIGN \d+ ft2 @@ -1885,8 +1821,6 @@ Child tables: ft2, FOREIGN Check constraints: "f2_check" CHECK (f2 <> ''::text) "fd_pt1chk2" CHECK (f2 <> ''::text) -Not-null constraints: - "ft2_c1_not_null" NOT NULL "f1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1933,8 +1867,6 @@ CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1) c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not-null constraints: - "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN \d+ fd_pt2_1 @@ -1946,8 +1878,6 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) -Not-null constraints: - "fd_pt2_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1967,8 +1897,6 @@ CREATE FOREIGN TABLE fd_pt2_1 ( c2 | text | | | | | extended | | c3 | date | | | | | plain | | c4 | character(1) | | | | | extended | | -Not-null constraints: - "fd_pt2_1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1984,8 +1912,6 @@ DROP FOREIGN TABLE fd_pt2_1; c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not-null constraints: - "fd_pt2_c1_not_null" NOT NULL "c1" Number of partitions: 0 CREATE FOREIGN TABLE fd_pt2_1 ( @@ -2000,8 +1926,6 @@ CREATE FOREIGN TABLE fd_pt2_1 ( c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not-null constraints: - "fd_pt2_1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2015,8 +1939,6 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not-null constraints: - "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN \d+ fd_pt2_1 @@ -2028,8 +1950,6 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) -Not-null constraints: - "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2047,8 +1967,6 @@ ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> ''); c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not-null constraints: - "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN \d+ fd_pt2_1 @@ -2062,9 +1980,6 @@ Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Check constraints: "p21chk" CHECK (c2 <> ''::text) -Not-null constraints: - "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) - "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2082,9 +1997,6 @@ ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; c2 | text | | not null | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not-null constraints: - "fd_pt2_c1_not_null" NOT NULL "c1" - "fd_pt2_c2_not_null" NOT NULL "c2" Number of partitions: 0 \d+ fd_pt2_1 @@ -2096,9 +2008,6 @@ Number of partitions: 0 c3 | date | | not null | | | plain | | Check constraints: "p21chk" CHECK (c2 <> ''::text) -Not-null constraints: - "fd_pt2_1_c1_not_null" NOT NULL "c1" - "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2118,9 +2027,6 @@ ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); Partition key: LIST (c1) Check constraints: "fd_pt2chk1" CHECK (c1 > 0) -Not-null constraints: - "fd_pt2_c1_not_null" NOT NULL "c1" - "fd_pt2_c2_not_null" NOT NULL "c2" Number of partitions: 0 \d+ fd_pt2_1 @@ -2132,10 +2038,6 @@ Number of partitions: 0 c3 | date | | not null | | | plain | | Check constraints: "p21chk" CHECK (c2 <> ''::text) -Not-null constraints: - "fd_pt2_1_c1_not_null" NOT NULL "c1" - "fd_pt2_1_c2_not_null" NOT NULL "c2" - "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 0b55167ac8..46764bd9e3 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -2036,19 +2036,13 @@ ORDER BY co.contype, cr.relname, co.conname, p.conname; part33_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk part3_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk parted_self_fk | parted_self_fk_id_abc_fkey | f | t | | | parted_self_fk - part1_self_fk | part1_self_fk_id_not_null | n | t | | | - part2_self_fk | parted_self_fk_id_not_null | n | t | | | - part32_self_fk | part3_self_fk_id_not_null | n | t | | | - part33_self_fk | part33_self_fk_id_not_null | n | t | | | - part3_self_fk | part3_self_fk_id_not_null | n | t | | | - parted_self_fk | parted_self_fk_id_not_null | n | t | | | part1_self_fk | part1_self_fk_pkey | p | t | parted_self_fk_pkey | t | part2_self_fk | part2_self_fk_pkey | p | t | parted_self_fk_pkey | t | part32_self_fk | part32_self_fk_pkey | p | t | part3_self_fk_pkey | t | part33_self_fk | part33_self_fk_pkey | p | t | part3_self_fk_pkey | t | part3_self_fk | part3_self_fk_pkey | p | t | parted_self_fk_pkey | t | parted_self_fk | parted_self_fk_pkey | p | t | | | -(18 rows) +(12 rows) -- detach and re-attach multiple times just to ensure everything is kosher ALTER TABLE parted_self_fk DETACH PARTITION part2_self_fk; @@ -2071,19 +2065,13 @@ ORDER BY co.contype, cr.relname, co.conname, p.conname; part33_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk part3_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk parted_self_fk | parted_self_fk_id_abc_fkey | f | t | | | parted_self_fk - part1_self_fk | part1_self_fk_id_not_null | n | t | | | - part2_self_fk | parted_self_fk_id_not_null | n | t | | | - part32_self_fk | part3_self_fk_id_not_null | n | t | | | - part33_self_fk | part33_self_fk_id_not_null | n | t | | | - part3_self_fk | part3_self_fk_id_not_null | n | t | | | - parted_self_fk | parted_self_fk_id_not_null | n | t | | | part1_self_fk | part1_self_fk_pkey | p | t | parted_self_fk_pkey | t | part2_self_fk | part2_self_fk_pkey | p | t | parted_self_fk_pkey | t | part32_self_fk | part32_self_fk_pkey | p | t | part3_self_fk_pkey | t | part33_self_fk | part33_self_fk_pkey | p | t | part3_self_fk_pkey | t | part3_self_fk | part3_self_fk_pkey | p | t | parted_self_fk_pkey | t | parted_self_fk | parted_self_fk_pkey | p | t | | | -(18 rows) +(12 rows) -- Leave this table around, for pg_upgrade/pg_dump tests -- Test creating a constraint at the parent that already exists in partitions. diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index a4f3773662..44058db7c1 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -318,8 +318,6 @@ NOTICE: merging column "b" with inherited definition a | integer | | not null | | plain | | b | integer | | | generated always as (a * 22) stored | plain | | x | integer | | | | plain | | -Not-null constraints: - "gtestx_a_not_null" NOT NULL "a" (inherited) Inherits: gtest1 CREATE TABLE gtestxx_1 (a int NOT NULL, b int); diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 29539e7f63..3d554fe327 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -578,10 +578,6 @@ TABLE itest8; f3 | integer | | not null | generated by default as identity | plain | | f4 | bigint | | not null | generated always as identity | plain | | f5 | bigint | | | | plain | | -Not-null constraints: - "itest8_f2_not_null" NOT NULL "f2" - "itest8_f3_not_null" NOT NULL "f3" - "itest8_f4_not_null" NOT NULL "f4" \d itest8_f2_seq Sequence "public.itest8_f2_seq" diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out index 3de99dd927..f25723da92 100644 --- a/src/test/regress/expected/indexing.out +++ b/src/test/regress/expected/indexing.out @@ -1117,17 +1117,15 @@ alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 3 select conname, contype, conrelid::regclass, conindid::regclass, conkey from pg_constraint where conrelid::regclass::text like 'idxpart%' order by conrelid::regclass::text, conname; - conname | contype | conrelid | conindid | conkey ----------------------+---------+-----------+----------------+-------- - idxpart_pkey | p | idxpart | idxpart_pkey | {1,2} - idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2} - idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2} - idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2} - idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2} - idxpart3_a_not_null | n | idxpart3 | - | {2} - idxpart3_b_not_null | n | idxpart3 | - | {1} - idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1} -(8 rows) + conname | contype | conrelid | conindid | conkey +----------------+---------+-----------+----------------+-------- + idxpart_pkey | p | idxpart | idxpart_pkey | {1,2} + idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2} + idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2} + idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2} + idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2} + idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1} +(6 rows) drop table idxpart; -- Verify that multi-layer partitioning honors the requirement that all @@ -1260,20 +1258,12 @@ create table idxpart (a int) partition by range (a); create table idxpart0 (like idxpart); alter table idxpart0 add unique (a); alter table idxpart attach partition idxpart0 default; -alter table only idxpart add primary key (a); -- works, but idxpart0.a is nullable -\d idxpart0 - Table "public.idxpart0" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | -Partition of: idxpart DEFAULT -Indexes: - "idxpart0_a_key" UNIQUE CONSTRAINT, btree (a) - -alter index idxpart_pkey attach partition idxpart0_a_key; -- fails, lacks NOT NULL -ERROR: invalid primary key definition -DETAIL: Column "a" of relation "idxpart0" is not marked NOT NULL. +alter table only idxpart add primary key (a); -- fail, no not-null constraint +ERROR: constraint must be added to child tables too +DETAIL: Column "a" of relation "idxpart0" is not already NOT NULL. +HINT: Do not specify the ONLY keyword. alter table idxpart0 alter column a set not null; +alter table only idxpart add primary key (a); -- now it works alter index idxpart_pkey attach partition idxpart0_a_key; alter table idxpart0 alter column a drop not null; -- fail, pkey needs it ERROR: column "a" is marked NOT NULL in parent table diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index a621db0aa3..ad73213414 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -2023,516 +2023,6 @@ select * from cnullparent where f1 = 2; drop table cnullparent cascade; NOTICE: drop cascades to table cnullchild -- --- Test inheritance of NOT NULL constraints --- -create table pp1 (f1 int); -create table cc1 (f2 text, f3 int) inherits (pp1); -\d cc1 - Table "public.cc1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - f1 | integer | | | - f2 | text | | | - f3 | integer | | | -Inherits: pp1 - -create table cc2(f4 float) inherits(pp1,cc1); -NOTICE: merging multiple inherited definitions of column "f1" -\d cc2 - Table "public.cc2" - Column | Type | Collation | Nullable | Default ---------+------------------+-----------+----------+--------- - f1 | integer | | | - f2 | text | | | - f3 | integer | | | - f4 | double precision | | | -Inherits: pp1, - cc1 - --- named NOT NULL constraint -alter table cc1 add column a2 int constraint nn not null; -\d+ cc1 - Table "public.cc1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | | | plain | | - f2 | text | | | | extended | | - f3 | integer | | | | plain | | - a2 | integer | | not null | | plain | | -Not-null constraints: - "nn" NOT NULL "a2" -Inherits: pp1 -Child tables: cc2 - -\d+ cc2 - Table "public.cc2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------------------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | | | plain | | - f2 | text | | | | extended | | - f3 | integer | | | | plain | | - f4 | double precision | | | | plain | | - a2 | integer | | not null | | plain | | -Not-null constraints: - "nn" NOT NULL "a2" (inherited) -Inherits: pp1, - cc1 - -alter table pp1 alter column f1 set not null; -\d+ pp1 - Table "public.pp1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | not null | | plain | | -Not-null constraints: - "pp1_f1_not_null" NOT NULL "f1" -Child tables: cc1, - cc2 - -\d+ cc1 - Table "public.cc1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | not null | | plain | | - f2 | text | | | | extended | | - f3 | integer | | | | plain | | - a2 | integer | | not null | | plain | | -Not-null constraints: - "pp1_f1_not_null" NOT NULL "f1" (inherited) - "nn" NOT NULL "a2" -Inherits: pp1 -Child tables: cc2 - -\d+ cc2 - Table "public.cc2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------------------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | not null | | plain | | - f2 | text | | | | extended | | - f3 | integer | | | | plain | | - f4 | double precision | | | | plain | | - a2 | integer | | not null | | plain | | -Not-null constraints: - "pp1_f1_not_null" NOT NULL "f1" (inherited) - "nn" NOT NULL "a2" (inherited) -Inherits: pp1, - cc1 - --- cannot create table with inconsistent NO INHERIT constraint -create table cc3 (a2 int not null no inherit) inherits (cc1); -NOTICE: moving and merging column "a2" with inherited definition -DETAIL: User-specified column moved to the position of the inherited column. -ERROR: cannot define not-null constraint on column "a2" with NO INHERIT -DETAIL: The column has an inherited not-null constraint. --- change NO INHERIT status of inherited constraint: no dice, it's inherited -alter table cc2 add not null a2 no inherit; -ERROR: cannot change NO INHERIT status of NOT NULL constraint "nn" on relation "cc2" --- remove constraint from cc2: no dice, it's inherited -alter table cc2 alter column a2 drop not null; -ERROR: cannot drop inherited constraint "nn" of relation "cc2" --- remove constraint cc1, should succeed -alter table cc1 alter column a2 drop not null; -\d+ cc1 - Table "public.cc1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | not null | | plain | | - f2 | text | | | | extended | | - f3 | integer | | | | plain | | - a2 | integer | | | | plain | | -Not-null constraints: - "pp1_f1_not_null" NOT NULL "f1" (inherited) -Inherits: pp1 -Child tables: cc2 - --- same for cc2 -alter table cc2 alter column f1 drop not null; -ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc2" -\d+ cc2 - Table "public.cc2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------------------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | not null | | plain | | - f2 | text | | | | extended | | - f3 | integer | | | | plain | | - f4 | double precision | | | | plain | | - a2 | integer | | | | plain | | -Not-null constraints: - "pp1_f1_not_null" NOT NULL "f1" (inherited) -Inherits: pp1, - cc1 - --- remove from cc1, should fail again -alter table cc1 alter column f1 drop not null; -ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc1" --- remove from pp1, should succeed -alter table pp1 alter column f1 drop not null; -\d+ pp1 - Table "public.pp1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | | | plain | | -Child tables: cc1, - cc2 - -alter table pp1 add primary key (f1); --- Leave these tables around, for pg_upgrade testing --- Test a not-null addition that must walk down the hierarchy -CREATE TABLE inh_parent (); -CREATE TABLE inh_child (i int) INHERITS (inh_parent); -CREATE TABLE inh_grandchild () INHERITS (inh_parent, inh_child); -ALTER TABLE inh_parent ADD COLUMN i int NOT NULL; -NOTICE: merging definition of column "i" for child "inh_child" -NOTICE: merging definition of column "i" for child "inh_grandchild" -drop table inh_parent, inh_child, inh_grandchild; --- Test the same constraint name for different columns in different parents -create table inh_parent1(a int constraint nn not null); -create table inh_parent2(b int constraint nn not null); -create table inh_child () inherits (inh_parent1, inh_parent2); -\d+ inh_child - Table "public.inh_child" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | - b | integer | | not null | | plain | | -Not-null constraints: - "nn" NOT NULL "a" (inherited) - "inh_child_b_not_null" NOT NULL "b" (inherited) -Inherits: inh_parent1, - inh_parent2 - -drop table inh_parent1, inh_parent2, inh_child; --- Test multiple parents with overlapping primary keys -create table inh_parent1(a int, b int, c int, primary key (a, b)); -create table inh_parent2(d int, e int, b int, primary key (d, b)); -create table inh_child() inherits (inh_parent1, inh_parent2); -NOTICE: merging multiple inherited definitions of column "b" -select conrelid::regclass, conname, contype, conkey, - coninhcount, conislocal, connoinherit - from pg_constraint where contype in ('n','p') and - conrelid::regclass::text in ('inh_child', 'inh_parent1', 'inh_parent2') - order by 1, 2; - conrelid | conname | contype | conkey | coninhcount | conislocal | connoinherit --------------+----------------------+---------+--------+-------------+------------+-------------- - inh_parent1 | inh_parent1_pkey | p | {1,2} | 0 | t | t - inh_parent2 | inh_parent2_pkey | p | {1,3} | 0 | t | t - inh_child | inh_child_a_not_null | n | {1} | 1 | f | f - inh_child | inh_child_b_not_null | n | {2} | 2 | f | f - inh_child | inh_child_d_not_null | n | {4} | 1 | f | f -(5 rows) - -\d+ inh_child - Table "public.inh_child" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | - b | integer | | not null | | plain | | - c | integer | | | | plain | | - d | integer | | not null | | plain | | - e | integer | | | | plain | | -Not-null constraints: - "inh_child_a_not_null" NOT NULL "a" (inherited) - "inh_child_b_not_null" NOT NULL "b" (inherited) - "inh_child_d_not_null" NOT NULL "d" (inherited) -Inherits: inh_parent1, - inh_parent2 - -drop table inh_parent1, inh_parent2, inh_child; --- NOT NULL NO INHERIT -create table inh_nn_parent(a int); -create table inh_nn_child() inherits (inh_nn_parent); -alter table inh_nn_parent add not null a no inherit; -create table inh_nn_child2() inherits (inh_nn_parent); -select conrelid::regclass, conname, contype, conkey, - (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]), - coninhcount, conislocal, connoinherit - from pg_constraint where contype = 'n' and - conrelid::regclass::text like 'inh\_nn\_%' - order by 2, 1; - conrelid | conname | contype | conkey | attname | coninhcount | conislocal | connoinherit ----------------+--------------------------+---------+--------+---------+-------------+------------+-------------- - inh_nn_parent | inh_nn_parent_a_not_null | n | {1} | a | 0 | t | t -(1 row) - -\d+ inh_nn* - Table "public.inh_nn_child" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -Inherits: inh_nn_parent - - Table "public.inh_nn_child2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | -Inherits: inh_nn_parent - - Table "public.inh_nn_parent" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | not null | | plain | | -Not-null constraints: - "inh_nn_parent_a_not_null" NOT NULL "a" NO INHERIT -Child tables: inh_nn_child, - inh_nn_child2 - -drop table inh_nn_parent, inh_nn_child, inh_nn_child2; -CREATE TABLE inh_nn_parent (a int, NOT NULL a NO INHERIT); -CREATE TABLE inh_nn_child() INHERITS (inh_nn_parent); -ALTER TABLE inh_nn_parent ADD CONSTRAINT nna NOT NULL a; -ERROR: cannot change NO INHERIT status of NOT NULL constraint "inh_nn_parent_a_not_null" on relation "inh_nn_parent" -ALTER TABLE inh_nn_parent ALTER a SET NOT NULL; -ERROR: cannot change NO INHERIT status of NOT NULL constraint "inh_nn_parent_a_not_null" on relation "inh_nn_parent" -DROP TABLE inh_nn_parent cascade; -NOTICE: drop cascades to table inh_nn_child --- Adding a PK at the top level of a hierarchy should cause all descendants --- to be checked for nulls, even past a no-inherit constraint -CREATE TABLE inh_nn_lvl1 (a int); -CREATE TABLE inh_nn_lvl2 () INHERITS (inh_nn_lvl1); -CREATE TABLE inh_nn_lvl3 (CONSTRAINT foo NOT NULL a NO INHERIT) INHERITS (inh_nn_lvl2); -CREATE TABLE inh_nn_lvl4 () INHERITS (inh_nn_lvl3); -CREATE TABLE inh_nn_lvl5 () INHERITS (inh_nn_lvl4); -INSERT INTO inh_nn_lvl2 VALUES (NULL); -ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a); -ERROR: column "a" of relation "inh_nn_lvl2" contains null values -DELETE FROM inh_nn_lvl2; -INSERT INTO inh_nn_lvl5 VALUES (NULL); -ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a); -ERROR: column "a" of relation "inh_nn_lvl5" contains null values -DROP TABLE inh_nn_lvl1 CASCADE; -NOTICE: drop cascades to 4 other objects -DETAIL: drop cascades to table inh_nn_lvl2 -drop cascades to table inh_nn_lvl3 -drop cascades to table inh_nn_lvl4 -drop cascades to table inh_nn_lvl5 --- --- test inherit/deinherit --- -create table inh_parent(f1 int); -create table inh_child1(f1 int not null); -create table inh_child2(f1 int); --- inh_child1 should have not null constraint -alter table inh_child1 inherit inh_parent; --- should fail, missing NOT NULL constraint -alter table inh_child2 inherit inh_child1; -ERROR: column "f1" in child table must be marked NOT NULL -alter table inh_child2 alter column f1 set not null; -alter table inh_child2 inherit inh_child1; --- add NOT NULL constraint recursively -alter table inh_parent alter column f1 set not null; -\d+ inh_parent - Table "public.inh_parent" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | not null | | plain | | -Not-null constraints: - "inh_parent_f1_not_null" NOT NULL "f1" -Child tables: inh_child1 - -\d+ inh_child1 - Table "public.inh_child1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | not null | | plain | | -Not-null constraints: - "inh_child1_f1_not_null" NOT NULL "f1" (local, inherited) -Inherits: inh_parent -Child tables: inh_child2 - -\d+ inh_child2 - Table "public.inh_child2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | not null | | plain | | -Not-null constraints: - "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited) -Inherits: inh_child1 - -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass) - order by 2, 1; - conrelid | conname | contype | coninhcount | conislocal -------------+------------------------+---------+-------------+------------ - inh_child1 | inh_child1_f1_not_null | n | 1 | t - inh_child2 | inh_child2_f1_not_null | n | 1 | t - inh_parent | inh_parent_f1_not_null | n | 0 | t -(3 rows) - --- --- test deinherit procedure --- --- deinherit inh_child1 -create table inh_child3 () inherits (inh_child1); -alter table inh_child1 no inherit inh_parent; -\d+ inh_parent - Table "public.inh_parent" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | not null | | plain | | -Not-null constraints: - "inh_parent_f1_not_null" NOT NULL "f1" - -\d+ inh_child1 - Table "public.inh_child1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | not null | | plain | | -Not-null constraints: - "inh_child1_f1_not_null" NOT NULL "f1" -Child tables: inh_child2, - inh_child3 - -\d+ inh_child2 - Table "public.inh_child2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | not null | | plain | | -Not-null constraints: - "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited) -Inherits: inh_child1 - -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid::regclass::text in ('inh_parent', 'inh_child1', 'inh_child2', 'inh_child3') - order by 2, 1; - conrelid | conname | contype | coninhcount | conislocal -------------+------------------------+---------+-------------+------------ - inh_child1 | inh_child1_f1_not_null | n | 0 | t - inh_child3 | inh_child1_f1_not_null | n | 1 | f - inh_child2 | inh_child2_f1_not_null | n | 1 | t - inh_parent | inh_parent_f1_not_null | n | 0 | t -(4 rows) - -drop table inh_parent, inh_child1, inh_child2, inh_child3; --- a PK in parent must have a not-null in child that it can mark inherited -create table inh_parent (a int primary key); -create table inh_child (a int primary key); -alter table inh_child inherit inh_parent; -- nope -ERROR: column "a" in child table must be marked NOT NULL -alter table inh_child alter a set not null; -alter table inh_child inherit inh_parent; -- now it works --- don't interfere with other types of constraints -alter table inh_parent add constraint inh_parent_excl exclude ((1) with =); -alter table inh_parent add constraint inh_parent_uq unique (a); -alter table inh_parent add constraint inh_parent_fk foreign key (a) references inh_parent (a); -create table inh_child2 () inherits (inh_parent); -create table inh_child3 (like inh_parent); -alter table inh_child3 inherit inh_parent; -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint - where conrelid::regclass::text in ('inh_parent', 'inh_child', 'inh_child2', 'inh_child3') - order by 2, 1; - conrelid | conname | contype | coninhcount | conislocal -------------+-----------------------+---------+-------------+------------ - inh_child2 | inh_child2_a_not_null | n | 1 | f - inh_child3 | inh_child3_a_not_null | n | 1 | t - inh_child | inh_child_a_not_null | n | 1 | t - inh_child | inh_child_pkey | p | 0 | t - inh_parent | inh_parent_excl | x | 0 | t - inh_parent | inh_parent_fk | f | 0 | t - inh_parent | inh_parent_pkey | p | 0 | t - inh_parent | inh_parent_uq | u | 0 | t -(8 rows) - -drop table inh_parent, inh_child, inh_child2, inh_child3; --- --- test multi inheritance tree --- -create table inh_parent(f1 int not null); -create table inh_child1() inherits(inh_parent); -create table inh_child2() inherits(inh_parent); -create table inh_child3() inherits(inh_child1, inh_child2); -NOTICE: merging multiple inherited definitions of column "f1" --- show constraint info -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass, 'inh_child3'::regclass) - order by 2, conrelid::regclass::text; - conrelid | conname | contype | coninhcount | conislocal -------------+------------------------+---------+-------------+------------ - inh_child1 | inh_parent_f1_not_null | n | 1 | f - inh_child2 | inh_parent_f1_not_null | n | 1 | f - inh_child3 | inh_parent_f1_not_null | n | 2 | f - inh_parent | inh_parent_f1_not_null | n | 0 | t -(4 rows) - -drop table inh_parent cascade; -NOTICE: drop cascades to 3 other objects -DETAIL: drop cascades to table inh_child1 -drop cascades to table inh_child2 -drop cascades to table inh_child3 --- test child table with inherited columns and --- with explicitly specified not null constraints -create table inh_parent_1(f1 int); -create table inh_parent_2(f2 text); -create table inh_child(f1 int not null, f2 text not null) inherits(inh_parent_1, inh_parent_2); -NOTICE: merging column "f1" with inherited definition -NOTICE: merging column "f2" with inherited definition --- show constraint info -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid in ('inh_parent_1'::regclass, 'inh_parent_2'::regclass, 'inh_child'::regclass) - order by 2, conrelid::regclass::text; - conrelid | conname | contype | coninhcount | conislocal ------------+-----------------------+---------+-------------+------------ - inh_child | inh_child_f1_not_null | n | 0 | t - inh_child | inh_child_f2_not_null | n | 0 | t -(2 rows) - --- also drops inh_child table -drop table inh_parent_1 cascade; -NOTICE: drop cascades to table inh_child -drop table inh_parent_2; --- test multi layer inheritance tree -create table inh_p1(f1 int not null); -create table inh_p2(f1 int not null); -create table inh_p3(f2 int); -create table inh_p4(f1 int not null, f3 text not null); -create table inh_multiparent() inherits(inh_p1, inh_p2, inh_p3, inh_p4); -NOTICE: merging multiple inherited definitions of column "f1" -NOTICE: merging multiple inherited definitions of column "f1" --- constraint on f1 should have three parents -select conrelid::regclass, contype, conname, - (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]), - coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid::regclass in ('inh_p1', 'inh_p2', 'inh_p3', 'inh_p4', - 'inh_multiparent') - order by conrelid::regclass::text, conname; - conrelid | contype | conname | attname | coninhcount | conislocal ------------------+---------+--------------------+---------+-------------+------------ - inh_multiparent | n | inh_p1_f1_not_null | f1 | 3 | f - inh_multiparent | n | inh_p4_f3_not_null | f3 | 1 | f - inh_p1 | n | inh_p1_f1_not_null | f1 | 0 | t - inh_p2 | n | inh_p2_f1_not_null | f1 | 0 | t - inh_p4 | n | inh_p4_f1_not_null | f1 | 0 | t - inh_p4 | n | inh_p4_f3_not_null | f3 | 0 | t -(6 rows) - -create table inh_multiparent2 (a int not null, f1 int) inherits(inh_p3, inh_multiparent); -NOTICE: merging multiple inherited definitions of column "f2" -NOTICE: merging column "f1" with inherited definition -select conrelid::regclass, contype, conname, - (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]), - coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid::regclass in ('inh_p3', 'inh_multiparent', 'inh_multiparent2') - order by conrelid::regclass::text, conname; - conrelid | contype | conname | attname | coninhcount | conislocal -------------------+---------+-----------------------------+---------+-------------+------------ - inh_multiparent | n | inh_p1_f1_not_null | f1 | 3 | f - inh_multiparent | n | inh_p4_f3_not_null | f3 | 1 | f - inh_multiparent2 | n | inh_multiparent2_a_not_null | a | 0 | t - inh_multiparent2 | n | inh_p1_f1_not_null | f1 | 1 | f - inh_multiparent2 | n | inh_p4_f3_not_null | f3 | 1 | f -(5 rows) - -drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade; -NOTICE: drop cascades to 2 other objects -DETAIL: drop cascades to table inh_multiparent -drop cascades to table inh_multiparent2 --- -- Mixed ownership inheritance tree -- create role regress_alice; diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out index 076264c88e..f13f75eefc 100644 --- a/src/test/regress/expected/partition_merge.out +++ b/src/test/regress/expected/partition_merge.out @@ -801,8 +801,6 @@ Partition constraint: ((i IS NOT NULL) AND (i >= 0) AND (i < 2)) Indexes: "tp_1_2_pkey" PRIMARY KEY, btree (i) "tp_1_2_i_idx" btree (i) -Not-null constraints: - "tp_1_2_i_not_null" NOT NULL "i" DROP TABLE t; -- diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 09a8d8221c..30b6371134 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -193,8 +193,6 @@ Indexes: "testpub_tbl2_pkey" PRIMARY KEY, btree (id) Publications: "testpub_foralltables" -Not-null constraints: - "testpub_tbl2_id_not_null" NOT NULL "id" \dRp+ testpub_foralltables Publication testpub_foralltables @@ -1159,8 +1157,6 @@ Publications: "testpib_ins_trunct" "testpub_default" "testpub_fortbl" -Not-null constraints: - "testpub_tbl1_id_not_null" NOT NULL "id" \dRp+ testpub_default Publication testpub_default @@ -1186,8 +1182,6 @@ Indexes: Publications: "testpib_ins_trunct" "testpub_fortbl" -Not-null constraints: - "testpub_tbl1_id_not_null" NOT NULL "id" -- verify relation cache invalidation when a primary key is added using -- an existing index diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out index cb3ef599d5..e9d7315a9c 100644 --- a/src/test/regress/expected/replica_identity.out +++ b/src/test/regress/expected/replica_identity.out @@ -174,10 +174,6 @@ Indexes: "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) -Not-null constraints: - "test_replica_identity_id_not_null" NOT NULL "id" - "test_replica_identity_keya_not_null" NOT NULL "keya" - "test_replica_identity_keyb_not_null" NOT NULL "keyb" Replica Identity: FULL ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; @@ -235,9 +231,6 @@ Indexes: -- used as replica identity. ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL; ERROR: column "id" is in index used as replica identity --- but it's OK when the identity is FULL -ALTER TABLE test_replica_identity3 REPLICA IDENTITY FULL; -ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL; -- -- Test that replica identity can be set on an index that's not yet valid. -- (This matches the way pg_dump will try to dump a partitioned table.) @@ -260,8 +253,6 @@ ALTER TABLE ONLY test_replica_identity4_1 Partition key: LIST (id) Indexes: "test_replica_identity4_pkey" PRIMARY KEY, btree (id) INVALID REPLICA IDENTITY -Not-null constraints: - "test_replica_identity4_id_not_null" NOT NULL "id" Partitions: test_replica_identity4_1 FOR VALUES IN (1) ALTER INDEX test_replica_identity4_pkey @@ -274,26 +265,11 @@ ALTER INDEX test_replica_identity4_pkey Partition key: LIST (id) Indexes: "test_replica_identity4_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY -Not-null constraints: - "test_replica_identity4_id_not_null" NOT NULL "id" Partitions: test_replica_identity4_1 FOR VALUES IN (1) --- Dropping the primary key is not allowed if that would leave the replica --- identity as nullable -CREATE TABLE test_replica_identity5 (a int not null, b int, c int, - PRIMARY KEY (b, c)); -CREATE UNIQUE INDEX test_replica_identity5_a_b_key ON test_replica_identity5 (a, b); -ALTER TABLE test_replica_identity5 REPLICA IDENTITY USING INDEX test_replica_identity5_a_b_key; -ALTER TABLE test_replica_identity5 DROP CONSTRAINT test_replica_identity5_pkey; -ERROR: column "b" is in index used as replica identity -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; -ERROR: column "b" is in index used as replica identity 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_identity_othertable; DROP TABLE test_replica_identity_t3; diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 4ccf98d8e9..319190855b 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -955,8 +955,6 @@ Policies: POLICY "pp1r" AS RESTRICTIVE TO regress_rls_dave USING ((cid < 55)) -Not-null constraints: - "part_document_dlevel_not_null" NOT NULL "dlevel" Partitions: part_document_fiction FOR VALUES FROM (11) TO (12), part_document_nonfiction FOR VALUES FROM (99) TO (100), part_document_satire FOR VALUES FROM (55) TO (56) diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 9df5a63bdf..8c8fa27a6a 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -854,6 +854,7 @@ alter table atacc1 add constraint "atacc1_pkey" primary key (test); alter table atacc1 alter column test drop not null; \d atacc1 alter table atacc1 drop constraint "atacc1_pkey"; +alter table atacc1 alter column test drop not null; \d atacc1 insert into atacc1 values (null); alter table atacc1 alter test set not null; @@ -919,6 +920,14 @@ insert into parent values (NULL); insert into child (a, b) values (NULL, 'foo'); alter table only parent alter a set not null; alter table child alter a set not null; +delete from parent; +alter table only parent alter a set not null; +insert into parent values (NULL); +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); +delete from child; +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); drop table child; drop table parent; @@ -2340,9 +2349,6 @@ CREATE TABLE atnotnull1 (); ALTER TABLE atnotnull1 ADD COLUMN a INT, ALTER a SET NOT NULL; -ALTER TABLE atnotnull1 - ADD COLUMN b INT, - ADD NOT NULL b; ALTER TABLE atnotnull1 ADD COLUMN c INT, ADD PRIMARY KEY (c); diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index e753b8c345..5ffcd4ffc7 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -196,48 +196,6 @@ INSERT INTO ATACC2 (TEST2) VALUES (3); INSERT INTO ATACC1 (TEST2) VALUES (3); DROP TABLE ATACC1 CASCADE; --- NOT NULL NO INHERIT -CREATE TABLE ATACC1 (a int, not null a no inherit); -CREATE TABLE ATACC2 () INHERITS (ATACC1); -\d+ ATACC2 -DROP TABLE ATACC1, ATACC2; -CREATE TABLE ATACC1 (a int); -ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT; -CREATE TABLE ATACC2 () INHERITS (ATACC1); -\d+ ATACC2 -DROP TABLE ATACC1, ATACC2; -CREATE TABLE ATACC1 (a int); -CREATE TABLE ATACC2 () INHERITS (ATACC1); -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); -CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); -INSERT INTO ATACC3 VALUES (null); -- make sure we scan atacc3 -ALTER TABLE ATACC2 INHERIT ATACC1; -ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; -DELETE FROM ATACC3; -ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; -\d+ ATACC[123] -ALTER TABLE ATACC2 DROP CONSTRAINT a_is_not_null; -ALTER TABLE ATACC1 DROP CONSTRAINT ditto; -\d+ ATACC3 -DROP TABLE ATACC1, ATACC2, ATACC3; - --- The same cannot be achieved this way -CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); -CREATE TABLE ATACC1 (a int, CONSTRAINT ditto NOT NULL a); -CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); -ALTER TABLE ATACC2 INHERIT ATACC1; -DROP TABLE ATACC1, ATACC2, ATACC3; - -- -- Check constraints on INSERT INTO -- @@ -598,181 +556,6 @@ ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =); DROP TABLE deferred_excl; --- verify constraints created for NOT NULL clauses -CREATE TABLE notnull_tbl1 (a INTEGER NOT NULL NOT NULL); -\d+ notnull_tbl1 -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; --- no-op -ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a; -\d+ notnull_tbl1 --- duplicate name -ALTER TABLE notnull_tbl1 ADD COLUMN b INT CONSTRAINT notnull_tbl1_a_not_null NOT NULL; --- DROP NOT NULL gets rid of both the attnotnull flag and the constraint itself -ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL; -\d notnull_tbl1 -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; --- SET NOT NULL puts both back -ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; -\d notnull_tbl1 -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; --- Doing it twice doesn't create a redundant constraint -ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; --- Using the "table constraint" syntax also works -ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL; -ALTER TABLE notnull_tbl1 ADD CONSTRAINT foobar NOT NULL a; -\d notnull_tbl1 -select conname, contype, conkey from pg_constraint where conrelid = 'notnull_tbl1'::regclass; -DROP TABLE notnull_tbl1; - --- nope -CREATE TABLE notnull_tbl2 (a INTEGER CONSTRAINT blah NOT NULL, b INTEGER CONSTRAINT blah NOT NULL); - --- can't drop not-null in primary key -CREATE TABLE notnull_tbl2 (a INTEGER PRIMARY KEY); -ALTER TABLE notnull_tbl2 ALTER a DROP NOT NULL; -DROP TABLE notnull_tbl2; - --- 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 -DROP TABLE notnull_tbl1; - -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 -BEGIN; -/* make sure the table can be put right, but roll that back */ -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 -ROLLBACK; --- Leave this table around for pg_upgrade testing - -CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL)); -ALTER TABLE notnull_tbl3 ALTER A DROP NOT NULL; -ALTER TABLE notnull_tbl3 ADD b int, ADD CONSTRAINT pk PRIMARY KEY (a, b); -\d notnull_tbl3 -ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk; -\d notnull_tbl3 - --- Primary keys in parent table cause NOT NULL constraint to spawn on their --- children. Verify that they work correctly. -CREATE TABLE cnn_parent (a int, b int); -CREATE TABLE cnn_child () INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild (NOT NULL b) INHERITS (cnn_child); -CREATE TABLE cnn_child2 (NOT NULL a NO INHERIT) INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild2 () INHERITS (cnn_grandchild, cnn_child2); - -ALTER TABLE cnn_parent ADD PRIMARY KEY (b); -\d+ cnn_grandchild -\d+ cnn_grandchild2 -ALTER TABLE cnn_parent DROP CONSTRAINT cnn_parent_pkey; -\set VERBOSITY terse -DROP TABLE cnn_parent CASCADE; -\set VERBOSITY default - --- As above, but create the primary key ahead of time -CREATE TABLE cnn_parent (a int, b int PRIMARY KEY); -CREATE TABLE cnn_child () INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild (NOT NULL b) INHERITS (cnn_child); -CREATE TABLE cnn_child2 (NOT NULL a NO INHERIT) INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild2 () INHERITS (cnn_grandchild, cnn_child2); - -ALTER TABLE cnn_parent ADD PRIMARY KEY (b); -\d+ cnn_grandchild -\d+ cnn_grandchild2 -ALTER TABLE cnn_parent DROP CONSTRAINT cnn_parent_pkey; -\set VERBOSITY terse -DROP TABLE cnn_parent CASCADE; -\set VERBOSITY default - --- As above, but create the primary key using a UNIQUE index -CREATE TABLE cnn_parent (a int, b int); -CREATE TABLE cnn_child () INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild (NOT NULL b) INHERITS (cnn_child); -CREATE TABLE cnn_child2 (NOT NULL a NO INHERIT) INHERITS (cnn_parent); -CREATE TABLE cnn_grandchild2 () INHERITS (cnn_grandchild, cnn_child2); - -CREATE UNIQUE INDEX b_uq ON cnn_parent (b); -ALTER TABLE cnn_parent ADD PRIMARY KEY USING INDEX b_uq; -\d+ cnn_grandchild -\d+ cnn_grandchild2 -ALTER TABLE cnn_parent DROP CONSTRAINT cnn_parent_pkey; --- keeps these tables around, for pg_upgrade testing - --- A primary key shouldn't attach to a unique constraint -create table cnn2_parted (a int primary key) partition by list (a); -create table cnn2_part1 (a int unique); -alter table cnn2_parted attach partition cnn2_part1 for values in (1); -\d+ cnn2_part1 -drop table cnn2_parted; - --- ensure columns in partitions are marked not-null -create table cnn2_parted(a int primary key) partition by list (a); -create table cnn2_part1(a int); -alter table cnn2_parted attach partition cnn2_part1 for values in (1); -insert into cnn2_part1 values (null); -drop table cnn2_parted, cnn2_part1; - -create table cnn2_parted(a int not null) partition by list (a); -create table cnn2_part1(a int primary key); -alter table cnn2_parted attach partition cnn2_part1 for values in (1); -drop table cnn2_parted, cnn2_part1; - -create table cnn2_parted(a int) partition by list (a); -create table cnn_part1 partition of cnn2_parted for values in (1, null); -insert into cnn_part1 values (null); -alter table cnn2_parted add primary key (a); -drop table cnn2_parted; - --- columns in regular and LIKE inheritance should be marked not-nullable --- for primary keys, even if those are deferred -CREATE TABLE notnull_tbl4 (a INTEGER PRIMARY KEY INITIALLY DEFERRED); -CREATE TABLE notnull_tbl4_lk (LIKE notnull_tbl4); -CREATE TABLE notnull_tbl4_lk2 (LIKE notnull_tbl4 INCLUDING INDEXES); -CREATE TABLE notnull_tbl4_lk3 (LIKE notnull_tbl4 INCLUDING INDEXES, CONSTRAINT a_nn NOT NULL a); -CREATE TABLE notnull_tbl4_cld () INHERITS (notnull_tbl4); -CREATE TABLE notnull_tbl4_cld2 (PRIMARY KEY (a) DEFERRABLE) INHERITS (notnull_tbl4); -CREATE TABLE notnull_tbl4_cld3 (PRIMARY KEY (a) DEFERRABLE, CONSTRAINT a_nn NOT NULL a) INHERITS (notnull_tbl4); -\d+ notnull_tbl4 -\d+ notnull_tbl4_lk -\d+ notnull_tbl4_lk2 -\d+ notnull_tbl4_lk3 -\d+ notnull_tbl4_cld -\d+ notnull_tbl4_cld2 -\d+ notnull_tbl4_cld3 --- leave these tables around for pg_upgrade testing - --- also, if a NOT NULL is dropped underneath a deferrable PK, the column --- should still be nullable afterwards. This mimics what pg_dump does. -CREATE TABLE notnull_tbl5 (a INTEGER CONSTRAINT a_nn NOT NULL); -ALTER TABLE notnull_tbl5 ADD PRIMARY KEY (a) DEFERRABLE; -ALTER TABLE notnull_tbl5 DROP CONSTRAINT a_nn; -\d+ notnull_tbl5 -DROP TABLE notnull_tbl5; - -- Comments -- Setup a low-level role to enforce non-superuser checks. CREATE ROLE regress_constraint_comments; diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql index e5b7b18b91..5f1f4b80c9 100644 --- a/src/test/regress/sql/indexing.sql +++ b/src/test/regress/sql/indexing.sql @@ -667,10 +667,9 @@ create table idxpart (a int) partition by range (a); create table idxpart0 (like idxpart); alter table idxpart0 add unique (a); alter table idxpart attach partition idxpart0 default; -alter table only idxpart add primary key (a); -- works, but idxpart0.a is nullable -\d idxpart0 -alter index idxpart_pkey attach partition idxpart0_a_key; -- fails, lacks NOT NULL +alter table only idxpart add primary key (a); -- fail, no not-null constraint alter table idxpart0 alter column a set not null; +alter table only idxpart add primary key (a); -- now it works alter index idxpart_pkey attach partition idxpart0_a_key; alter table idxpart0 alter column a drop not null; -- fail, pkey needs it drop table idxpart; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 2205e59aff..e3bcfdb181 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -759,235 +759,6 @@ select * from cnullparent; select * from cnullparent where f1 = 2; drop table cnullparent cascade; --- --- Test inheritance of NOT NULL constraints --- -create table pp1 (f1 int); -create table cc1 (f2 text, f3 int) inherits (pp1); -\d cc1 -create table cc2(f4 float) inherits(pp1,cc1); -\d cc2 - --- named NOT NULL constraint -alter table cc1 add column a2 int constraint nn not null; -\d+ cc1 -\d+ cc2 -alter table pp1 alter column f1 set not null; -\d+ pp1 -\d+ cc1 -\d+ cc2 - --- cannot create table with inconsistent NO INHERIT constraint -create table cc3 (a2 int not null no inherit) inherits (cc1); - --- change NO INHERIT status of inherited constraint: no dice, it's inherited -alter table cc2 add not null a2 no inherit; - --- remove constraint from cc2: no dice, it's inherited -alter table cc2 alter column a2 drop not null; - --- remove constraint cc1, should succeed -alter table cc1 alter column a2 drop not null; -\d+ cc1 - --- same for cc2 -alter table cc2 alter column f1 drop not null; -\d+ cc2 - --- remove from cc1, should fail again -alter table cc1 alter column f1 drop not null; - --- remove from pp1, should succeed -alter table pp1 alter column f1 drop not null; -\d+ pp1 - -alter table pp1 add primary key (f1); --- Leave these tables around, for pg_upgrade testing - --- Test a not-null addition that must walk down the hierarchy -CREATE TABLE inh_parent (); -CREATE TABLE inh_child (i int) INHERITS (inh_parent); -CREATE TABLE inh_grandchild () INHERITS (inh_parent, inh_child); -ALTER TABLE inh_parent ADD COLUMN i int NOT NULL; -drop table inh_parent, inh_child, inh_grandchild; - --- Test the same constraint name for different columns in different parents -create table inh_parent1(a int constraint nn not null); -create table inh_parent2(b int constraint nn not null); -create table inh_child () inherits (inh_parent1, inh_parent2); -\d+ inh_child -drop table inh_parent1, inh_parent2, inh_child; - --- Test multiple parents with overlapping primary keys -create table inh_parent1(a int, b int, c int, primary key (a, b)); -create table inh_parent2(d int, e int, b int, primary key (d, b)); -create table inh_child() inherits (inh_parent1, inh_parent2); -select conrelid::regclass, conname, contype, conkey, - coninhcount, conislocal, connoinherit - from pg_constraint where contype in ('n','p') and - conrelid::regclass::text in ('inh_child', 'inh_parent1', 'inh_parent2') - order by 1, 2; -\d+ inh_child -drop table inh_parent1, inh_parent2, inh_child; - --- NOT NULL NO INHERIT -create table inh_nn_parent(a int); -create table inh_nn_child() inherits (inh_nn_parent); -alter table inh_nn_parent add not null a no inherit; -create table inh_nn_child2() inherits (inh_nn_parent); -select conrelid::regclass, conname, contype, conkey, - (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]), - coninhcount, conislocal, connoinherit - from pg_constraint where contype = 'n' and - conrelid::regclass::text like 'inh\_nn\_%' - order by 2, 1; -\d+ inh_nn* -drop table inh_nn_parent, inh_nn_child, inh_nn_child2; - -CREATE TABLE inh_nn_parent (a int, NOT NULL a NO INHERIT); -CREATE TABLE inh_nn_child() INHERITS (inh_nn_parent); -ALTER TABLE inh_nn_parent ADD CONSTRAINT nna NOT NULL a; -ALTER TABLE inh_nn_parent ALTER a SET NOT NULL; -DROP TABLE inh_nn_parent cascade; - --- Adding a PK at the top level of a hierarchy should cause all descendants --- to be checked for nulls, even past a no-inherit constraint -CREATE TABLE inh_nn_lvl1 (a int); -CREATE TABLE inh_nn_lvl2 () INHERITS (inh_nn_lvl1); -CREATE TABLE inh_nn_lvl3 (CONSTRAINT foo NOT NULL a NO INHERIT) INHERITS (inh_nn_lvl2); -CREATE TABLE inh_nn_lvl4 () INHERITS (inh_nn_lvl3); -CREATE TABLE inh_nn_lvl5 () INHERITS (inh_nn_lvl4); -INSERT INTO inh_nn_lvl2 VALUES (NULL); -ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a); -DELETE FROM inh_nn_lvl2; -INSERT INTO inh_nn_lvl5 VALUES (NULL); -ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a); -DROP TABLE inh_nn_lvl1 CASCADE; - --- --- test inherit/deinherit --- -create table inh_parent(f1 int); -create table inh_child1(f1 int not null); -create table inh_child2(f1 int); - --- inh_child1 should have not null constraint -alter table inh_child1 inherit inh_parent; - --- should fail, missing NOT NULL constraint -alter table inh_child2 inherit inh_child1; - -alter table inh_child2 alter column f1 set not null; -alter table inh_child2 inherit inh_child1; - --- add NOT NULL constraint recursively -alter table inh_parent alter column f1 set not null; - -\d+ inh_parent -\d+ inh_child1 -\d+ inh_child2 - -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass) - order by 2, 1; - --- --- test deinherit procedure --- - --- deinherit inh_child1 -create table inh_child3 () inherits (inh_child1); -alter table inh_child1 no inherit inh_parent; -\d+ inh_parent -\d+ inh_child1 -\d+ inh_child2 -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid::regclass::text in ('inh_parent', 'inh_child1', 'inh_child2', 'inh_child3') - order by 2, 1; -drop table inh_parent, inh_child1, inh_child2, inh_child3; - --- a PK in parent must have a not-null in child that it can mark inherited -create table inh_parent (a int primary key); -create table inh_child (a int primary key); -alter table inh_child inherit inh_parent; -- nope -alter table inh_child alter a set not null; -alter table inh_child inherit inh_parent; -- now it works - --- don't interfere with other types of constraints -alter table inh_parent add constraint inh_parent_excl exclude ((1) with =); -alter table inh_parent add constraint inh_parent_uq unique (a); -alter table inh_parent add constraint inh_parent_fk foreign key (a) references inh_parent (a); -create table inh_child2 () inherits (inh_parent); -create table inh_child3 (like inh_parent); -alter table inh_child3 inherit inh_parent; -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint - where conrelid::regclass::text in ('inh_parent', 'inh_child', 'inh_child2', 'inh_child3') - order by 2, 1; - -drop table inh_parent, inh_child, inh_child2, inh_child3; - --- --- test multi inheritance tree --- -create table inh_parent(f1 int not null); -create table inh_child1() inherits(inh_parent); -create table inh_child2() inherits(inh_parent); -create table inh_child3() inherits(inh_child1, inh_child2); - --- show constraint info -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass, 'inh_child3'::regclass) - order by 2, conrelid::regclass::text; - -drop table inh_parent cascade; - --- test child table with inherited columns and --- with explicitly specified not null constraints -create table inh_parent_1(f1 int); -create table inh_parent_2(f2 text); -create table inh_child(f1 int not null, f2 text not null) inherits(inh_parent_1, inh_parent_2); - --- show constraint info -select conrelid::regclass, conname, contype, coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid in ('inh_parent_1'::regclass, 'inh_parent_2'::regclass, 'inh_child'::regclass) - order by 2, conrelid::regclass::text; - --- also drops inh_child table -drop table inh_parent_1 cascade; -drop table inh_parent_2; - --- test multi layer inheritance tree -create table inh_p1(f1 int not null); -create table inh_p2(f1 int not null); -create table inh_p3(f2 int); -create table inh_p4(f1 int not null, f3 text not null); - -create table inh_multiparent() inherits(inh_p1, inh_p2, inh_p3, inh_p4); - --- constraint on f1 should have three parents -select conrelid::regclass, contype, conname, - (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]), - coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid::regclass in ('inh_p1', 'inh_p2', 'inh_p3', 'inh_p4', - 'inh_multiparent') - order by conrelid::regclass::text, conname; - -create table inh_multiparent2 (a int not null, f1 int) inherits(inh_p3, inh_multiparent); -select conrelid::regclass, contype, conname, - (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]), - coninhcount, conislocal - from pg_constraint where contype = 'n' and - conrelid::regclass in ('inh_p3', 'inh_multiparent', 'inh_multiparent2') - order by conrelid::regclass::text, conname; - -drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade; - -- -- Mixed ownership inheritance tree -- diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql index 30daec05b7..039cca25e8 100644 --- a/src/test/regress/sql/replica_identity.sql +++ b/src/test/regress/sql/replica_identity.sql @@ -100,9 +100,6 @@ ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint; -- ALTER TABLE DROP NOT NULL is not allowed for columns part of an index -- used as replica identity. ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL; --- but it's OK when the identity is FULL -ALTER TABLE test_replica_identity3 REPLICA IDENTITY FULL; -ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL; -- -- Test that replica identity can be set on an index that's not yet valid. @@ -123,21 +120,9 @@ ALTER INDEX test_replica_identity4_pkey ATTACH PARTITION test_replica_identity4_1_pkey; \d+ test_replica_identity4 --- Dropping the primary key is not allowed if that would leave the replica --- identity as nullable -CREATE TABLE test_replica_identity5 (a int not null, b int, c int, - PRIMARY KEY (b, c)); -CREATE UNIQUE INDEX test_replica_identity5_a_b_key ON test_replica_identity5 (a, b); -ALTER TABLE test_replica_identity5 REPLICA IDENTITY USING INDEX test_replica_identity5_a_b_key; -ALTER TABLE test_replica_identity5 DROP CONSTRAINT test_replica_identity5_pkey; -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; - 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_identity_othertable; DROP TABLE test_replica_identity_t3; -- 2.30.2