agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH 1/2] Allow dropping partitioned table without CASCADE 7+ messages / 2 participants [nested] [flat]
* [PATCH 1/2] Allow dropping partitioned table without CASCADE @ 2017-02-16 06:56 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: amit @ 2017-02-16 06:56 UTC (permalink / raw) 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 3cea220421..31b50ad77f 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); @@ -725,7 +727,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 @@ -2240,7 +2242,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; @@ -2270,7 +2273,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++; } @@ -2283,7 +2287,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]; @@ -2317,7 +2322,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 @@ -10744,7 +10752,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 e84af67fb2..ca66158ee3 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3339,10 +3339,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); @@ -3371,5 +3369,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 20eb3d35f9..c07a474b3d 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -667,10 +667,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 a403fd8cb4..fbcc739f41 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2199,10 +2199,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); @@ -2227,5 +2225,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 f41dd71475..1f0fa8e16d 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -595,10 +595,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 --------------379EDB3A18529E0BEF728289 Content-Type: text/x-diff; name="0002-Add-regression-tests-foreign-partition-DDL.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-Add-regression-tests-foreign-partition-DDL.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH] Allow dropping partitioned table without CASCADE @ 2017-02-16 06:56 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: amit @ 2017-02-16 06:56 UTC (permalink / raw) 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-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH 1/2] Allow dropping partitioned table without CASCADE @ 2017-02-16 06:56 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: amit @ 2017-02-16 06:56 UTC (permalink / raw) 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 | 22 ++-------------------- src/test/regress/expected/insert.out | 7 ++----- src/test/regress/expected/update.out | 7 +------ src/test/regress/sql/alter_table.sql | 10 ++++------ src/test/regress/sql/create_table.sql | 9 ++------- src/test/regress/sql/inherit.sql | 4 ++-- src/test/regress/sql/insert.sql | 7 ++----- src/test/regress/sql/update.sql | 2 +- 11 files changed, 40 insertions(+), 73 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3cea220421..31b50ad77f 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); @@ -725,7 +727,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 @@ -2240,7 +2242,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; @@ -2270,7 +2273,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++; } @@ -2283,7 +2287,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]; @@ -2317,7 +2322,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 @@ -10744,7 +10752,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 e84af67fb2..ca66158ee3 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3339,10 +3339,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); @@ -3371,5 +3369,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 20eb3d35f9..c07a474b3d 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -667,10 +667,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..795d9f575c 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1843,23 +1843,5 @@ explain (costs off) select * from range_list_parted where a >= 30; Filter: (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 +drop table list_parted; +drop table range_list_parted; 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..9366f04255 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -219,9 +219,4 @@ DETAIL: Failing row contains (b, 9). -- ok 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 +drop table range_parted; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index a403fd8cb4..fbcc739f41 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2199,10 +2199,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); @@ -2227,5 +2225,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 f41dd71475..1f0fa8e16d 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -595,10 +595,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/inherit.sql b/src/test/regress/sql/inherit.sql index a8b7eb1c8d..836ec22c20 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -612,5 +612,5 @@ explain (costs off) select * from range_list_parted where b is null; explain (costs off) select * from range_list_parted where a is not null and a < 67; explain (costs off) select * from range_list_parted where a >= 30; -drop table list_parted cascade; -drop table range_list_parted cascade; +drop table list_parted; +drop table range_list_parted; 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; diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql index d7721ed376..663711997b 100644 --- a/src/test/regress/sql/update.sql +++ b/src/test/regress/sql/update.sql @@ -126,4 +126,4 @@ update range_parted set b = b - 1 where b = 10; update range_parted set b = b + 1 where b = 10; -- cleanup -drop table range_parted cascade; +drop table range_parted; -- 2.11.0 --------------CE28692440D789D70003F6EB Content-Type: text/x-diff; name="0002-Add-regression-tests-foreign-partition-DDL.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-Add-regression-tests-foreign-partition-DDL.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH] Allow dropping partitioned table without CASCADE @ 2017-02-16 06:56 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: amit @ 2017-02-16 06:56 UTC (permalink / raw) 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 | 22 ++-------------------- src/test/regress/expected/insert.out | 7 ++----- src/test/regress/expected/update.out | 7 +------ src/test/regress/sql/alter_table.sql | 10 ++++------ src/test/regress/sql/create_table.sql | 9 ++------- src/test/regress/sql/inherit.sql | 4 ++-- src/test/regress/sql/insert.sql | 7 ++----- src/test/regress/sql/update.sql | 2 +- 11 files changed, 40 insertions(+), 73 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3cea220421..cf566f974b 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); @@ -725,7 +727,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 @@ -2240,7 +2242,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; @@ -2270,7 +2273,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++; } @@ -2283,7 +2287,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]; @@ -2317,7 +2322,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_AUTO); + else + recordDependencyOn(&childobject, &parentobject, DEPENDENCY_NORMAL); /* * Post creation hook of this inheritance. Since object_access_hook @@ -10744,7 +10752,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 9885fcba89..c15bbdcbd1 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3339,10 +3339,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); @@ -3371,5 +3369,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 20eb3d35f9..c07a474b3d 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -667,10 +667,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..795d9f575c 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1843,23 +1843,5 @@ explain (costs off) select * from range_list_parted where a >= 30; Filter: (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 +drop table list_parted; +drop table range_list_parted; 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..9366f04255 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -219,9 +219,4 @@ DETAIL: Failing row contains (b, 9). -- ok 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 +drop table range_parted; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index f7b754f0be..37f327bf6d 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2199,10 +2199,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); @@ -2227,5 +2225,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 f41dd71475..1f0fa8e16d 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -595,10 +595,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/inherit.sql b/src/test/regress/sql/inherit.sql index a8b7eb1c8d..836ec22c20 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -612,5 +612,5 @@ explain (costs off) select * from range_list_parted where b is null; explain (costs off) select * from range_list_parted where a is not null and a < 67; explain (costs off) select * from range_list_parted where a >= 30; -drop table list_parted cascade; -drop table range_list_parted cascade; +drop table list_parted; +drop table range_list_parted; 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; diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql index d7721ed376..663711997b 100644 --- a/src/test/regress/sql/update.sql +++ b/src/test/regress/sql/update.sql @@ -126,4 +126,4 @@ update range_parted set b = b - 1 where b = 10; update range_parted set b = b + 1 where b = 10; -- cleanup -drop table range_parted cascade; +drop table range_parted; -- 2.11.0 --------------45F030A7BE1F47DD3B7363A6 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 --------------45F030A7BE1F47DD3B7363A6-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH] Allow dropping partitioned table without CASCADE @ 2017-02-16 06:56 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: amit @ 2017-02-16 06:56 UTC (permalink / raw) 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 | 22 ++-------------------- src/test/regress/expected/insert.out | 7 ++----- src/test/regress/expected/update.out | 7 +------ src/test/regress/sql/alter_table.sql | 10 ++++------ src/test/regress/sql/create_table.sql | 9 ++------- src/test/regress/sql/inherit.sql | 4 ++-- src/test/regress/sql/insert.sql | 7 ++----- src/test/regress/sql/update.sql | 2 +- 11 files changed, 40 insertions(+), 73 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3cea220421..31b50ad77f 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); @@ -725,7 +727,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 @@ -2240,7 +2242,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; @@ -2270,7 +2273,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++; } @@ -2283,7 +2287,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]; @@ -2317,7 +2322,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 @@ -10744,7 +10752,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 9885fcba89..c15bbdcbd1 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3339,10 +3339,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); @@ -3371,5 +3369,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 20eb3d35f9..c07a474b3d 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -667,10 +667,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..795d9f575c 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1843,23 +1843,5 @@ explain (costs off) select * from range_list_parted where a >= 30; Filter: (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 +drop table list_parted; +drop table range_list_parted; 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..9366f04255 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -219,9 +219,4 @@ DETAIL: Failing row contains (b, 9). -- ok 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 +drop table range_parted; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index f7b754f0be..37f327bf6d 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2199,10 +2199,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); @@ -2227,5 +2225,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 f41dd71475..1f0fa8e16d 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -595,10 +595,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/inherit.sql b/src/test/regress/sql/inherit.sql index a8b7eb1c8d..836ec22c20 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -612,5 +612,5 @@ explain (costs off) select * from range_list_parted where b is null; explain (costs off) select * from range_list_parted where a is not null and a < 67; explain (costs off) select * from range_list_parted where a >= 30; -drop table list_parted cascade; -drop table range_list_parted cascade; +drop table list_parted; +drop table range_list_parted; 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; diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql index d7721ed376..663711997b 100644 --- a/src/test/regress/sql/update.sql +++ b/src/test/regress/sql/update.sql @@ -126,4 +126,4 @@ update range_parted set b = b - 1 where b = 10; update range_parted set b = b + 1 where b = 10; -- cleanup -drop table range_parted cascade; +drop table range_parted; -- 2.11.0 --------------655E53A440FB4B4ADAF5161F 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 --------------655E53A440FB4B4ADAF5161F-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH] Allow dropping partitioned table without CASCADE @ 2017-02-16 06:56 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: amit @ 2017-02-16 06:56 UTC (permalink / raw) 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 | 22 ++-------------------- src/test/regress/expected/insert.out | 7 ++----- src/test/regress/expected/update.out | 7 +------ src/test/regress/sql/alter_table.sql | 10 ++++------ src/test/regress/sql/create_table.sql | 9 ++------- src/test/regress/sql/inherit.sql | 4 ++-- src/test/regress/sql/insert.sql | 7 ++----- src/test/regress/sql/update.sql | 2 +- 11 files changed, 40 insertions(+), 73 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3cea220421..cf566f974b 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); @@ -725,7 +727,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 @@ -2240,7 +2242,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; @@ -2270,7 +2273,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++; } @@ -2283,7 +2287,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]; @@ -2317,7 +2322,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_AUTO); + else + recordDependencyOn(&childobject, &parentobject, DEPENDENCY_NORMAL); /* * Post creation hook of this inheritance. Since object_access_hook @@ -10744,7 +10752,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 9885fcba89..c15bbdcbd1 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3339,10 +3339,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); @@ -3371,5 +3369,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 20eb3d35f9..c07a474b3d 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -667,10 +667,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..795d9f575c 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1843,23 +1843,5 @@ explain (costs off) select * from range_list_parted where a >= 30; Filter: (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 +drop table list_parted; +drop table range_list_parted; 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..9366f04255 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -219,9 +219,4 @@ DETAIL: Failing row contains (b, 9). -- ok 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 +drop table range_parted; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index f7b754f0be..37f327bf6d 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2199,10 +2199,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); @@ -2227,5 +2225,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 f41dd71475..1f0fa8e16d 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -595,10 +595,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/inherit.sql b/src/test/regress/sql/inherit.sql index a8b7eb1c8d..836ec22c20 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -612,5 +612,5 @@ explain (costs off) select * from range_list_parted where b is null; explain (costs off) select * from range_list_parted where a is not null and a < 67; explain (costs off) select * from range_list_parted where a >= 30; -drop table list_parted cascade; -drop table range_list_parted cascade; +drop table list_parted; +drop table range_list_parted; 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; diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql index d7721ed376..663711997b 100644 --- a/src/test/regress/sql/update.sql +++ b/src/test/regress/sql/update.sql @@ -126,4 +126,4 @@ update range_parted set b = b - 1 where b = 10; update range_parted set b = b + 1 where b = 10; -- cleanup -drop table range_parted cascade; +drop table range_parted; -- 2.11.0 --------------EB312FBA713FE247EB63323E 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 --------------EB312FBA713FE247EB63323E-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v47 1/9] Make index_concurrently_create_copy more general @ 2026-03-24 18:02 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 7+ messages in thread From: Álvaro Herrera @ 2026-03-24 18:02 UTC (permalink / raw) Add a 'boolean concurrent' option, and make it work for both cases. Also rename it to index_create_copy. This allows it to be reused for other purposes -- specifically, for REPACK CONCURRENTLY. With the CONCURRENTLY option, REPACK cannot simply swap the heap file and rebuild its indexes. Instead, it needs to build a separate set of indexes (including system catalog entries) *before* the actual swap, to reduce the time AccessExclusiveLock needs to be held for. This approach is different from what CREATE INDEX CONCURRENTLY does. Per a suggestion from Mihail Nikalayeu. Author: Antonin Houska <ah@cybertec.at> Discussion: https://postgr.es/m/41104.1754922120@localhost --- src/backend/catalog/index.c | 39 +++++++++++++++++++------------- src/backend/commands/indexcmds.c | 15 +++++++----- src/backend/nodes/makefuncs.c | 9 ++++---- src/include/catalog/index.h | 7 +++--- src/include/nodes/makefuncs.h | 4 +++- 5 files changed, 43 insertions(+), 31 deletions(-) diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 1ccfa687f05..b86ad73c626 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1289,17 +1289,17 @@ index_create(Relation heapRelation, } /* - * index_concurrently_create_copy + * index_create_copy * - * Create concurrently an index based on the definition of the one provided by - * caller. The index is inserted into catalogs and needs to be built later - * on. This is called during concurrent reindex processing. + * Create an index based on the definition of the one provided by caller. The + * index is inserted into catalogs. If 'concurrently' is TRUE, it needs to be + * built later on; otherwise it's built immediately. * * "tablespaceOid" is the tablespace to use for this index. */ Oid -index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, - Oid tablespaceOid, const char *newName) +index_create_copy(Relation heapRelation, bool concurrently, + Oid oldIndexId, Oid tablespaceOid, const char *newName) { Relation indexRelation; IndexInfo *oldInfo, @@ -1318,6 +1318,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, List *indexColNames = NIL; List *indexExprs = NIL; List *indexPreds = NIL; + int flags = 0; indexRelation = index_open(oldIndexId, RowExclusiveLock); @@ -1328,7 +1329,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, * Concurrent build of an index with exclusion constraints is not * supported. */ - if (oldInfo->ii_ExclusionOps != NULL) + if (oldInfo->ii_ExclusionOps != NULL && concurrently) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("concurrent index creation for exclusion constraints is not supported"))); @@ -1384,9 +1385,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, } /* - * Build the index information for the new index. Note that rebuild of - * indexes with exclusion constraints is not supported, hence there is no - * need to fill all the ii_Exclusion* fields. + * Build the index information for the new index. */ newInfo = makeIndexInfo(oldInfo->ii_NumIndexAttrs, oldInfo->ii_NumIndexKeyAttrs, @@ -1395,10 +1394,13 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, indexPreds, oldInfo->ii_Unique, oldInfo->ii_NullsNotDistinct, - false, /* not ready for inserts */ - true, + !concurrently, /* isready */ + concurrently, /* concurrent */ indexRelation->rd_indam->amsummarizing, - oldInfo->ii_WithoutOverlaps); + oldInfo->ii_WithoutOverlaps, + oldInfo->ii_ExclusionOps, + oldInfo->ii_ExclusionProcs, + oldInfo->ii_ExclusionStrats); /* * Extract the list of column names and the column numbers for the new @@ -1436,6 +1438,9 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, stattargets[i].isnull = isnull; } + if (concurrently) + flags = INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT; + /* * Now create the new index. * @@ -1459,7 +1464,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, indcoloptions->values, stattargets, reloptionsDatum, - INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT, + flags, 0, true, /* allow table to be a system catalog? */ false, /* is_internal? */ @@ -2453,7 +2458,8 @@ BuildIndexInfo(Relation index) indexStruct->indisready, false, index->rd_indam->amsummarizing, - indexStruct->indisexclusion && indexStruct->indisunique); + indexStruct->indisexclusion && indexStruct->indisunique, + NULL, NULL, NULL); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) @@ -2513,7 +2519,8 @@ BuildDummyIndexInfo(Relation index) indexStruct->indisready, false, index->rd_indam->amsummarizing, - indexStruct->indisexclusion && indexStruct->indisunique); + indexStruct->indisexclusion && indexStruct->indisunique, + NULL, NULL, NULL); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 373e8234794..4a2d21915b1 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -244,7 +244,8 @@ CheckIndexCompatible(Oid oldId, */ indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes, accessMethodId, NIL, NIL, false, false, - false, false, amsummarizing, isWithoutOverlaps); + false, false, amsummarizing, isWithoutOverlaps, + NULL, NULL, NULL); typeIds = palloc_array(Oid, numberOfAttributes); collationIds = palloc_array(Oid, numberOfAttributes); opclassIds = palloc_array(Oid, numberOfAttributes); @@ -931,7 +932,8 @@ DefineIndex(ParseState *pstate, !concurrent, concurrent, amissummarizing, - stmt->iswithoutoverlaps); + stmt->iswithoutoverlaps, + NULL, NULL, NULL); typeIds = palloc_array(Oid, numberOfAttributes); collationIds = palloc_array(Oid, numberOfAttributes); @@ -3989,10 +3991,11 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein tablespaceid = indexRel->rd_rel->reltablespace; /* Create new index definition based on given index */ - newIndexId = index_concurrently_create_copy(heapRel, - idx->indexId, - tablespaceid, - concurrentName); + newIndexId = index_create_copy(heapRel, + true, + idx->indexId, + tablespaceid, + concurrentName); /* * Now open the relation of the new index, a session-level lock is diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 3cd35c5c457..8d23aa917e5 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -834,7 +834,8 @@ IndexInfo * makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, List *predicates, bool unique, bool nulls_not_distinct, bool isready, bool concurrent, bool summarizing, - bool withoutoverlaps) + bool withoutoverlaps, Oid *exclusion_ops, Oid *exclusion_procs, + uint16 *exclusion_strats) { IndexInfo *n = makeNode(IndexInfo); @@ -863,9 +864,9 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, n->ii_PredicateState = NULL; /* exclusion constraints */ - n->ii_ExclusionOps = NULL; - n->ii_ExclusionProcs = NULL; - n->ii_ExclusionStrats = NULL; + n->ii_ExclusionOps = exclusion_ops; + n->ii_ExclusionProcs = exclusion_procs; + n->ii_ExclusionStrats = exclusion_strats; /* speculative inserts */ n->ii_UniqueOps = NULL; diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index a38e95bc0eb..ed9e4c37d27 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -101,10 +101,9 @@ extern Oid index_create(Relation heapRelation, #define INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS (1 << 4) #define INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS (1 << 5) -extern Oid index_concurrently_create_copy(Relation heapRelation, - Oid oldIndexId, - Oid tablespaceOid, - const char *newName); +extern Oid index_create_copy(Relation heapRelation, bool concurrently, + Oid oldIndexId, Oid tablespaceOid, + const char *newName); extern void index_concurrently_build(Oid heapRelationId, Oid indexRelationId); diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index bf54d39feb0..40ec249a7a1 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -99,7 +99,9 @@ extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, List *predicates, bool unique, bool nulls_not_distinct, bool isready, bool concurrent, - bool summarizing, bool withoutoverlaps); + bool summarizing, bool withoutoverlaps, + Oid *exclusion_ops, Oid *exclusion_procs, + uint16 *exclusion_strats); extern Node *makeStringConst(char *str, int location); extern DefElem *makeDefElem(char *name, Node *arg, int location); -- 2.47.3 --2fxmamo6mu2qbxgv Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="v47-0002-give-options-bitmask-to-table_delete-table_updat.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-03-24 18:02 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-02-16 06:56 [PATCH] Allow dropping partitioned table without CASCADE amit <amitlangote09@gmail.com> 2017-02-16 06:56 [PATCH] Allow dropping partitioned table without CASCADE amit <amitlangote09@gmail.com> 2017-02-16 06:56 [PATCH] Allow dropping partitioned table without CASCADE amit <amitlangote09@gmail.com> 2017-02-16 06:56 [PATCH 1/2] Allow dropping partitioned table without CASCADE amit <amitlangote09@gmail.com> 2017-02-16 06:56 [PATCH] Allow dropping partitioned table without CASCADE amit <amitlangote09@gmail.com> 2017-02-16 06:56 [PATCH 1/2] Allow dropping partitioned table without CASCADE amit <amitlangote09@gmail.com> 2026-03-24 18:02 [PATCH v47 1/9] Make index_concurrently_create_copy more general Álvaro Herrera <alvherre@kurilemu.de>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox