From: amit Date: Thu, 16 Feb 2017 15:56:44 +0900 Subject: [PATCH] Allow dropping partitioned table without CASCADE Currently, a normal dependency is created between a inheritance parent and child when creating the child. That means one must specify CASCADE to drop the parent table if a child table exists. When creating partitions as inheritance children, create auto dependency instead, so that partitions are dropped automatically when the parent is dropped i.e., without specifying CASCADE. --- src/backend/commands/tablecmds.c | 26 ++++++++++++++++++-------- src/test/regress/expected/alter_table.out | 10 ++++------ src/test/regress/expected/create_table.out | 9 ++------- src/test/regress/expected/inherit.out | 18 ------------------ src/test/regress/expected/insert.out | 7 ++----- src/test/regress/expected/update.out | 5 ----- src/test/regress/sql/alter_table.sql | 10 ++++------ src/test/regress/sql/create_table.sql | 9 ++------- src/test/regress/sql/insert.sql | 7 ++----- 9 files changed, 34 insertions(+), 67 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f33aa70da6..27b6556a71 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -289,9 +289,11 @@ static List *MergeAttributes(List *schema, List *supers, char relpersistence, static bool MergeCheckConstraint(List *constraints, char *name, Node *expr); static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel); static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel); -static void StoreCatalogInheritance(Oid relationId, List *supers); +static void StoreCatalogInheritance(Oid relationId, List *supers, + bool child_is_partition); static void StoreCatalogInheritance1(Oid relationId, Oid parentOid, - int16 seqNumber, Relation inhRelation); + int16 seqNumber, Relation inhRelation, + bool child_is_partition); static int findAttrByName(const char *attributeName, List *schema); static void AlterIndexNamespaces(Relation classRel, Relation rel, Oid oldNspOid, Oid newNspOid, ObjectAddresses *objsMoved); @@ -730,7 +732,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, typaddress); /* Store inheritance information for new rel. */ - StoreCatalogInheritance(relationId, inheritOids); + StoreCatalogInheritance(relationId, inheritOids, stmt->partbound != NULL); /* * We must bump the command counter to make the newly-created relation @@ -2245,7 +2247,8 @@ MergeCheckConstraint(List *constraints, char *name, Node *expr) * supers is a list of the OIDs of the new relation's direct ancestors. */ static void -StoreCatalogInheritance(Oid relationId, List *supers) +StoreCatalogInheritance(Oid relationId, List *supers, + bool child_is_partition) { Relation relation; int16 seqNumber; @@ -2275,7 +2278,8 @@ StoreCatalogInheritance(Oid relationId, List *supers) { Oid parentOid = lfirst_oid(entry); - StoreCatalogInheritance1(relationId, parentOid, seqNumber, relation); + StoreCatalogInheritance1(relationId, parentOid, seqNumber, relation, + child_is_partition); seqNumber++; } @@ -2288,7 +2292,8 @@ StoreCatalogInheritance(Oid relationId, List *supers) */ static void StoreCatalogInheritance1(Oid relationId, Oid parentOid, - int16 seqNumber, Relation inhRelation) + int16 seqNumber, Relation inhRelation, + bool child_is_partition) { TupleDesc desc = RelationGetDescr(inhRelation); Datum values[Natts_pg_inherits]; @@ -2322,7 +2327,10 @@ StoreCatalogInheritance1(Oid relationId, Oid parentOid, childobject.objectId = relationId; childobject.objectSubId = 0; - recordDependencyOn(&childobject, &parentobject, DEPENDENCY_NORMAL); + if (!child_is_partition) + recordDependencyOn(&childobject, &parentobject, DEPENDENCY_NORMAL); + else + recordDependencyOn(&childobject, &parentobject, DEPENDENCY_AUTO); /* * Post creation hook of this inheritance. Since object_access_hook @@ -10749,7 +10757,9 @@ CreateInheritance(Relation child_rel, Relation parent_rel) StoreCatalogInheritance1(RelationGetRelid(child_rel), RelationGetRelid(parent_rel), inhseqno + 1, - catalogRelation); + catalogRelation, + parent_rel->rd_rel->relkind == + RELKIND_PARTITIONED_TABLE); /* Now we're done with pg_inherits */ heap_close(catalogRelation, RowExclusiveLock); diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index b0e80a7788..0d1554a73e 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3334,10 +3334,8 @@ ALTER TABLE list_parted2 DROP COLUMN b; ERROR: cannot drop column named in partition key ALTER TABLE list_parted2 ALTER COLUMN b TYPE text; ERROR: cannot alter type of column named in partition key --- cleanup: avoid using CASCADE -DROP TABLE list_parted, part_1; -DROP TABLE list_parted2, part_2, part_5, part_5_a; -DROP TABLE range_parted, part1, part2; +-- cleanup +DROP TABLE list_parted, list_parted2, range_parted; -- more tests for certain multi-level partitioning scenarios create table p (a int, b int) partition by range (a, b); create table p1 (b int, a int not null) partition by range (b); @@ -3366,5 +3364,5 @@ insert into p1 (a, b) values (2, 3); -- check that partition validation scan correctly detects violating rows alter table p attach partition p1 for values from (1, 2) to (1, 10); ERROR: partition constraint is violated by some row --- cleanup: avoid using CASCADE -drop table p, p1, p11; +-- cleanup +drop table p; diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index fc92cd92dd..bfad755e32 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -659,10 +659,5 @@ Check constraints: "check_a" CHECK (length(a) > 0) Number of partitions: 3 (Use \d+ to list them.) --- cleanup: avoid using CASCADE -DROP TABLE parted, part_a, part_b, part_c, part_c_1_10; -DROP TABLE list_parted, part_1, part_2, part_null; -DROP TABLE range_parted; -DROP TABLE list_parted2, part_ab, part_null_z; -DROP TABLE range_parted2, part0, part1, part2, part3; -DROP TABLE range_parted3, part00, part10, part11, part12; +-- cleanup +DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index a8c8b28a75..623aa1db93 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1844,22 +1844,4 @@ explain (costs off) select * from range_list_parted where a >= 30; (11 rows) drop table list_parted cascade; -NOTICE: drop cascades to 3 other objects -DETAIL: drop cascades to table part_ab_cd -drop cascades to table part_ef_gh -drop cascades to table part_null_xy drop table range_list_parted cascade; -NOTICE: drop cascades to 13 other objects -DETAIL: drop cascades to table part_1_10 -drop cascades to table part_1_10_ab -drop cascades to table part_1_10_cd -drop cascades to table part_10_20 -drop cascades to table part_10_20_ab -drop cascades to table part_10_20_cd -drop cascades to table part_21_30 -drop cascades to table part_21_30_ab -drop cascades to table part_21_30_cd -drop cascades to table part_40_inf -drop cascades to table part_40_inf_ab -drop cascades to table part_40_inf_cd -drop cascades to table part_40_inf_null diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index 81af3ef497..31cfa4e76e 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -314,10 +314,7 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p (9 rows) -- cleanup -drop table part1, part2, part3, part4, range_parted; -drop table part_ee_ff3_1, part_ee_ff3_2, part_ee_ff1, part_ee_ff2, part_ee_ff3; -drop table part_ee_ff, part_gg2_2, part_gg2_1, part_gg2, part_gg1, part_gg; -drop table part_aa_bb, part_cc_dd, part_null, list_parted; +drop table range_parted, list_parted; -- more tests for certain multi-level partitioning scenarios create table p (a int, b int) partition by range (a, b); create table p1 (b int not null, a int not null) partition by range ((b+0)); @@ -387,4 +384,4 @@ with ins (a, b, c) as (5 rows) -- cleanup -drop table p, p1, p11, p12, p2, p3, p4; +drop table p; diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index a1e9255450..af0d5bfffe 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -220,8 +220,3 @@ DETAIL: Failing row contains (b, 9). update range_parted set b = b + 1 where b = 10; -- cleanup drop table range_parted cascade; -NOTICE: drop cascades to 4 other objects -DETAIL: drop cascades to table part_a_1_a_10 -drop cascades to table part_a_10_a_20 -drop cascades to table part_b_1_b_10 -drop cascades to table part_b_10_b_20 diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 7513769359..0787e785ce 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2194,10 +2194,8 @@ ALTER TABLE part_2 INHERIT inh_test; ALTER TABLE list_parted2 DROP COLUMN b; ALTER TABLE list_parted2 ALTER COLUMN b TYPE text; --- cleanup: avoid using CASCADE -DROP TABLE list_parted, part_1; -DROP TABLE list_parted2, part_2, part_5, part_5_a; -DROP TABLE range_parted, part1, part2; +-- cleanup +DROP TABLE list_parted, list_parted2, range_parted; -- more tests for certain multi-level partitioning scenarios create table p (a int, b int) partition by range (a, b); @@ -2222,5 +2220,5 @@ insert into p1 (a, b) values (2, 3); -- check that partition validation scan correctly detects violating rows alter table p attach partition p1 for values from (1, 2) to (1, 10); --- cleanup: avoid using CASCADE -drop table p, p1, p11; +-- cleanup +drop table p; diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 5f25c436ee..553c084a10 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -593,10 +593,5 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); -- returned. \d parted --- cleanup: avoid using CASCADE -DROP TABLE parted, part_a, part_b, part_c, part_c_1_10; -DROP TABLE list_parted, part_1, part_2, part_null; -DROP TABLE range_parted; -DROP TABLE list_parted2, part_ab, part_null_z; -DROP TABLE range_parted2, part0, part1, part2, part3; -DROP TABLE range_parted3, part00, part10, part11, part12; +-- cleanup +DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3; diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index 454e1ce2e7..dfdc24eba8 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -186,10 +186,7 @@ insert into list_parted (b) values (1); select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1; -- cleanup -drop table part1, part2, part3, part4, range_parted; -drop table part_ee_ff3_1, part_ee_ff3_2, part_ee_ff1, part_ee_ff2, part_ee_ff3; -drop table part_ee_ff, part_gg2_2, part_gg2_1, part_gg2, part_gg1, part_gg; -drop table part_aa_bb, part_cc_dd, part_null, list_parted; +drop table range_parted, list_parted; -- more tests for certain multi-level partitioning scenarios create table p (a int, b int) partition by range (a, b); @@ -241,4 +238,4 @@ with ins (a, b, c) as select a, b, min(c), max(c) from ins group by a, b order by 1; -- cleanup -drop table p, p1, p11, p12, p2, p3, p4; +drop table p; -- 2.11.0 --------------5730E3B4E532BCB0A73A0493 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------5730E3B4E532BCB0A73A0493--