This is an automated email from the ASF dual-hosted git repository. avamingli pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 27ef32832b0d551a9f91a78802ef54fa386454da Author: Huansong Fu <[email protected]> AuthorDate: Wed Apr 19 15:26:06 2023 -0700 Merge redundant test cases in regress tests partition|partition1 Both 'partition' and 'partition1' tests have test cases for MPP-13806 both of which create/drop a table named 'mpp13806'. Running them in parallel caused flakiness with an error: alter table mpp13806 add partition test end (date '2008-01-01') exclusive; +ERROR: relation "mpp13806" does not exist The log shows that it's just that the two tests stepped each other's toes: 2023-04-19 20:53:01.875375 UTC,"gpadmin","regression",p45824,th-117626752,"[local]",,2023-04-19 20:52:25 UTC,0,con467,cmd1524,seg-1,,,,sx1,"LOG","00000","statement: CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2)) 2023-04-19 20:53:01.905142 UTC,"gpadmin","regression",p46488,th-117626752,"[local]",,2023-04-19 20:52:44 UTC,0,con474,cmd865,seg-1,,,,sx1,"LOG","00000","statement: drop table if exists mpp13806;",,,,,,,0,,"postgres.c",1729, 2023-04-19 20:53:01.906080 UTC,"gpadmin","regression",p45824,th-117626752,"[local]",,2023-04-19 20:52:25 UTC,0,con467,cmd1525,seg-1,,,,sx1,"LOG","00000","statement: alter table mpp13806 add partition test end (date '2008-01-01') exclusive;",,,,,,,0,,"postgres.c",1729, 2023-04-19 20:53:01.918500 UTC,"gpadmin","regression",p45824,th-117626752,"[local]",,2023-04-19 20:52:25 UTC,17108,con467,cmd1525,seg-1,,,x17108,sx1,"ERROR","42P01","relation ""mpp13806"" does not exist",,,,,,"alter table mpp13806 add partition test end (date '2008-01-01') exclusive;",0,,"namespace.c",440 Now merge those tests into one place. --- src/test/regress/expected/partition.out | 24 -- src/test/regress/expected/partition1.out | 20 ++ src/test/regress/expected/partition_optimizer.out | 24 -- src/test/regress/sql/partition.sql | 342 ++++++++++++++++++++-- src/test/regress/sql/partition1.sql | 12 + 5 files changed, 350 insertions(+), 72 deletions(-) diff --git a/src/test/regress/expected/partition.out b/src/test/regress/expected/partition.out index ce016eff88..47f87ea7fc 100755 --- a/src/test/regress/expected/partition.out +++ b/src/test/regress/expected/partition.out @@ -4489,30 +4489,6 @@ group by relname; drop table s cascade; -- MPP-13750 ) --- MPP-13806 start -drop table if exists mpp13806; -NOTICE: table "mpp13806" does not exist, skipping - CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2)) - DISTRIBUTED BY (id) - PARTITION BY RANGE (date) - ( START (date '2008-01-01') INCLUSIVE - END (date '2008-01-05') EXCLUSIVE - EVERY (INTERVAL '1 day') ); - --- Adding unbound partition right before the start used to fail -alter table mpp13806 add partition test end (date '2008-01-01') exclusive; - -drop table if exists mpp13806; - CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2)) - DISTRIBUTED BY (id) - PARTITION BY RANGE (date) - ( START (date '2008-01-01') EXCLUSIVE - END (date '2008-01-05') EXCLUSIVE - EVERY (INTERVAL '1 day') ); --- For good measure, test the opposite case -alter table mpp13806 add partition test end (date '2008-01-01') inclusive; -drop table mpp13806; --- MPP-13806 end -- MPP-14471 start -- No unenforceable PK/UK constraints! (UNIQUE INDEXes still allowed; tested above) drop table if exists tc cascade; diff --git a/src/test/regress/expected/partition1.out b/src/test/regress/expected/partition1.out index ec4d70da27..9460b73c41 100644 --- a/src/test/regress/expected/partition1.out +++ b/src/test/regress/expected/partition1.out @@ -2107,6 +2107,26 @@ select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like mpp13806_1_prt_test1 | FOR VALUES FROM ('12-31-2007') TO ('01-01-2008') (7 rows) +drop table if exists mpp13806; + CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2)) + DISTRIBUTED BY (id) + PARTITION BY RANGE (date) + ( START (date '2008-01-01') EXCLUSIVE + END (date '2008-01-05') EXCLUSIVE + EVERY (INTERVAL '1 day') ); +-- For good measure, test the opposite case +alter table mpp13806 add partition test end (date '2008-01-01') inclusive; +select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like 'mpp13806%'; + relname | pg_get_expr +---------------------+-------------------------------------------------- + mpp13806 | + mpp13806_1_prt_1 | FOR VALUES FROM ('01-02-2008') TO ('01-03-2008') + mpp13806_1_prt_2 | FOR VALUES FROM ('01-03-2008') TO ('01-04-2008') + mpp13806_1_prt_3 | FOR VALUES FROM ('01-04-2008') TO ('01-05-2008') + mpp13806_1_prt_test | FOR VALUES FROM (MINVALUE) TO ('01-02-2008') +(5 rows) + +drop table mpp13806; -- -- Create two tables mpp14613_range (range partitioned) and -- mpp14613_list (list partitioned) with 5 partitions (including default diff --git a/src/test/regress/expected/partition_optimizer.out b/src/test/regress/expected/partition_optimizer.out index ad07522606..2aa66e1a92 100755 --- a/src/test/regress/expected/partition_optimizer.out +++ b/src/test/regress/expected/partition_optimizer.out @@ -4490,30 +4490,6 @@ group by relname; drop table s cascade; -- MPP-13750 ) --- MPP-13806 start -drop table if exists mpp13806; -NOTICE: table "mpp13806" does not exist, skipping - CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2)) - DISTRIBUTED BY (id) - PARTITION BY RANGE (date) - ( START (date '2008-01-01') INCLUSIVE - END (date '2008-01-05') EXCLUSIVE - EVERY (INTERVAL '1 day') ); - --- Adding unbound partition right before the start used to fail -alter table mpp13806 add partition test end (date '2008-01-01') exclusive; - -drop table if exists mpp13806; - CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2)) - DISTRIBUTED BY (id) - PARTITION BY RANGE (date) - ( START (date '2008-01-01') EXCLUSIVE - END (date '2008-01-05') EXCLUSIVE - EVERY (INTERVAL '1 day') ); --- For good measure, test the opposite case -alter table mpp13806 add partition test end (date '2008-01-01') inclusive; -drop table mpp13806; --- MPP-13806 end -- MPP-14471 start -- No unenforceable PK/UK constraints! (UNIQUE INDEXes still allowed; tested above) drop table if exists tc cascade; diff --git a/src/test/regress/sql/partition.sql b/src/test/regress/sql/partition.sql index cc1d519cb8..5948383b80 100644 --- a/src/test/regress/sql/partition.sql +++ b/src/test/regress/sql/partition.sql @@ -2902,30 +2902,6 @@ drop table s cascade; -- MPP-13750 ) --- MPP-13806 start -drop table if exists mpp13806; - CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2)) - DISTRIBUTED BY (id) - PARTITION BY RANGE (date) - ( START (date '2008-01-01') INCLUSIVE - END (date '2008-01-05') EXCLUSIVE - EVERY (INTERVAL '1 day') ); - --- Adding unbound partition right before the start used to fail -alter table mpp13806 add partition test end (date '2008-01-01') exclusive; - -drop table if exists mpp13806; - CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2)) - DISTRIBUTED BY (id) - PARTITION BY RANGE (date) - ( START (date '2008-01-01') EXCLUSIVE - END (date '2008-01-05') EXCLUSIVE - EVERY (INTERVAL '1 day') ); --- For good measure, test the opposite case -alter table mpp13806 add partition test end (date '2008-01-01') inclusive; -drop table mpp13806; --- MPP-13806 end - -- MPP-14471 start -- No unenforceable PK/UK constraints! (UNIQUE INDEXes still allowed; tested above) drop table if exists tc cascade; @@ -3865,3 +3841,321 @@ commit; -- no relations remain in this case. select count(*) from pg_class where relname like 'temp_parent_%'; +-- check ATTACH PARTITION on parent table with different distribution policy +CREATE EXTENSION IF NOT EXISTS gp_debug_numsegments; +SELECT gp_debug_set_create_table_default_numsegments(1); + +CREATE TABLE expanded_parent(a int, b int) PARTITION BY RANGE(b) (START (0) END (6) EVERY (3)); +CREATE TABLE expanded_parent2(a int, b int) PARTITION BY RANGE(b) (START (0) END (6) EVERY (3)); +CREATE TABLE unexpanded_parent(a int, b int) PARTITION BY RANGE(b) (START (0) END (6) EVERY (3)); +CREATE TABLE unexpanded_attach(a int, b int); +CREATE TABLE unexpanded_attach2(a int, b int); +CREATE TABLE expanded_attach(a int, b int); +CREATE TABLE expanded_attach2(a int, b int); + +SELECT gp_debug_reset_create_table_default_numsegments(); + +-- attaching unexpanded partition to expanded table should fail +ALTER TABLE expanded_parent EXPAND TABLE; +ALTER TABLE expanded_parent ATTACH PARTITION unexpanded_attach FOR VALUES FROM (6) TO (9); + +-- attaching unexpanded partition to expanded table with partition prepare should fail +ALTER TABLE expanded_parent2 EXPAND PARTITION PREPARE; +ALTER TABLE expanded_parent2 ATTACH PARTITION unexpanded_attach2 FOR VALUES FROM (6) TO (9); + +-- attaching expanded partition to unexpanded table should fail +ALTER TABLE expanded_attach EXPAND TABLE; +ALTER TABLE unexpanded_parent ATTACH PARTITION expanded_attach FOR VALUES FROM (6) TO (9); + +-- attaching expanded partition to expanded table should succeed +ALTER TABLE expanded_attach2 EXPAND TABLE; +ALTER TABLE expanded_parent2 ATTACH PARTITION expanded_attach2 FOR VALUES FROM (6) TO (9); +ALTER TABLE expanded_parent ATTACH PARTITION expanded_attach FOR VALUES FROM (6) TO (9); + +-- cleanup +DROP TABLE expanded_parent; +DROP TABLE expanded_parent2; +DROP TABLE unexpanded_parent; +DROP TABLE unexpanded_attach; +DROP TABLE unexpanded_attach2; + +-- +-- Verify inheritance behavior of new partition child using various syntax +-- +-- set owner for partition root (should be inherited except for ATTACH, EXCHANGE) +CREATE ROLE part_inherit_role CREATEROLE; +CREATE ROLE part_inherit_other_role IN ROLE part_inherit_role; +CREATE ROLE part_inherit_priv_role; +CREATE ROLE part_inherit_attach_priv_role; +CREATE ROLE part_inherit_exchange_out_priv_role; +SET ROLE part_inherit_role; + +CREATE TABLE part_inherit ( + a int, + b int, +-- non-partition constraint should be inherited except for ATTACH, EXCHANGE + CONSTRAINT con1 CHECK (a >= 0) +) +PARTITION BY RANGE (a) +SUBPARTITION BY RANGE (b) +SUBPARTITION TEMPLATE +(SUBPARTITION l2_child START(10100) END(10200)) +(DEFAULT PARTITION l1_default, + PARTITION l1_child1 START (0) END (100), + PARTITION l1_to_split START (100) END (200), + PARTITION l1_to_exchange START (200) END (300)) +--AM and reloption should be inherited except for ATTACH, EXCHANGE +WITH (appendonly = TRUE, compresslevel = 7); + +-- Set more properties for the root. +-- index are inherited +CREATE INDEX part_inherit_i on part_inherit(a); +-- privileges are inherited except for ATTACH, EXCHANGE +GRANT UPDATE ON part_inherit TO part_inherit_priv_role; +-- triggers are inherited except for subpartitions +-- FIXME: In 6X we used to not inherit triggers at all, in 7X we start to inherit +-- trigger like the upstream, but the subpartitions created from the subpartition +-- template still don't inherit the triggers. We should fix that. +CREATE FUNCTION part_inherit_trig() RETURNS TRIGGER LANGUAGE plpgsql + AS $$ BEGIN RETURN NULL; END $$; +CREATE TRIGGER part_inherit_tg AFTER INSERT ON part_inherit + FOR EACH ROW EXECUTE FUNCTION part_inherit_trig(); +-- rules are not inherited +CREATE RULE part_inherit_rule AS ON UPDATE TO part_inherit DO INSERT INTO part_inherit values(1); +-- row-level security policies are not inherited +ALTER TABLE part_inherit ENABLE ROW LEVEL SECURITY; + +-- Check the current status +SELECT c.relname, + c.reloptions, + c.relkind, + a.amname as am, + c.relhasindex as hasindex, + r.rolname as owner, + c.relacl as acl, + c.relchecks as numchecks, + c.relhasrules as hasrules, + c.relhastriggers as hastriggers, + c.relrowsecurity as rowsecurity +FROM pg_class c join pg_roles r on c.relowner = r.oid + join pg_am a on c.relam = a.oid +WHERE c.relname LIKE 'part_inherit%' AND + c.relkind NOT IN ('i', 'I'); + +-- Now create child partitions in various forms + +-- set an alternative role +SET ROLE part_inherit_other_role; + +-- CREATE TABLE PARTITION OF +CREATE TABLE part_inherit_partof PARTITION OF part_inherit FOR VALUES FROM (300) TO (400); + +SELECT c.relname, + c.reloptions, + c.relkind, + a.amname as am, + c.relhasindex as hasindex, + r.rolname as owner, + c.relacl as acl, + c.relchecks as numchecks, + c.relhasrules as hasrules, + c.relhastriggers as hastriggers, + c.relrowsecurity as rowsecurity +FROM pg_class c join pg_roles r on c.relowner = r.oid + join pg_am a on c.relam = a.oid +WHERE c.relname = 'part_inherit_partof'; + +-- Now create child partitions in various forms +-- ATTACH PARTITION +-- error if the partition-to-be doesn't have the same constraint as the parent. +CREATE TABLE part_inherit_attach(a int, b int); +ALTER TABLE part_inherit ATTACH PARTITION part_inherit_attach FOR VALUES FROM (400) TO (500); +DROP TABLE part_inherit_attach; +-- good case: have the same constraint as parent. Can have other constraint too. +CREATE TABLE part_inherit_attach(a int, b int, CONSTRAINT con1 CHECK (a>=0), CONSTRAINT con2 CHECK (b>=0)); +-- reloptions and AM ('heap' which is different than the future parent) will be kept +ALTER TABLE part_inherit_attach SET (fillfactor=30); +-- privilege will be kept +GRANT UPDATE ON part_inherit_attach TO part_inherit_attach_priv_role; +-- do the attach +ALTER TABLE part_inherit ATTACH PARTITION part_inherit_attach FOR VALUES FROM (400) TO (500); + +SELECT c.relname, + c.reloptions, + c.relkind, + a.amname as am, + c.relhasindex as hasindex, + r.rolname as owner, + c.relacl as acl, + c.relchecks as numchecks, + c.relhasrules as hasrules, + c.relhastriggers as hastriggers, + c.relrowsecurity as rowsecurity +FROM pg_class c join pg_roles r on c.relowner = r.oid + join pg_am a on c.relam = a.oid +WHERE c.relname = 'part_inherit_attach'; + + +-- ADD PARTITION +ALTER TABLE part_inherit ADD PARTITION added START(500) END(600); + +SELECT c.relname, + c.reloptions, + c.relkind, + a.amname as am, + c.relhasindex as hasindex, + r.rolname as owner, + c.relacl as acl, + c.relchecks as numchecks, + c.relhasrules as hasrules, + c.relhastriggers as hastriggers, + c.relrowsecurity as rowsecurity +FROM pg_class c join pg_roles r on c.relowner = r.oid + join pg_am a on c.relam = a.oid +WHERE c.relname LIKE 'part_inherit_1_prt_added%' AND + c.relkind NOT IN ('i', 'I'); + +-- EXCHANGE PARTITION - same behavior as ATTACH PARTITION +-- error if the partition-to-be doesn't have the same constraint as the parent. +CREATE TABLE part_inherit_exchange_out(a int, b int); +ALTER TABLE part_inherit_1_prt_l1_to_exchange EXCHANGE PARTITION l2_child WITH TABLE part_inherit_exchange_out; +DROP TABLE part_inherit_exchange_out; +-- good case: have the same constraint as parent. Can have other constraint too. +CREATE TABLE part_inherit_exchange_out(a int, b int, CONSTRAINT con1 CHECK (a>=0), CONSTRAINT con2 CHECK (b>=0)); +-- reloptions and AM ('heap' which is different than the future parent) will be kept +ALTER TABLE part_inherit_exchange_out SET (fillfactor=30); +-- privilege will be kept +GRANT UPDATE ON part_inherit_exchange_out TO part_inherit_exchange_out_priv_role; +-- do the exchange +ALTER TABLE part_inherit_1_prt_l1_to_exchange EXCHANGE PARTITION l2_child WITH TABLE part_inherit_exchange_out; + +SELECT c.relname, + c.reloptions, + c.relkind, + a.amname as am, + c.relhasindex as hasindex, + r.rolname as owner, + c.relacl as acl, + c.relchecks as numchecks, + c.relhasrules as hasrules, + c.relhastriggers as hastriggers, + c.relrowsecurity as rowsecurity +FROM pg_class c join pg_roles r on c.relowner = r.oid + join pg_am a on c.relam = a.oid +WHERE (c.relname LIKE 'part_inherit_1_prt_l1_to_exchange%' OR c.relname = 'part_inherit_exchange_out') + AND c.relkind NOT IN ('i', 'I'); + +-- SPLIT PARTITION +ALTER TABLE part_inherit_1_prt_l1_to_split SPLIT PARTITION l2_child AT (10150) INTO (PARTITION split1, PARTITION split2); + +SELECT c.relname, + c.reloptions, + c.relkind, + a.amname as am, + c.relhasindex as hasindex, + r.rolname as owner, + c.relacl as acl, + c.relchecks as numchecks, + c.relhasrules as hasrules, + c.relhastriggers as hastriggers, + c.relrowsecurity as rowsecurity +FROM pg_class c join pg_roles r on c.relowner = r.oid + join pg_am a on c.relam = a.oid +WHERE c.relname LIKE 'part_inherit_1_prt_l1_to_split%' AND + c.relkind NOT IN ('i', 'I'); + +-- Now print everything for comparison +SELECT c.relname, + c.reloptions, + c.relkind, + a.amname as am, + c.relhasindex as hasindex, + r.rolname as owner, + c.relacl as acl, + c.relchecks as numchecks, + c.relhasrules as hasrules, + c.relhastriggers as hastriggers, + c.relrowsecurity as rowsecurity +FROM pg_class c join pg_roles r on c.relowner = r.oid + join pg_am a on c.relam = a.oid +WHERE c.relname LIKE 'part_inherit%' AND + c.relkind NOT IN ('i', 'I'); + +RESET ROLE; + +DROP TABLE part_inherit; +DROP FUNCTION part_inherit_trig CASCADE; +DROP TABLE part_inherit_exchange_out; +DROP ROLE part_inherit_role; +DROP ROLE part_inherit_other_role; +DROP ROLE part_inherit_priv_role; +DROP ROLE part_inherit_attach_priv_role; +DROP ROLE part_inherit_exchange_out_priv_role; + +--Test cases for data selection from range partitioned tables with predicate on date or timestamp type------------- +drop table if exists test_rangePartition; +create table public.test_rangePartition +(datedday date) + WITH ( + appendonly=false + ) + PARTITION BY RANGE(datedday) +( + PARTITION pn_20221022 START ('2022-10-22'::date) END ('2022-10-23'::date), + PARTITION pn_20221023 START ('2022-10-23'::date) END ('2022-10-24'::date), + DEFAULT PARTITION pdefault + ); + +insert into public.test_rangePartition(datedday) +select ('2022-10-22'::date) +union +select ('2022-10-23'::date); + +--Test case with condition on date and timestamp +explain (costs off) select max(datedday) from public.test_rangePartition where datedday='2022-10-23' or datedday=('2022-10-23'::date -interval '1 day'); +select max(datedday) from public.test_rangePartition where datedday='2022-10-23' or datedday=('2022-10-23'::date -interval '1 day'); + +--Test case with condition on date and timestamp +explain (costs off) select max(datedday) from public.test_rangePartition where datedday='2022-10-23' or datedday='2022-10-22'; +select max(datedday) from public.test_rangePartition where datedday='2022-10-23' or datedday='2022-10-22'; + +--Test case with condition on timestamp and timestamp +explain (costs off) select max(datedday) from public.test_rangePartition where datedday=('2022-10-23'::date -interval '0 day') or datedday=('2022-10-23'::date -interval '1 day'); +select max(datedday) from public.test_rangePartition where datedday=('2022-10-23'::date -interval '0 day') or datedday=('2022-10-23'::date -interval '1 day'); + +--Test case with condition on date and timestamp +explain (costs off) select datedday from public.test_rangePartition where datedday='2022-10-23' or datedday=('2022-10-23'::date -interval '1 day'); +select datedday from public.test_rangePartition where datedday='2022-10-23' or datedday=('2022-10-23'::date -interval '1 day'); + +drop table test_rangePartition; + +--Test cases for data selection from List partitioned tables with predicate on date or timestamp type------------- +drop table if exists test_listPartition; +create table test_listPartition (i int, d date) + partition by list(d) + (partition p1 values('2022-10-22'), partition p2 values('2022-10-23'), + default partition pdefault ); + + +insert into test_listPartition values(1,'2022-10-22'); +insert into test_listPartition values(2,'2022-10-23'); +insert into test_listPartition values(3,'2022-10-24'); + + +--Test case with condition on date and timestamp +explain (costs off) select max(d) from test_listPartition where d='2022-10-23' or d=('2022-10-23'::date -interval '1 day'); +select max(d) from test_listPartition where d='2022-10-23' or d=('2022-10-23'::date -interval '1 day'); + +--Test case with condition on date and date +explain (costs off) select max(d) from test_listPartition where d='2022-10-23' or d='2022-10-22'; +select max(d) from test_listPartition where d='2022-10-23' or d='2022-10-22'; + +--Test case with condition on timestamp and timestamp +explain (costs off) select max(d) from test_listPartition where d=('2022-10-23'::date -interval '0 day') or d=('2022-10-23'::date -interval '1 day'); +select max(d) from test_listPartition where d=('2022-10-23'::date -interval '0 day') or d=('2022-10-23'::date -interval '1 day'); + +--Test case with condition on timestamp and timestamp +explain (costs off) select d from test_listPartition where d='2022-10-23' or d=('2022-10-23'::date -interval '1 day'); +select d from test_listPartition where d='2022-10-23' or d=('2022-10-23'::date -interval '1 day'); + +drop table test_listPartition; diff --git a/src/test/regress/sql/partition1.sql b/src/test/regress/sql/partition1.sql index cd37759373..0c5e8328d2 100644 --- a/src/test/regress/sql/partition1.sql +++ b/src/test/regress/sql/partition1.sql @@ -1252,6 +1252,18 @@ select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like alter table mpp13806 add partition test1 start (date '2007-12-31') inclusive end (date '2008-01-01') exclusive; select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like 'mpp13806%'; +drop table if exists mpp13806; + CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2)) + DISTRIBUTED BY (id) + PARTITION BY RANGE (date) + ( START (date '2008-01-01') EXCLUSIVE + END (date '2008-01-05') EXCLUSIVE + EVERY (INTERVAL '1 day') ); +-- For good measure, test the opposite case +alter table mpp13806 add partition test end (date '2008-01-01') inclusive; +select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like 'mpp13806%'; +drop table mpp13806; + -- -- Create two tables mpp14613_range (range partitioned) and --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
