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] 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 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] Do not check the REPLICATION attribute when running REPACK. @ 2026-04-20 07:20 Antonin Houska <ah@cybertec.at> 0 siblings, 0 replies; 7+ messages in thread From: Antonin Houska @ 2026-04-20 07:20 UTC (permalink / raw) Although REPACK (CONCURRENTLY) uses replication slots, there is no concern that the slot will leak data of other users because the MAINTAIN privilege on the table is required anyway. The REPLICATION attribute is also not needed to prevent REPACK from stealing slots from logical replication, since commit e76d8c749c introduces a limit on the maximum number of slots used by REPACK. --- src/backend/commands/repack_worker.c | 1 - 1 file changed, 1 deletion(-) diff --git a/src/backend/commands/repack_worker.c b/src/backend/commands/repack_worker.c index b17edd771e2..e4a4860805b 100644 --- a/src/backend/commands/repack_worker.c +++ b/src/backend/commands/repack_worker.c @@ -214,7 +214,6 @@ repack_setup_logical_decoding(Oid relid) /* * Make sure we can use logical decoding. */ - CheckSlotPermissions(); CheckLogicalDecodingRequirements(true); /* -- 2.47.3 --=-=-=-- ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-04-20 07:20 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 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> 2017-02-16 06:56 [PATCH] Allow dropping partitioned table without CASCADE amit <amitlangote09@gmail.com> 2026-04-20 07:20 [PATCH] Do not check the REPLICATION attribute when running REPACK. Antonin Houska <ah@cybertec.at>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox