From fae090a20dcdc98a7d9c77ccd03e3f2185e107a6 Mon Sep 17 00:00:00 2001 From: jian he Date: Tue, 10 Jun 2025 13:21:44 +0800 Subject: [PATCH v42 1/1] fix MERGE PARTITION with partitioned table not enforced/valid constr add tests for MERGE PARTITION while partitioned table have * not valid not-null constraint * not enforced check constraint * not valid check constraint * not enforced foreign key constraint * not valid foreign key constraint also changed createTableConstraints to make ALTER TABLE MERGE PARTITION support these (not valid, not enforced) constraints. --- src/backend/catalog/pg_constraint.c | 2 +- src/backend/commands/tablecmds.c | 10 ++- src/test/regress/expected/partition_merge.out | 62 ++++++++++++++++++- src/test/regress/sql/partition_merge.sql | 29 +++++++++ 4 files changed, 98 insertions(+), 5 deletions(-) diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 2d5ac1ea813..1f948876d98 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -875,7 +875,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh) false))); constr->is_enforced = true; constr->skip_validation = !conForm->convalidated; - constr->initially_valid = true; + constr->initially_valid = conForm->convalidated; constr->is_no_inherit = conForm->connoinherit; notnulls = lappend(notnulls, constr); } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d6ce87dd965..0375e5251f5 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -22215,6 +22215,7 @@ createTableConstraints(Relation modelRel, Relation newRel) char *ccbin = constr->check[ccnum].ccbin; bool ccenforced = constr->check[ccnum].ccenforced; bool ccnoinherit = constr->check[ccnum].ccnoinherit; + bool ccvalid = constr->check[ccnum].ccvalid; Node *ccbin_node; bool found_whole_row; CookedConstraint *cooked; @@ -22245,7 +22246,7 @@ createTableConstraints(Relation modelRel, Relation newRel) cooked->attnum = 0; cooked->expr = ccbin_node; cooked->is_enforced = ccenforced; - cooked->skip_validation = false; + cooked->skip_validation = !ccvalid; cooked->is_local = true; cooked->inhcount = 0; cooked->is_no_inherit = ccnoinherit; @@ -22267,7 +22268,12 @@ createTableConstraints(Relation modelRel, Relation newRel) false, true); Assert(list_length(nnconstraints) > 0); - AddRelationNotNullConstraints(newRel, nnconstraints, NULL); + + /* + * we already set pg_attribute.attnotnull in createPartitionTable. no + * need call set_attnotnull again. + */ + AddRelationNewConstraints(newRel, NIL, nnconstraints, false, true, true, NULL); } } diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out index f754849039e..fb7413d9fed 100644 --- a/src/test/regress/expected/partition_merge.out +++ b/src/test/regress/expected/partition_merge.out @@ -1032,20 +1032,27 @@ CREATE STATISTICS t_stat (DEPENDENCIES) on i, b from t; CREATE STATISTICS tp_0_1_stat (DEPENDENCIES) on i, b from tp_0_1; CREATE STATISTICS tp_1_2_stat (DEPENDENCIES) on i, b from tp_1_2; ALTER TABLE t ADD CONSTRAINT t_b_check CHECK (b > 0); +ALTER TABLE t ADD CONSTRAINT t_b_check1 CHECK (b > 0) NOT ENFORCED; +ALTER TABLE t ADD CONSTRAINT t_b_check2 CHECK (b > 0) NOT VALID; +ALTER TABLE t ADD CONSTRAINT t_b_nn NOT NULL b NOT VALID; \d+ tp_0_1 Table "partitions_merge_schema.tp_0_1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+-------------------------------------------------+---------+--------------+------------- i | integer | | | | plain | | tp_0_1.i t | text | | | 'default_tp_0_1'::text | main | | - b | bigint | | | | plain | | + b | bigint | | not null | | plain | | d | date | | | generated always as ('02-02-2022'::date) stored | plain | | Partition of: t FOR VALUES FROM (0) TO (1) Partition constraint: ((abs(i) IS NOT NULL) AND (abs(i) >= 0) AND (abs(i) < 1)) Check constraints: "t_b_check" CHECK (b > 0) + "t_b_check1" CHECK (b > 0) NOT ENFORCED + "t_b_check2" CHECK (b > 0) NOT VALID Statistics objects: "partitions_merge_schema.tp_0_1_stat" (dependencies) ON i, b FROM tp_0_1 +Not-null constraints: + "t_b_nn" NOT NULL "b" (inherited) NOT VALID ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; \d+ tp_0_1 @@ -1054,14 +1061,65 @@ ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; --------+---------+-----------+----------+-------------------------------------------------+----------+--------------+------------- i | integer | | | | plain | | t | text | | | 'default_t'::text | extended | | - b | bigint | | | | plain | | + b | bigint | | not null | | plain | | d | date | | | generated always as ('01-01-2022'::date) stored | plain | | Partition of: t FOR VALUES FROM (0) TO (2) Partition constraint: ((abs(i) IS NOT NULL) AND (abs(i) >= 0) AND (abs(i) < 2)) Check constraints: "t_b_check" CHECK (b > 0) + "t_b_check1" CHECK (b > 0) NOT ENFORCED + "t_b_check2" CHECK (b > 0) NOT VALID +Not-null constraints: + "t_b_nn" NOT NULL "b" (inherited) NOT VALID DROP TABLE t; +-- Test MERGE PARTITIONS with not valid or not enforced foreign key constraint +CREATE TABLE pk (i INT PRIMARY KEY) PARTITION BY RANGE (i); +CREATE TABLE pk_1 PARTITION OF pk FOR VALUES FROM (0) TO (1); +CREATE TABLE pk_2 PARTITION OF pk FOR VALUES FROM (1) TO (2); +INSERT INTO pk VALUES (0), (1); +CREATE TABLE fk (i INT); +INSERT INTO fk VALUES (1), (2); +ALTER TABLE fk ADD CONSTRAINT fk_i_fkey FOREIGN KEY (i) REFERENCES pk NOT VALID; +ALTER TABLE pk MERGE PARTITIONS (pk_1, pk_2) INTO pk12; +\d pk12 + Table "partitions_merge_schema.pk12" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + i | integer | | not null | +Partition of: pk FOR VALUES FROM (0) TO (2) +Indexes: + "pk12_pkey" PRIMARY KEY, btree (i) +Referenced by: + TABLE "fk" CONSTRAINT "fk_i_fkey" FOREIGN KEY (i) REFERENCES pk(i) NOT VALID + +ALTER TABLE fk validate constraint fk_i_fkey; --error +ERROR: insert or update on table "fk" violates foreign key constraint "fk_i_fkey" +DETAIL: Key (i)=(2) is not present in table "pk". +DROP TABLE IF EXISTS fk, pk CASCADE; +CREATE TABLE pk (i INT PRIMARY KEY) PARTITION BY RANGE (i); +CREATE TABLE pk_1 PARTITION OF pk FOR VALUES FROM (0) TO (1); +CREATE TABLE pk_2 PARTITION OF pk FOR VALUES FROM (1) TO (2); +INSERT INTO pk VALUES (0), (1); +CREATE TABLE fk (i INT); +INSERT INTO fk VALUES (1), (2); +ALTER TABLE fk ADD CONSTRAINT fk_i_fkey FOREIGN KEY (i) REFERENCES pk NOT ENFORCED; +ALTER TABLE pk MERGE PARTITIONS (pk_1, pk_2) INTO pk12; +\d pk12 + Table "partitions_merge_schema.pk12" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + i | integer | | not null | +Partition of: pk FOR VALUES FROM (0) TO (2) +Indexes: + "pk12_pkey" PRIMARY KEY, btree (i) +Referenced by: + TABLE "fk" CONSTRAINT "fk_i_fkey" FOREIGN KEY (i) REFERENCES pk(i) NOT ENFORCED + +ALTER TABLE fk ALTER CONSTRAINT fk_i_fkey ENFORCED; --error +ERROR: insert or update on table "fk" violates foreign key constraint "fk_i_fkey" +DETAIL: Key (i)=(2) is not present in table "pk". +DROP TABLE IF EXISTS fk, pk CASCADE; RESET search_path; -- DROP SCHEMA partitions_merge_schema; diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql index f8324f01e5c..df424e42d10 100644 --- a/src/test/regress/sql/partition_merge.sql +++ b/src/test/regress/sql/partition_merge.sql @@ -693,6 +693,9 @@ CREATE STATISTICS tp_0_1_stat (DEPENDENCIES) on i, b from tp_0_1; CREATE STATISTICS tp_1_2_stat (DEPENDENCIES) on i, b from tp_1_2; ALTER TABLE t ADD CONSTRAINT t_b_check CHECK (b > 0); +ALTER TABLE t ADD CONSTRAINT t_b_check1 CHECK (b > 0) NOT ENFORCED; +ALTER TABLE t ADD CONSTRAINT t_b_check2 CHECK (b > 0) NOT VALID; +ALTER TABLE t ADD CONSTRAINT t_b_nn NOT NULL b NOT VALID; \d+ tp_0_1 ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; @@ -700,6 +703,32 @@ ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; DROP TABLE t; +-- Test MERGE PARTITIONS with not valid or not enforced foreign key constraint +CREATE TABLE pk (i INT PRIMARY KEY) PARTITION BY RANGE (i); +CREATE TABLE pk_1 PARTITION OF pk FOR VALUES FROM (0) TO (1); +CREATE TABLE pk_2 PARTITION OF pk FOR VALUES FROM (1) TO (2); +INSERT INTO pk VALUES (0), (1); +CREATE TABLE fk (i INT); +INSERT INTO fk VALUES (1), (2); +ALTER TABLE fk ADD CONSTRAINT fk_i_fkey FOREIGN KEY (i) REFERENCES pk NOT VALID; +ALTER TABLE pk MERGE PARTITIONS (pk_1, pk_2) INTO pk12; +\d pk12 +ALTER TABLE fk validate constraint fk_i_fkey; --error + +DROP TABLE IF EXISTS fk, pk CASCADE; +CREATE TABLE pk (i INT PRIMARY KEY) PARTITION BY RANGE (i); +CREATE TABLE pk_1 PARTITION OF pk FOR VALUES FROM (0) TO (1); +CREATE TABLE pk_2 PARTITION OF pk FOR VALUES FROM (1) TO (2); +INSERT INTO pk VALUES (0), (1); +CREATE TABLE fk (i INT); +INSERT INTO fk VALUES (1), (2); + +ALTER TABLE fk ADD CONSTRAINT fk_i_fkey FOREIGN KEY (i) REFERENCES pk NOT ENFORCED; +ALTER TABLE pk MERGE PARTITIONS (pk_1, pk_2) INTO pk12; +\d pk12 + +ALTER TABLE fk ALTER CONSTRAINT fk_i_fkey ENFORCED; --error +DROP TABLE IF EXISTS fk, pk CASCADE; RESET search_path; -- 2.34.1