From 1a5517a57ac5f3dd083b4814d403e35f26a542f5 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Tue, 19 Dec 2023 11:38:48 +0530 Subject: [PATCH 10/27] Drop identity property when detaching partition A partition's identity column shares the identity space (i.e. underlying sequence) as the corresponding column of the partitioned table. If a partition is detached it can longer share the identity space as before. Hence the identity columns of the partition being detached loose their identity property. When identity of a column of a regular table is dropped it retains the NOT NULL constarint that came with the identity property. Similarly the columns of the partition being detached retain the NOT NULL constraints that came with identity property, even though the identity property itself is lost. Also note that the sequence associated with the identity property is linked to the partitioned table (and not the partition being detached). That sequence is not dropped as part of detach operation. Ashutosh Bapat --- src/backend/commands/tablecmds.c | 13 +++++++++++ src/test/regress/expected/identity.out | 30 ++++++++++++++++++++++++++ src/test/regress/sql/identity.sql | 10 +++++++++ 3 files changed, 53 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 67b38c5101..19badb3fba 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -19498,6 +19498,7 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, HeapTuple tuple, newtuple; Relation trigrel = NULL; + int attno; if (concurrent) { @@ -19663,6 +19664,18 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, heap_freetuple(newtuple); table_close(classRel, RowExclusiveLock); + /* + * Drop identity property from all identity columns of partition. + */ + for (attno = 0; attno < RelationGetNumberOfAttributes(partRel); attno++) + { + Form_pg_attribute attr = TupleDescAttr(partRel->rd_att, attno); + + if (!attr->attisdropped && attr->attidentity) + ATExecDropIdentity(partRel, NameStr(attr->attname), false, + AccessExclusiveLock, true, true); + } + if (OidIsValid(defaultPartOid)) { /* diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index e185e0d4a1..501fcc22c6 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -615,6 +615,36 @@ SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; pitest2_p2 | 08-06-2016 | from pitest2_p2 | 300 (10 rows) +-- detaching a partition removes identity property +ALTER TABLE pitest2 DETACH PARTITION pitest2_p1; +INSERT into pitest2(f1, f2) VALUES ('2016-08-7', 'from pitest2'); +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-7', 'from pitest2_p1'); -- error +ERROR: null value in column "f3" of relation "pitest2_p1" violates not-null constraint +DETAIL: Failing row contains (07-07-2016, from pitest2_p1, null). +INSERT into pitest2_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest2_p1', 2000); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+------ + pitest2_p2 | 08-02-2016 | from pitest2 | 2 + pitest2_p2 | 08-03-2016 | from pitest2_p2 | 4 + pitest2_p2 | 08-04-2016 | from pitest2 | 6 + pitest2_p2 | 08-05-2016 | from pitest2 | 1000 + pitest2_p2 | 08-06-2016 | from pitest2_p2 | 300 + pitest2_p2 | 08-07-2016 | from pitest2 | 1004 +(6 rows) + +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2_p1; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+------ + pitest2_p1 | 07-02-2016 | from pitest2 | 1 + pitest2_p1 | 07-03-2016 | from pitest2_p1 | 3 + pitest2_p1 | 07-04-2016 | from pitest2 | 5 + pitest2_p1 | 07-05-2016 | from pitest2 | 200 + pitest2_p1 | 07-06-2016 | from pitest2_p1 | 1002 + pitest2_p1 | 07-07-2016 | from pitest2_p1 | 2000 +(6 rows) + +DROP TABLE pitest2_p1; -- changing a regular column to identity column in a partitioned table CREATE TABLE pitest3 (f1 date NOT NULL, f2 text, f3 int) PARTITION BY RANGE (f1); CREATE TABLE pitest3_p1 PARTITION OF pitest3 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql index a1862df1d8..0d7ecfb3e0 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -373,6 +373,16 @@ INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-6', 'from pitest2_p1'); INSERT INTO pitest2_p2 (f1, f2, f3) VALUES ('2016-08-6', 'from pitest2_p2', 300); SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; +-- detaching a partition removes identity property +ALTER TABLE pitest2 DETACH PARTITION pitest2_p1; +INSERT into pitest2(f1, f2) VALUES ('2016-08-7', 'from pitest2'); +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-7', 'from pitest2_p1'); -- error +INSERT into pitest2_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest2_p1', 2000); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2_p1; + +DROP TABLE pitest2_p1; + -- changing a regular column to identity column in a partitioned table CREATE TABLE pitest3 (f1 date NOT NULL, f2 text, f3 int) PARTITION BY RANGE (f1); CREATE TABLE pitest3_p1 PARTITION OF pitest3 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- 2.25.1