From 71590d20c1f8bbe8fb1f4996ff343066a21ea256 Mon Sep 17 00:00:00 2001 From: amit Date: Fri, 16 Dec 2016 09:45:57 +0900 Subject: [PATCH 7/7] Add some more tests for tuple-routing We fixed some issues with how PartitionDispatch related code handled multi-level partitioned tables in commit a25665088d, but didn't add any tests. Reported by: Dmitry Ivanov, Robert Haas Patch by: Amit Langote Reports: https://www.postgresql.org/message-id/0d5b64c9-fa05-4dab-93e7-56576d1193ca%40postgrespro.ru https://www.postgresql.org/message-id/CA%2BTgmoZ86v1G%2Bzx9etMiSQaBBvYMKfU-iitqZArSh5z0n8Q4cA%40mail.gmail.com --- src/test/regress/expected/insert.out | 40 +++++++++++++++++++++++++++++++++++- src/test/regress/sql/insert.sql | 19 +++++++++++++++++ 2 files changed, 58 insertions(+), 1 deletion(-) diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index 328f776dd7..aaa74da607 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -301,6 +301,36 @@ alter table part_ee_ff add constraint check_b_25 check (b = 25); insert into list_parted values ('ee', 20); ERROR: new row for relation "part_ee_ff3" violates check constraint "check_b_25" DETAIL: Failing row contains (ee, 20). +alter table part_ee_ff drop constraint check_b_25; +-- some more tests to exercise tuple-routing with multi-level partitioning +create table part_gg partition of list_parted for values in ('gg') partition by range (b); +create table part_gg1 partition of part_gg for values from (unbounded) to (1); +create table part_gg2 partition of part_gg for values from (1) to (10) partition by range (b); +create table part_gg2_1 partition of part_gg2 for values from (1) to (5); +create table part_gg2_2 partition of part_gg2 for values from (5) to (10); +create table part_ee_ff4 partition of part_ee_ff for values from (30) to (40) partition by range (b); +create table part_ee_ff4_1 partition of part_ee_ff4 for values from (30) to (35); +create table part_ee_ff4_2 partition of part_ee_ff4 for values from (35) to (40); +truncate list_parted; +insert into list_parted values ('aa'), ('cc'); +insert into list_parted select 'Ff', s.a from generate_series(1, 39) s(a); +insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a); +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; + tableoid | a | min_b | max_b +---------------+----+-------+------- + part_aa_bb | aa | | + part_cc_dd | cc | | + part_ee_ff1 | Ff | 1 | 9 + part_ee_ff2 | Ff | 10 | 19 + part_ee_ff3 | Ff | 20 | 29 + part_ee_ff4_1 | Ff | 30 | 34 + part_ee_ff4_2 | Ff | 35 | 39 + part_gg2_1 | gg | 1 | 4 + part_gg2_2 | gg | 5 | 9 + part_null | | 1 | 1 +(10 rows) + -- cleanup drop table range_parted cascade; NOTICE: drop cascades to 4 other objects @@ -309,7 +339,7 @@ drop cascades to table part2 drop cascades to table part3 drop cascades to table part4 drop table list_parted cascade; -NOTICE: drop cascades to 7 other objects +NOTICE: drop cascades to 15 other objects DETAIL: drop cascades to table part_aa_bb drop cascades to table part_cc_dd drop cascades to table part_null @@ -317,3 +347,11 @@ drop cascades to table part_ee_ff drop cascades to table part_ee_ff1 drop cascades to table part_ee_ff2 drop cascades to table part_ee_ff3 +drop cascades to table part_ee_ff4 +drop cascades to table part_ee_ff4_1 +drop cascades to table part_ee_ff4_2 +drop cascades to table part_gg +drop cascades to table part_gg1 +drop cascades to table part_gg2 +drop cascades to table part_gg2_1 +drop cascades to table part_gg2_2 diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index ebe371e884..0fa9cef4b5 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -180,6 +180,25 @@ alter table part_ee_ff attach partition part_ee_ff3 for values from (20) to (30) truncate part_ee_ff; alter table part_ee_ff add constraint check_b_25 check (b = 25); insert into list_parted values ('ee', 20); +alter table part_ee_ff drop constraint check_b_25; + +-- some more tests to exercise tuple-routing with multi-level partitioning +create table part_gg partition of list_parted for values in ('gg') partition by range (b); +create table part_gg1 partition of part_gg for values from (unbounded) to (1); +create table part_gg2 partition of part_gg for values from (1) to (10) partition by range (b); +create table part_gg2_1 partition of part_gg2 for values from (1) to (5); +create table part_gg2_2 partition of part_gg2 for values from (5) to (10); + +create table part_ee_ff4 partition of part_ee_ff for values from (30) to (40) partition by range (b); +create table part_ee_ff4_1 partition of part_ee_ff4 for values from (30) to (35); +create table part_ee_ff4_2 partition of part_ee_ff4 for values from (35) to (40); + +truncate list_parted; +insert into list_parted values ('aa'), ('cc'); +insert into list_parted select 'Ff', s.a from generate_series(1, 39) s(a); +insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a); +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 range_parted cascade; -- 2.11.0