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 3419239371a33ed3f6596a8d9b06fc319e2556c5 Author: Zhang Mingli <[email protected]> AuthorDate: Tue Jan 7 19:11:37 2025 +0800 fix test cases --- gpcontrib/gp_inject_fault/gp_inject_fault--1.0.sql | 2 +- src/backend/access/appendonly/aosegfiles.c | 18 +- src/backend/access/transam/xlog.c | 6 +- src/backend/commands/explain.c | 10 +- src/include/cdb/cdbtm.h | 6 +- src/test/regress/expected/AOCO_Compression.out | 11 +- src/test/regress/expected/aggregates_optimizer.out | 14 +- src/test/regress/expected/partition.out | 530 ++++++++++++++++++++ src/test/regress/expected/partition_optimizer.out | 543 +++++++++++++++++++++ src/test/regress/expected/partition_prune.out | 2 +- src/test/regress/expected/privileges.out | 5 - .../regress/output/alter_db_set_tablespace.source | 2 +- src/test/regress/output/external_table.source | 12 - src/test/regress/stdin | 2 - src/test/regress/stdout | 4 - 15 files changed, 1109 insertions(+), 58 deletions(-) diff --git a/gpcontrib/gp_inject_fault/gp_inject_fault--1.0.sql b/gpcontrib/gp_inject_fault/gp_inject_fault--1.0.sql index 4c64ee4b57..b18a3dc62c 100644 --- a/gpcontrib/gp_inject_fault/gp_inject_fault--1.0.sql +++ b/gpcontrib/gp_inject_fault/gp_inject_fault--1.0.sql @@ -4,7 +4,7 @@ \echo Use "CREATE EXTENSION gp_fault_inject" to load this file. \quit -- NOTE: we let some background process ignore all but a few faults (check checkBgProcessSkipFault()). -CREATE FUNCTION @[email protected]_inject_fault( +CREATE FUNCTION gp_inject_fault( faultname text, type text, ddl text, diff --git a/src/backend/access/appendonly/aosegfiles.c b/src/backend/access/appendonly/aosegfiles.c index 5e9d3d48cf..92f6996d51 100644 --- a/src/backend/access/appendonly/aosegfiles.c +++ b/src/backend/access/appendonly/aosegfiles.c @@ -1527,25 +1527,25 @@ get_ao_compression_ratio(PG_FUNCTION_ARGS) Relation parentrel; float8 result = -1.0; + /* open the parent (main) relation */ + parentrel = table_open(relid, AccessShareLock); + + if (parentrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + { + table_close(parentrel, AccessShareLock); + PG_RETURN_FLOAT8(result); + } + if (Gp_role == GP_ROLE_EXECUTE) ereport(ERROR, (errmsg("get_ao_compression_ratio is expected to run in QD process, or utility mode"))); - /* open the parent (main) relation */ - parentrel = table_open(relid, AccessShareLock); - if (!(RelationIsAoRows(parentrel) || RelationIsAoCols(parentrel))) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("'%s' is not an append-only relation", RelationGetRelationName(parentrel)))); - if (parentrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) - { - table_close(parentrel, AccessShareLock); - PG_RETURN_FLOAT8(result); - } - if (RelationIsAoRows(parentrel)) result = aorow_compression_ratio_internal(parentrel); else diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 24810a2de0..22196d95d0 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -13890,15 +13890,15 @@ initialize_wal_bytes_written(void) * WAL generation and move at sustained speed with network and mirrors. * * NB: This function should never be called from inside a critical section, - * meaning caller should never have MyPgXact->delayChkpt set to true. Otherwise, + * meaning caller should never have MyProc->delayChkpt set to true. Otherwise, * if mirror is down, we will end up in a deadlock situation between the primary - * and the checkpointer process, because if MyPgXact->delayChkpt is set, + * and the checkpointer process, because if MyProc->delayChkpt is set, * checkpointer cannot proceed to unset WalSndCtl->sync_standbys_defined. */ void wait_to_avoid_large_repl_lag(void) { - Assert(!MyPgXact->delayChkpt); + Assert(!MyProc->delayChkpt); /* rep_lag_avoidance_threshold is defined in KB */ if (rep_lag_avoidance_threshold && wal_bytes_written > (rep_lag_avoidance_threshold * 1024)) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 8ba54dfa28..e0f20f94d4 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -724,6 +724,9 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, /* run the plan */ ExecutorRun(queryDesc, dir, 0L, true); + /* run cleanup too */ + ExecutorFinish(queryDesc); + /* Wait for completion of all qExec processes. */ if (queryDesc->estate->dispatcherState && queryDesc->estate->dispatcherState->primaryResults) { @@ -742,13 +745,6 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, } } - /* run cleanup too */ - ExecutorFinish(queryDesc); - - /* Wait for completion of all qExec processes. */ - if (queryDesc->estate->dispatcherState && queryDesc->estate->dispatcherState->primaryResults) - cdbdisp_checkDispatchResult(queryDesc->estate->dispatcherState, DISPATCH_WAIT_NONE); - /* We can't run ExecutorEnd 'till we're done printing the stats... */ totaltime += elapsed_time(&starttime); } diff --git a/src/include/cdb/cdbtm.h b/src/include/cdb/cdbtm.h index e38edc5efe..793522c30d 100644 --- a/src/include/cdb/cdbtm.h +++ b/src/include/cdb/cdbtm.h @@ -298,9 +298,9 @@ extern volatile int *shmNumCommittedGxacts; extern bool IsDtxRecoveryProcess(void); -extern char *DtxStateToString(DtxState state); -extern char *DtxProtocolCommandToString(DtxProtocolCommand command); -extern char *DtxContextToString(DtxContext context); +extern const char *DtxStateToString(DtxState state); +extern const char *DtxProtocolCommandToString(DtxProtocolCommand command); +extern const char *DtxContextToString(DtxContext context); extern void dtxDeformGid(const char *gid, DistributedTransactionId *distribXid); extern void dtxFormGid(char *gid, DistributedTransactionId gxid); diff --git a/src/test/regress/expected/AOCO_Compression.out b/src/test/regress/expected/AOCO_Compression.out index 7fcfa10564..b04a127d33 100644 --- a/src/test/regress/expected/AOCO_Compression.out +++ b/src/test/regress/expected/AOCO_Compression.out @@ -3680,14 +3680,15 @@ SELECT * FROM pg_compression WHERE compname='rle_type'; DROP type if exists mood_encoded cascade; CREATE TYPE mood_encoded AS ENUM ('sad', 'ok', 'happy'); ALTER TYPE public.mood_encoded SET DEFAULT ENCODING (compresstype=zlib, blocksize=65536, compresslevel=4); +ERROR: mood_encoded is not a base type SELECT t.typname, te.typoptions FROM pg_type t LEFT JOIN pg_type_encoding te ON (t.oid=te.typid) WHERE t.typname in ('mood_encoded', '_mood_encoded'); - typname | typoptions ----------------+----------------------------------------------------- - _mood_encoded | {compresstype=zlib,blocksize=65536,compresslevel=4} - mood_encoded | {compresstype=zlib,blocksize=65536,compresslevel=4} + typname | typoptions +---------------+------------ + mood_encoded | + _mood_encoded | (2 rows) -- get_ao_compression_ratio when the relation is a partitioned table with ao table children. @@ -3703,7 +3704,7 @@ PARTITION BY RANGE (sales_date) ); SELECT get_ao_compression_ratio('public.partitioned_table_14876'); get_ao_compression_ratio ------------- +-------------------------- -1 (1 row) diff --git a/src/test/regress/expected/aggregates_optimizer.out b/src/test/regress/expected/aggregates_optimizer.out index ee9d88c873..b3cdac99b4 100644 --- a/src/test/regress/expected/aggregates_optimizer.out +++ b/src/test/regress/expected/aggregates_optimizer.out @@ -2827,6 +2827,7 @@ INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL +NOTICE: avg_transfn called with 1 NOTICE: avg_transfn called with 3 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL @@ -2834,6 +2835,7 @@ INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL +NOTICE: avg_transfn called with 3 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner @@ -2866,6 +2868,8 @@ INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL +NOTICE: avg_transfn called with 1 +NOTICE: avg_transfn called with 3 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner @@ -2928,13 +2932,13 @@ DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 3 +NOTICE: avg_transfn called with 3 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL -NOTICE: avg_transfn called with 3 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner @@ -2953,22 +2957,22 @@ discard plans; select my_avg(one),my_sum(two) from (values(1,2),(3,4)) t(one,two); INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL -NOTICE: avg_transfn called with 2 +NOTICE: avg_transfn called with 1 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL -NOTICE: avg_transfn called with 1 -NOTICE: avg_transfn called with 4 +NOTICE: avg_transfn called with 2 +NOTICE: avg_transfn called with 3 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL -NOTICE: avg_transfn called with 3 +NOTICE: avg_transfn called with 4 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner diff --git a/src/test/regress/expected/partition.out b/src/test/regress/expected/partition.out index 47f87ea7fc..5fc25d0e0c 100755 --- a/src/test/regress/expected/partition.out +++ b/src/test/regress/expected/partition.out @@ -6053,3 +6053,533 @@ select count(*) from pg_class where relname like 'temp_parent_%'; 0 (1 row) +-- 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); + gp_debug_set_create_table_default_numsegments +----------------------------------------------- + 1 +(1 row) + +CREATE TABLE expanded_parent(a int, b int) PARTITION BY RANGE(b) (START (0) END (6) EVERY (3)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE expanded_parent2(a int, b int) PARTITION BY RANGE(b) (START (0) END (6) EVERY (3)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE unexpanded_parent(a int, b int) PARTITION BY RANGE(b) (START (0) END (6) EVERY (3)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE unexpanded_attach(a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE unexpanded_attach2(a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE expanded_attach(a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE expanded_attach2(a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +SELECT gp_debug_reset_create_table_default_numsegments(); + gp_debug_reset_create_table_default_numsegments +------------------------------------------------- + +(1 row) + +-- 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); +ERROR: distribution policy for "unexpanded_attach" must be the same as that for "expanded_parent" +-- 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); +ERROR: distribution policy for "unexpanded_attach2" must be the same as that for "expanded_parent2" +-- 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); +ERROR: distribution policy for "expanded_attach" must be the same as that for "unexpanded_parent" +-- 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; +NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE part_inherit_other_role IN ROLE part_inherit_role; +NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE part_inherit_priv_role; +NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE part_inherit_attach_priv_role; +NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE part_inherit_exchange_out_priv_role; +NOTICE: resource queue required -- using default resource queue "pg_default" +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); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +--------------------------------------------------+-------------------+---------+--------+----------+-------------------+------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | t | t | t + part_inherit_1_prt_l1_child1 | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_child1_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_default | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_default_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_exchange | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_exchange_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(9 rows) + +-- 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); +NOTICE: table has parent, setting distribution columns to match parent table +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'; + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +---------------------+-------------------+---------+--------+----------+-------------------------+------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_partof | {compresslevel=7} | r | ao_row | t | part_inherit_other_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(1 row) + +-- 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); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +ALTER TABLE part_inherit ATTACH PARTITION part_inherit_attach FOR VALUES FROM (400) TO (500); +ERROR: child table is missing constraint "con1" +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)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +-- 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'; + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +---------------------+-----------------+---------+------+----------+-------------------------+-------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_attach | {fillfactor=30} | r | heap | t | part_inherit_other_role | {part_inherit_other_role=arwdDxt/part_inherit_other_role,part_inherit_attach_priv_role=w/part_inherit_other_role} | 2 | f | t | f +(1 row) + +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +-----------------------------------------+-------------------+---------+--------+----------+-------------------+------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_1_prt_added | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_added_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(2 rows) + +-- 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); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +ALTER TABLE part_inherit_1_prt_l1_to_exchange EXCHANGE PARTITION l2_child WITH TABLE part_inherit_exchange_out; +ERROR: child table is missing constraint "con1" +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)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +--------------------------------------------------+-------------------+---------+--------+----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_1_prt_l1_to_exchange | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_exchange_2_prt_l2_child | {fillfactor=30} | r | heap | t | part_inherit_other_role | {part_inherit_other_role=arwdDxt/part_inherit_other_role,part_inherit_exchange_out_priv_role=w/part_inherit_other_role} | 2 | f | t | f + part_inherit_exchange_out | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(3 rows) + +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +---------------------------------------------+-------------------+---------+--------+----------+-------------------+------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_1_prt_l1_to_split | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_split1 | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_split2 | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(3 rows) + +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +--------------------------------------------------+-------------------+---------+--------+----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_1_prt_l1_to_exchange_2_prt_l2_child | {fillfactor=30} | r | heap | t | part_inherit_other_role | {part_inherit_other_role=arwdDxt/part_inherit_other_role,part_inherit_exchange_out_priv_role=w/part_inherit_other_role} | 2 | f | t | f + part_inherit_attach | {fillfactor=30} | r | heap | t | part_inherit_other_role | {part_inherit_other_role=arwdDxt/part_inherit_other_role,part_inherit_attach_priv_role=w/part_inherit_other_role} | 2 | f | t | f + part_inherit_partof | {compresslevel=7} | r | ao_row | t | part_inherit_other_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | t | t | t + part_inherit_1_prt_added | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_added_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_child1 | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_child1_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_default | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_default_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_exchange | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_split1 | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_split2 | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_exchange_out | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(15 rows) + +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; +NOTICE: table "test_rangepartition" does not exist, skipping +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 + ); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'datedday' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +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'); + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Append + -> Seq Scan on test_rangepartition_1_prt_pn_20221022 test_rangepartition_1 + Filter: ((datedday = '10-23-2022'::date) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + -> Seq Scan on test_rangepartition_1_prt_pn_20221023 test_rangepartition_2 + Filter: ((datedday = '10-23-2022'::date) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Postgres query optimizer +(9 rows) + +select max(datedday) from public.test_rangePartition where datedday='2022-10-23' or datedday=('2022-10-23'::date -interval '1 day'); + max +------------ + 10-23-2022 +(1 row) + +--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'; + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 2:1 (slice1; segments: 2) + -> Partial Aggregate + -> Append + -> Seq Scan on test_rangepartition_1_prt_pn_20221022 test_rangepartition_1 + Filter: ((datedday = '10-23-2022'::date) OR (datedday = '10-22-2022'::date)) + -> Seq Scan on test_rangepartition_1_prt_pn_20221023 test_rangepartition_2 + Filter: ((datedday = '10-23-2022'::date) OR (datedday = '10-22-2022'::date)) + Optimizer: Postgres query optimizer +(9 rows) + +select max(datedday) from public.test_rangePartition where datedday='2022-10-23' or datedday='2022-10-22'; + max +------------ + 10-23-2022 +(1 row) + +--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'); + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Append + -> Seq Scan on test_rangepartition_1_prt_pn_20221022 test_rangepartition_1 + Filter: ((datedday = 'Sun Oct 23 00:00:00 2022'::timestamp without time zone) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + -> Seq Scan on test_rangepartition_1_prt_pn_20221023 test_rangepartition_2 + Filter: ((datedday = 'Sun Oct 23 00:00:00 2022'::timestamp without time zone) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Postgres query optimizer +(9 rows) + +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'); + max +------------ + 10-23-2022 +(1 row) + +--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'); + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + -> Append + -> Seq Scan on test_rangepartition_1_prt_pn_20221022 test_rangepartition_1 + Filter: ((datedday = '10-23-2022'::date) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + -> Seq Scan on test_rangepartition_1_prt_pn_20221023 test_rangepartition_2 + Filter: ((datedday = '10-23-2022'::date) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Postgres query optimizer +(7 rows) + +select datedday from public.test_rangePartition where datedday='2022-10-23' or datedday=('2022-10-23'::date -interval '1 day'); + datedday +------------ + 10-22-2022 + 10-23-2022 +(2 rows) + +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; +NOTICE: table "test_listpartition" does not exist, skipping +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 ); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +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'); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Append + -> Seq Scan on test_listpartition_1_prt_p1 test_listpartition_1 + Filter: ((d = '10-23-2022'::date) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + -> Seq Scan on test_listpartition_1_prt_p2 test_listpartition_2 + Filter: ((d = '10-23-2022'::date) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Postgres query optimizer +(9 rows) + +select max(d) from test_listPartition where d='2022-10-23' or d=('2022-10-23'::date -interval '1 day'); + max +------------ + 10-23-2022 +(1 row) + +--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'; + QUERY PLAN +------------------------------------------------------------------------------------------ + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Append + -> Seq Scan on test_listpartition_1_prt_p1 test_listpartition_1 + Filter: ((d = '10-23-2022'::date) OR (d = '10-22-2022'::date)) + -> Seq Scan on test_listpartition_1_prt_p2 test_listpartition_2 + Filter: ((d = '10-23-2022'::date) OR (d = '10-22-2022'::date)) + Optimizer: Postgres query optimizer +(9 rows) + +select max(d) from test_listPartition where d='2022-10-23' or d='2022-10-22'; + max +------------ + 10-23-2022 +(1 row) + +--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'); + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Append + -> Seq Scan on test_listpartition_1_prt_p1 test_listpartition_1 + Filter: ((d = 'Sun Oct 23 00:00:00 2022'::timestamp without time zone) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + -> Seq Scan on test_listpartition_1_prt_p2 test_listpartition_2 + Filter: ((d = 'Sun Oct 23 00:00:00 2022'::timestamp without time zone) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Postgres query optimizer +(9 rows) + +select max(d) from test_listPartition where d=('2022-10-23'::date -interval '0 day') or d=('2022-10-23'::date -interval '1 day'); + max +------------ + 10-23-2022 +(1 row) + +--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'); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + -> Append + -> Seq Scan on test_listpartition_1_prt_p1 test_listpartition_1 + Filter: ((d = '10-23-2022'::date) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + -> Seq Scan on test_listpartition_1_prt_p2 test_listpartition_2 + Filter: ((d = '10-23-2022'::date) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Postgres query optimizer +(7 rows) + +select d from test_listPartition where d='2022-10-23' or d=('2022-10-23'::date -interval '1 day'); + d +------------ + 10-22-2022 + 10-23-2022 +(2 rows) + +drop table test_listPartition; diff --git a/src/test/regress/expected/partition_optimizer.out b/src/test/regress/expected/partition_optimizer.out index 2aa66e1a92..8ff25b93d9 100755 --- a/src/test/regress/expected/partition_optimizer.out +++ b/src/test/regress/expected/partition_optimizer.out @@ -6043,3 +6043,546 @@ select count(*) from pg_class where relname like 'temp_parent_%'; 0 (1 row) +-- 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); + gp_debug_set_create_table_default_numsegments +----------------------------------------------- + 1 +(1 row) + +CREATE TABLE expanded_parent(a int, b int) PARTITION BY RANGE(b) (START (0) END (6) EVERY (3)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE expanded_parent2(a int, b int) PARTITION BY RANGE(b) (START (0) END (6) EVERY (3)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE unexpanded_parent(a int, b int) PARTITION BY RANGE(b) (START (0) END (6) EVERY (3)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE unexpanded_attach(a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE unexpanded_attach2(a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE expanded_attach(a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +CREATE TABLE expanded_attach2(a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +SELECT gp_debug_reset_create_table_default_numsegments(); + gp_debug_reset_create_table_default_numsegments +------------------------------------------------- + +(1 row) + +-- 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); +ERROR: distribution policy for "unexpanded_attach" must be the same as that for "expanded_parent" +-- 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); +ERROR: distribution policy for "unexpanded_attach2" must be the same as that for "expanded_parent2" +-- 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); +ERROR: distribution policy for "expanded_attach" must be the same as that for "unexpanded_parent" +-- 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; +NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE part_inherit_other_role IN ROLE part_inherit_role; +NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE part_inherit_priv_role; +NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE part_inherit_attach_priv_role; +NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE part_inherit_exchange_out_priv_role; +NOTICE: resource queue required -- using default resource queue "pg_default" +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); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +--------------------------------------------------+-------------------+---------+--------+----------+-------------------+------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | t | t | t + part_inherit_1_prt_l1_child1 | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_child1_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_default | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_default_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_exchange | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_exchange_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(9 rows) + +-- 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); +NOTICE: table has parent, setting distribution columns to match parent table +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'; + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +---------------------+-------------------+---------+--------+----------+-------------------------+------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_partof | {compresslevel=7} | r | ao_row | t | part_inherit_other_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(1 row) + +-- 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); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +ALTER TABLE part_inherit ATTACH PARTITION part_inherit_attach FOR VALUES FROM (400) TO (500); +ERROR: child table is missing constraint "con1" +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)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +-- 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'; + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +---------------------+-----------------+---------+------+----------+-------------------------+-------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_attach | {fillfactor=30} | r | heap | t | part_inherit_other_role | {part_inherit_other_role=arwdDxt/part_inherit_other_role,part_inherit_attach_priv_role=w/part_inherit_other_role} | 2 | f | t | f +(1 row) + +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +-----------------------------------------+-------------------+---------+--------+----------+-------------------+------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_1_prt_added | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_added_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(2 rows) + +-- 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); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +ALTER TABLE part_inherit_1_prt_l1_to_exchange EXCHANGE PARTITION l2_child WITH TABLE part_inherit_exchange_out; +ERROR: child table is missing constraint "con1" +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)); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +--------------------------------------------------+-------------------+---------+--------+----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_1_prt_l1_to_exchange_2_prt_l2_child | {fillfactor=30} | r | heap | t | part_inherit_other_role | {part_inherit_other_role=arwdDxt/part_inherit_other_role,part_inherit_exchange_out_priv_role=w/part_inherit_other_role} | 2 | f | t | f + part_inherit_1_prt_l1_to_exchange | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_exchange_out | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(3 rows) + +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +---------------------------------------------+-------------------+---------+--------+----------+-------------------+------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_1_prt_l1_to_split | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_split1 | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_split2 | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(3 rows) + +-- 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'); + relname | reloptions | relkind | am | hasindex | owner | acl | numchecks | hasrules | hastriggers | rowsecurity +--------------------------------------------------+-------------------+---------+--------+----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------+------------- + part_inherit_1_prt_l1_to_exchange_2_prt_l2_child | {fillfactor=30} | r | heap | t | part_inherit_other_role | {part_inherit_other_role=arwdDxt/part_inherit_other_role,part_inherit_exchange_out_priv_role=w/part_inherit_other_role} | 2 | f | t | f + part_inherit_attach | {fillfactor=30} | r | heap | t | part_inherit_other_role | {part_inherit_other_role=arwdDxt/part_inherit_other_role,part_inherit_attach_priv_role=w/part_inherit_other_role} | 2 | f | t | f + part_inherit_partof | {compresslevel=7} | r | ao_row | t | part_inherit_other_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | t | t | t + part_inherit_1_prt_added | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_added_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_child1 | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_child1_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_default | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_default_2_prt_l2_child | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_exchange | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split | {compresslevel=7} | p | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_split1 | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_1_prt_l1_to_split_2_prt_split2 | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f + part_inherit_exchange_out | {compresslevel=7} | r | ao_row | t | part_inherit_role | {part_inherit_role=arwdDxt/part_inherit_role,part_inherit_priv_role=w/part_inherit_role} | 1 | f | t | f +(15 rows) + +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; +NOTICE: table "test_rangepartition" does not exist, skipping +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 + ); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'datedday' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +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'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_rangepartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Dynamic Seq Scan on test_rangepartition + Number of partitions to scan: 2 + Filter: ((datedday = '10-23-2022'::date) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Pivotal Optimizer (GPORCA) +(7 rows) + +select max(datedday) from public.test_rangePartition where datedday='2022-10-23' or datedday=('2022-10-23'::date -interval '1 day'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_rangepartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + max +------------ + 10-23-2022 +(1 row) + +--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'; +NOTICE: One or more columns in the following table(s) do not have statistics: test_rangepartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + QUERY PLAN +-------------------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Dynamic Seq Scan on test_rangepartition + Number of partitions to scan: 2 + Filter: ((datedday = '10-23-2022'::date) OR (datedday = '10-22-2022'::date)) + Optimizer: Pivotal Optimizer (GPORCA) +(7 rows) + +select max(datedday) from public.test_rangePartition where datedday='2022-10-23' or datedday='2022-10-22'; +NOTICE: One or more columns in the following table(s) do not have statistics: test_rangepartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + max +------------ + 10-23-2022 +(1 row) + +--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'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_rangepartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Dynamic Seq Scan on test_rangepartition + Number of partitions to scan: 2 + Filter: ((datedday = 'Sun Oct 23 00:00:00 2022'::timestamp without time zone) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Pivotal Optimizer (GPORCA) +(7 rows) + +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'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_rangepartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + max +------------ + 10-23-2022 +(1 row) + +--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'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_rangepartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + -> Dynamic Seq Scan on test_rangepartition + Number of partitions to scan: 2 + Filter: ((datedday = '10-23-2022'::date) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +select datedday from public.test_rangePartition where datedday='2022-10-23' or datedday=('2022-10-23'::date -interval '1 day'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_rangepartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + datedday +------------ + 10-22-2022 + 10-23-2022 +(2 rows) + +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; +NOTICE: table "test_listpartition" does not exist, skipping +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 ); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Cloudberry Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +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'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_listpartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Dynamic Seq Scan on test_listpartition + Number of partitions to scan: 2 + Filter: ((d = '10-23-2022'::date) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Pivotal Optimizer (GPORCA) +(6 rows) + +select max(d) from test_listPartition where d='2022-10-23' or d=('2022-10-23'::date -interval '1 day'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_listpartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + max +------------ + 10-23-2022 +(1 row) + +--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'; +NOTICE: One or more columns in the following table(s) do not have statistics: test_listpartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Dynamic Seq Scan on test_listpartition + Number of partitions to scan: 2 + Filter: ((d = '10-23-2022'::date) OR (d = '10-22-2022'::date)) + Optimizer: Pivotal Optimizer (GPORCA) +(6 rows) + +select max(d) from test_listPartition where d='2022-10-23' or d='2022-10-22'; +NOTICE: One or more columns in the following table(s) do not have statistics: test_listpartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + max +------------ + 10-23-2022 +(1 row) + +--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'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_listpartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Dynamic Seq Scan on test_listpartition + Number of partitions to scan: 2 + Filter: ((d = 'Sun Oct 23 00:00:00 2022'::timestamp without time zone) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Pivotal Optimizer (GPORCA) +(6 rows) + +select max(d) from test_listPartition where d=('2022-10-23'::date -interval '0 day') or d=('2022-10-23'::date -interval '1 day'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_listpartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + max +------------ + 10-23-2022 +(1 row) + +--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'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_listpartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + QUERY PLAN +------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + -> Dynamic Seq Scan on test_listpartition + Number of partitions to scan: 2 + Filter: ((d = '10-23-2022'::date) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) + Optimizer: Pivotal Optimizer (GPORCA) +(5 rows) + +select d from test_listPartition where d='2022-10-23' or d=('2022-10-23'::date -interval '1 day'); +NOTICE: One or more columns in the following table(s) do not have statistics: test_listpartition +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. + d +------------ + 10-23-2022 + 10-22-2022 +(2 rows) + +drop table test_listPartition; diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 8d6513f137..6fcdd5600b 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -3777,7 +3777,7 @@ explain (costs off) select * from pp_enumpart where a = 'blue'; QUERY PLAN ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Seq Scan on pp_enumpart_blue + -> Seq Scan on pp_enumpart_blue pp_enumpart Filter: (a = 'blue'::pp_colors) Optimizer: Postgres query optimizer (4 rows) diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 43a4c86696..eba920a66d 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -1598,17 +1598,14 @@ CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation -CONTEXT: SQL function "mv_action" statement 1 \c - REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation -CONTEXT: SQL function "mv_action" statement 1 BEGIN; SET allow_segment_DML = ON; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; ERROR: must have admin option on role "regress_priv_group2" CONTEXT: SQL function "unwanted_grant" statement 1 SQL statement "SELECT unwanted_grant()" PL/pgSQL function sro_trojan() line 1 at PERFORM -SQL function "mv_action" statement 1 -- REFRESH MATERIALIZED VIEW CONCURRENTLY use of eval_const_expressions() SET SESSION AUTHORIZATION regress_sro_user; CREATE FUNCTION unwanted_grant_nofail(int) RETURNS int @@ -2560,7 +2557,6 @@ CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation -CONTEXT: SQL function "mv_action" statement 1 \c - REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation @@ -2570,7 +2566,6 @@ ERROR: must have admin option on role "regress_priv_group2" CONTEXT: SQL function "unwanted_grant" statement 1 SQL statement "SELECT unwanted_grant()" PL/pgSQL function sro_trojan() line 1 at PERFORM -SQL function "mv_action" statement 1 DROP OWNED BY regress_sro_user; DROP ROLE regress_sro_user; -- Test sandbox escape with CVE-2020-25695 on Cloudberry. diff --git a/src/test/regress/output/alter_db_set_tablespace.source b/src/test/regress/output/alter_db_set_tablespace.source index 407b10078c..c469a3ab1d 100644 --- a/src/test/regress/output/alter_db_set_tablespace.source +++ b/src/test/regress/output/alter_db_set_tablespace.source @@ -1278,7 +1278,7 @@ SELECT gp_inject_fault('ckpt_loop_begin', 'infinite_loop', mirror0()); show fsync; fsync ------- - off + on (1 row) --- start_ignore diff --git a/src/test/regress/output/external_table.source b/src/test/regress/output/external_table.source index e518f03934..8977af744a 100644 --- a/src/test/regress/output/external_table.source +++ b/src/test/regress/output/external_table.source @@ -1829,9 +1829,6 @@ NOTICE: found 2 data formatting errors (2 or more input rows), rejected related ERROR: segment reject limit reached, aborting operation DETAIL: Last error was: invalid input syntax for type integer: "error_1", column i (seg0 slice1 127.0.0.1:40000 pid=12226) CONTEXT: External table exttab_udfs_2, line 7 of file://@hostname@@abs_srcdir@/data/exttab_more_errors.data, column i -SQL statement "INSERT INTO exttab_udfs_insert_2 - SELECT i, j from exttab_udfs_2" -PL/pgSQL function exttab_udfs_func2() line 25 at SQL statement SELECT COUNT(*) > 0 FROM ( SELECT * FROM gp_read_error_log('exttab_udfs_1') @@ -1860,9 +1857,6 @@ NOTICE: found 2 data formatting errors (2 or more input rows), rejected related ERROR: segment reject limit reached, aborting operation DETAIL: Last error was: invalid input syntax for type integer: "error_1", column i (seg0 slice1 127.0.0.1:40000 pid=12217) CONTEXT: External table exttab_udfs_2, line 7 of file://@hostname@@abs_srcdir@/data/exttab_more_errors.data, column i -SQL statement "INSERT INTO exttab_udfs_insert_2 - SELECT i, j from exttab_udfs_2" -PL/pgSQL function exttab_udfs_func2() line 25 at SQL statement SELECT COUNT(*) > 0 FROM ( SELECT * FROM gp_read_error_log('exttab_udfs_1') @@ -1891,9 +1885,6 @@ NOTICE: found 2 data formatting errors (2 or more input rows), rejected related ERROR: segment reject limit reached, aborting operation DETAIL: Last error was: invalid input syntax for type integer: "error_1", column i (seg0 slice1 127.0.0.1:40000 pid=12226) CONTEXT: External table exttab_udfs_2, line 7 of file://@hostname@@abs_srcdir@/data/exttab_more_errors.data, column i -SQL statement "INSERT INTO exttab_udfs_insert_2 - SELECT i, j from exttab_udfs_2" -PL/pgSQL function exttab_udfs_func2() line 25 at SQL statement SELECT COUNT(*) > 0 FROM ( SELECT * FROM gp_read_error_log('exttab_udfs_1') @@ -1924,9 +1915,6 @@ NOTICE: found 2 data formatting errors (2 or more input rows), rejected related ERROR: segment reject limit reached, aborting operation DETAIL: Last error was: invalid input syntax for type integer: "error_1", column i (seg0 slice1 127.0.0.1:40000 pid=12217) CONTEXT: External table exttab_udfs_2, line 7 of file://@hostname@@abs_srcdir@/data/exttab_more_errors.data, column i -SQL statement "INSERT INTO exttab_udfs_insert_2 - SELECT i, j from exttab_udfs_2" -PL/pgSQL function exttab_udfs_func2() line 25 at SQL statement SELECT COUNT(*) > 0 FROM ( SELECT * FROM gp_read_error_log('exttab_udfs_1') diff --git a/src/test/regress/stdin b/src/test/regress/stdin deleted file mode 100644 index 14df008570..0000000000 --- a/src/test/regress/stdin +++ /dev/null @@ -1,2 +0,0 @@ -1 1 -1 2 diff --git a/src/test/regress/stdout b/src/test/regress/stdout deleted file mode 100644 index d03c99dafd..0000000000 --- a/src/test/regress/stdout +++ /dev/null @@ -1,4 +0,0 @@ -1 1 -1 2 -1 1 -1 2 --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
