This is an automated email from the ASF dual-hosted git repository. maxyang pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 8f94c0a1a71d5e8fdcd7fe22a2800331c99d8bd8 Author: Zhenglong Li <[email protected]> AuthorDate: Wed Jul 6 10:28:49 2022 +0800 Just use default on commit behavior to child tables when we create partition table (#13687) When we create a temporary table, we can use ON COMMIT to control the behavior of temporary tables at the end of a transaction block, such as DELETE ROWS, means delete all rows in this temporary table after this transaction is committed. But when used on a partitioned table, this is not cascaded to its partitions, so we can just use ONCOMMIT_NOOP (default behavior) when we create child partition tables. And If we use the parent on commit action, it will lead to inconsistent behavior between Greenplum and PostgreSQL, which we need to avoid. --- src/backend/parser/parse_partition_gp.c | 2 +- src/test/regress/expected/partition.out | 42 +++++++++++++++++++++++ src/test/regress/expected/partition_optimizer.out | 42 +++++++++++++++++++++++ src/test/regress/sql/partition.sql | 22 ++++++++++++ 4 files changed, 107 insertions(+), 1 deletion(-) diff --git a/src/backend/parser/parse_partition_gp.c b/src/backend/parser/parse_partition_gp.c index 9b2923b141..6d21d79fa8 100644 --- a/src/backend/parser/parse_partition_gp.c +++ b/src/backend/parser/parse_partition_gp.c @@ -864,7 +864,7 @@ makePartitionCreateStmt(Relation parentrel, char *partname, PartitionBoundSpec * childstmt->ofTypename = NULL; childstmt->constraints = NIL; childstmt->options = elem->options ? copyObject(elem->options) : NIL; - childstmt->oncommit = ONCOMMIT_NOOP; // FIXME: copy from parent stmt? + childstmt->oncommit = ONCOMMIT_NOOP; childstmt->tablespacename = elem->tablespacename ? pstrdup(elem->tablespacename) : NULL; childstmt->accessMethod = elem->accessMethod ? pstrdup(elem->accessMethod) : NULL; childstmt->if_not_exists = false; diff --git a/src/test/regress/expected/partition.out b/src/test/regress/expected/partition.out index d5ff8c52f1..895897a4c7 100755 --- a/src/test/regress/expected/partition.out +++ b/src/test/regress/expected/partition.out @@ -5946,3 +5946,45 @@ select count(*) from t_issue_547_ao; drop table t_issue_547_aoco; drop table t_issue_547_ao; +-- test on commit behavior used on partition table +-- test on commit delete rows +begin; +create temp table temp_parent (a int) partition by range(a) (start(1) end(10) every(10)) on commit delete rows; +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum 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 temp_parent select i from generate_series(1, 5) i; +select count(*) from temp_parent; + count +------- + 5 +(1 row) + +commit; +-- DELETE ROWS will not cascaded to its partitions when we use DELETE ROWS behavior +select count(*) from temp_parent; + count +------- + 5 +(1 row) + +drop table temp_parent; +-- test on commit drop +begin; +create temp table temp_parent (a int) partition by range(a) (start(1) end(10) every(1)) on commit drop; +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum 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 temp_parent select i from generate_series(1, 5) i; +select count(*) from pg_class where relname like 'temp_parent_%'; + count +------- + 9 +(1 row) + +commit; +-- no relations remain in this case. +select count(*) from pg_class where relname like 'temp_parent_%'; + count +------- + 0 +(1 row) + diff --git a/src/test/regress/expected/partition_optimizer.out b/src/test/regress/expected/partition_optimizer.out index 1b404d22a0..3905ac4a06 100755 --- a/src/test/regress/expected/partition_optimizer.out +++ b/src/test/regress/expected/partition_optimizer.out @@ -5946,3 +5946,45 @@ select count(*) from t_issue_547_ao; drop table t_issue_547_aoco; drop table t_issue_547_ao; +-- test on commit behavior used on partition table +-- test on commit delete rows +begin; +create temp table temp_parent (a int) partition by range(a) (start(1) end(10) every(10)) on commit delete rows; +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum 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 temp_parent select i from generate_series(1, 5) i; +select count(*) from temp_parent; + count +------- + 5 +(1 row) + +commit; +-- DELETE ROWS will not cascaded to its partitions when we use DELETE ROWS behavior +select count(*) from temp_parent; + count +------- + 5 +(1 row) + +drop table temp_parent; +-- test on commit drop +begin; +create temp table temp_parent (a int) partition by range(a) (start(1) end(10) every(1)) on commit drop; +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum 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 temp_parent select i from generate_series(1, 5) i; +select count(*) from pg_class where relname like 'temp_parent_%'; + count +------- + 9 +(1 row) + +commit; +-- no relations remain in this case. +select count(*) from pg_class where relname like 'temp_parent_%'; + count +------- + 0 +(1 row) + diff --git a/src/test/regress/sql/partition.sql b/src/test/regress/sql/partition.sql index bafea46f66..cebff356e2 100644 --- a/src/test/regress/sql/partition.sql +++ b/src/test/regress/sql/partition.sql @@ -3810,3 +3810,25 @@ select count(*) from t_issue_547_ao; drop table t_issue_547_aoco; drop table t_issue_547_ao; + +-- test on commit behavior used on partition table + +-- test on commit delete rows +begin; +create temp table temp_parent (a int) partition by range(a) (start(1) end(10) every(10)) on commit delete rows; +insert into temp_parent select i from generate_series(1, 5) i; +select count(*) from temp_parent; +commit; +-- DELETE ROWS will not cascaded to its partitions when we use DELETE ROWS behavior +select count(*) from temp_parent; +drop table temp_parent; + +-- test on commit drop +begin; +create temp table temp_parent (a int) partition by range(a) (start(1) end(10) every(1)) on commit drop; +insert into temp_parent select i from generate_series(1, 5) i; +select count(*) from pg_class where relname like 'temp_parent_%'; +commit; +-- no relations remain in this case. +select count(*) from pg_class where relname like 'temp_parent_%'; + --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
