v42-0001-fix-MERGE-PARTITION-with-partitioned-table-not-enforc.no-cfbot
application/octet-stream
Filename: v42-0001-fix-MERGE-PARTITION-with-partitioned-table-not-enforc.no-cfbot
Type: application/octet-stream
Part: 0
From fae090a20dcdc98a7d9c77ccd03e3f2185e107a6 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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