This is an automated email from the ASF dual-hosted git repository. reshke pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 9d8d8c29f5d9a008b4be0752faeb1574155bd109 Author: Kevin.wyh <[email protected]> AuthorDate: Fri Sep 15 03:15:41 2023 +0800 Fix "cache lookup failed for foreign table" for multi-level partition table with foreign tables in Orca (#16376) This issue was introduced by #15706. When ORCA translates multi-level partition table Relcache to DXL, it searches for the storage type of the leaf table and checks if the foreign table is a greenplum_fdw table. However, the none-leaf table is also checked, and an ERROR is thrown because the none-leaf table is not a foreign table. Fix this issue by checking if the table is a foreign table first to avoid elog ERROR. Co-authored-by: wuyuhao28 <[email protected]> --- contrib/postgres_fdw/expected/gp_postgres_fdw.out | 167 +++++++++++++++++++ .../expected/gp_postgres_fdw_optimizer.out | 179 +++++++++++++++++++++ contrib/postgres_fdw/sql/gp_postgres_fdw.sql | 97 +++++++++++ .../gpopt/translate/CTranslatorRelcacheToDXL.cpp | 6 + 4 files changed, 449 insertions(+) diff --git a/contrib/postgres_fdw/expected/gp_postgres_fdw.out b/contrib/postgres_fdw/expected/gp_postgres_fdw.out index df4ecdbe43..f45d80af72 100644 --- a/contrib/postgres_fdw/expected/gp_postgres_fdw.out +++ b/contrib/postgres_fdw/expected/gp_postgres_fdw.out @@ -1207,3 +1207,170 @@ select * from part_mixed_dpe, non_part where part_mixed_dpe.b=non_part.b; 9 | 9 | 9 | 9 (2 rows) +-- compare difference plans among when mpp_execute set to 'all segments', 'coordinator' and 'any' +explain (costs false) update t1 set b = b + 1 where b in (select a from gp_all where gp_all.a > 10); + QUERY PLAN +--------------------------------------------------------------- + Update on t1 + -> Hash Semi Join + Hash Cond: (t1.b = gp_all.a) + -> Seq Scan on t1 + Filter: (b > 10) + -> Hash + -> Broadcast Motion 3:3 (slice1; segments: 3) + -> Foreign Scan on gp_all + Optimizer: Postgres query optimizer +(9 rows) + +explain (costs false) update t1 set b = b + 1 where b in (select a from gp_any where gp_any.a > 10); + QUERY PLAN +------------------------------------------ + Update on t1 + -> Hash Semi Join + Hash Cond: (t1.b = gp_any.a) + -> Seq Scan on t1 + Filter: (b > 10) + -> Hash + -> Foreign Scan on gp_any + Optimizer: Postgres query optimizer +(8 rows) + +explain (costs false) update t1 set b = b + 1 where b in (select a from gp_coord where gp_coord.a > 10); + QUERY PLAN +-------------------------------------------------- + Update on t1 + -> Hash Semi Join + Hash Cond: (t1.b = gp_coord.a) + -> Seq Scan on t1 + Filter: (b > 10) + -> Hash + -> Broadcast Motion 1:3 (slice1) + -> Foreign Scan on gp_coord + Optimizer: Postgres query optimizer +(9 rows) + +--- +--- Test for #16376 of multi-level partition table with foreign table +--- +CREATE TABLE sub_part ( + a int, + b int, + c int) + DISTRIBUTED BY (a) +partition by range(b) subpartition by list(c) + SUBPARTITION TEMPLATE + ( + SUBPARTITION one values (1), + SUBPARTITION two values (2) + ) +( + START (0) INCLUSIVE END (5) EXCLUSIVE EVERY (1) +); +-- Create foreign tables +CREATE FOREIGN TABLE sub_part_1_prt_1_2_prt_one_foreign ( + a int, + b int, + c int) +SERVER loopback; +CREATE FOREIGN TABLE sub_part_1_prt_1_2_prt_two_foreign ( + a int, + b int, + c int) +SERVER loopback; +-- change a sub partition's all leaf table to foreign table +ALTER TABLE sub_part_1_prt_1 EXCHANGE PARTITION for(1) WITH TABLE sub_part_1_prt_1_2_prt_one_foreign; +ALTER TABLE sub_part_1_prt_1 EXCHANGE PARTITION for(2) WITH TABLE sub_part_1_prt_1_2_prt_two_foreign; +-- explain with ORCA should fall back to planner, rather than raise ERROR +explain select * from sub_part; + QUERY PLAN +--------------------------------------------------------------------------------------------- + Append (cost=100.00..14631.81 rows=641404 width=12) + -> Foreign Scan on sub_part_1_prt_1_2_prt_one (cost=100.00..383.06 rows=9102 width=12) + -> Foreign Scan on sub_part_1_prt_1_2_prt_two (cost=100.00..383.06 rows=9102 width=12) + -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_2_2_prt_one (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_2_2_prt_two (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_3_2_prt_one (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice4; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_3_2_prt_two (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice5; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_4_2_prt_one (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice6; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_4_2_prt_two (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice7; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_5_2_prt_one (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice8; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_5_2_prt_two (cost=0.00..293.67 rows=25967 width=12) + Optimizer: Postgres query optimizer +(20 rows) + +--- Clean up +DROP TABLE sub_part; +DROP TABLE sub_part_1_prt_1_2_prt_one_foreign; +DROP TABLE sub_part_1_prt_1_2_prt_two_foreign; +-- GPDB #16219: validate scram-sha-256 in postgres_fdw +alter system set password_encryption = 'scram-sha-256'; +-- add created user to pg_hba.conf +\! echo "host all u16219 0.0.0.0/0 scram-sha-256" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf +\! echo "host all u16219 ::1/128 scram-sha-256" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf +\! echo "local all u16219 scram-sha-256" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf +select pg_reload_conf(); + pg_reload_conf +---------------- + t +(1 row) + +\c postgres +create user u16219 password '123456'; +create database database_16219; +\c database_16219 +create extension postgres_fdw; +grant usage on FOREIGN DATA WRAPPER postgres_fdw to public; +set role u16219; +create table t1 (a int, b int); +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 t1 values(generate_series(1,10),generate_series(11,20)); +DO $d$ + BEGIN + EXECUTE $$CREATE SERVER database_16219 FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname '$$||current_database()||$$', + port '$$||current_setting('port')||$$', + host 'localhost' + )$$; + END; +$d$; +CREATE USER MAPPING FOR CURRENT_USER SERVER database_16219 + OPTIONS (user 'u16219', password '123456'); +CREATE FOREIGN TABLE f_t1(a int, b int) + server database_16219 options(schema_name 'public', table_name 't1'); +select count(*) from f_t1; + count +------- + 10 +(1 row) + +DO $d$ + BEGIN + EXECUTE $$ALTER SERVER database_16219 + OPTIONS (SET port '$$||current_setting('port')||$$')$$; + END; +$d$; +select count(*) from f_t1; + count +------- + 10 +(1 row) + +\c postgres +drop database database_16219; +drop user u16219; +alter system reset password_encryption; +select pg_reload_conf(); + pg_reload_conf +---------------- + t +(1 row) + diff --git a/contrib/postgres_fdw/expected/gp_postgres_fdw_optimizer.out b/contrib/postgres_fdw/expected/gp_postgres_fdw_optimizer.out index 8c3a070f6b..20bc6be3f6 100644 --- a/contrib/postgres_fdw/expected/gp_postgres_fdw_optimizer.out +++ b/contrib/postgres_fdw/expected/gp_postgres_fdw_optimizer.out @@ -1223,3 +1223,182 @@ select * from part_mixed_dpe, non_part where part_mixed_dpe.b=non_part.b; 9 | 9 | 9 | 9 (2 rows) +-- compare difference plans among when mpp_execute set to 'all segments', 'coordinator' and 'any' +explain (costs false) update t1 set b = b + 1 where b in (select a from gp_all where gp_all.a > 10); + QUERY PLAN +--------------------------------------------------------------- + Update on t1 + -> Hash Semi Join + Hash Cond: (t1.b = a) + -> Seq Scan on t1 + -> Hash + -> Broadcast Motion 3:3 (slice1; segments: 3) + -> Foreign Scan on gp_all + Optimizer: Pivotal Optimizer (GPORCA) +(8 rows) + +explain (costs false) update t1 set b = b + 1 where b in (select a from gp_any where gp_any.a > 10); + QUERY PLAN +------------------------------------------------------------------------ + Update on t1 + -> Hash Semi Join + Hash Cond: (t1.b = a) + -> Seq Scan on t1 + -> Hash + -> Foreign Scan on gp_any + Optimizer: Pivotal Optimizer (GPORCA) +(7 rows) + +explain (costs false) update t1 set b = b + 1 where b in (select a from gp_coord where gp_coord.a > 10); + QUERY PLAN +----------------------------------------------------------------------------------------------- + Update on t1 + -> Result + -> Redistribute Motion 3:3 (slice1; segments: 3) + Hash Key: t1.a + -> Hash Join + Hash Cond: (t1.b = a) + -> Redistribute Motion 3:3 (slice2; segments: 3) + Hash Key: t1.b + -> Seq Scan on t1 + -> Hash + -> GroupAggregate + Group Key: a + -> Sort + Sort Key: a + -> Redistribute Motion 3:3 (slice3; segments: 3) + Hash Key: a + -> GroupAggregate + Group Key: a + -> Sort + Sort Key: a + -> Redistribute Motion 1:3 (slice4) + -> Foreign Scan on gp_coord + Optimizer: Pivotal Optimizer (GPORCA) +(23 rows) + +--- +--- Test for #16376 of multi-level partition table with foreign table +--- +CREATE TABLE sub_part ( + a int, + b int, + c int) + DISTRIBUTED BY (a) +partition by range(b) subpartition by list(c) + SUBPARTITION TEMPLATE + ( + SUBPARTITION one values (1), + SUBPARTITION two values (2) + ) +( + START (0) INCLUSIVE END (5) EXCLUSIVE EVERY (1) +); +-- Create foreign tables +CREATE FOREIGN TABLE sub_part_1_prt_1_2_prt_one_foreign ( + a int, + b int, + c int) +SERVER loopback; +CREATE FOREIGN TABLE sub_part_1_prt_1_2_prt_two_foreign ( + a int, + b int, + c int) +SERVER loopback; +-- change a sub partition's all leaf table to foreign table +ALTER TABLE sub_part_1_prt_1 EXCHANGE PARTITION for(1) WITH TABLE sub_part_1_prt_1_2_prt_one_foreign; +ALTER TABLE sub_part_1_prt_1 EXCHANGE PARTITION for(2) WITH TABLE sub_part_1_prt_1_2_prt_two_foreign; +-- explain with ORCA should fall back to planner, rather than raise ERROR +explain select * from sub_part; + QUERY PLAN +--------------------------------------------------------------------------------------------- + Append (cost=100.00..14631.81 rows=641404 width=12) + -> Foreign Scan on sub_part_1_prt_1_2_prt_one (cost=100.00..383.06 rows=9102 width=12) + -> Foreign Scan on sub_part_1_prt_1_2_prt_two (cost=100.00..383.06 rows=9102 width=12) + -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_2_2_prt_one (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_2_2_prt_two (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_3_2_prt_one (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice4; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_3_2_prt_two (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice5; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_4_2_prt_one (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice6; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_4_2_prt_two (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice7; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_5_2_prt_one (cost=0.00..293.67 rows=25967 width=12) + -> Gather Motion 3:1 (slice8; segments: 3) (cost=0.00..1332.33 rows=77900 width=12) + -> Seq Scan on sub_part_1_prt_5_2_prt_two (cost=0.00..293.67 rows=25967 width=12) + Optimizer: Postgres query optimizer +(20 rows) + +--- Clean up +DROP TABLE sub_part; +DROP TABLE sub_part_1_prt_1_2_prt_one_foreign; +DROP TABLE sub_part_1_prt_1_2_prt_two_foreign; +-- GPDB #16219: validate scram-sha-256 in postgres_fdw +alter system set password_encryption = 'scram-sha-256'; +-- add created user to pg_hba.conf +\! echo "host all u16219 0.0.0.0/0 scram-sha-256" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf +\! echo "host all u16219 ::1/128 scram-sha-256" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf +\! echo "local all u16219 scram-sha-256" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf +select pg_reload_conf(); + pg_reload_conf +---------------- + t +(1 row) + +\c postgres +create user u16219 password '123456'; +create database database_16219; +\c database_16219 +create extension postgres_fdw; +grant usage on FOREIGN DATA WRAPPER postgres_fdw to public; +set role u16219; +create table t1 (a int, b int); +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 t1 values(generate_series(1,10),generate_series(11,20)); +DO $d$ + BEGIN + EXECUTE $$CREATE SERVER database_16219 FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname '$$||current_database()||$$', + port '$$||current_setting('port')||$$', + host 'localhost' + )$$; + END; +$d$; +CREATE USER MAPPING FOR CURRENT_USER SERVER database_16219 + OPTIONS (user 'u16219', password '123456'); +CREATE FOREIGN TABLE f_t1(a int, b int) + server database_16219 options(schema_name 'public', table_name 't1'); +select count(*) from f_t1; + count +------- + 10 +(1 row) + +DO $d$ + BEGIN + EXECUTE $$ALTER SERVER database_16219 + OPTIONS (SET port '$$||current_setting('port')||$$')$$; + END; +$d$; +select count(*) from f_t1; + count +------- + 10 +(1 row) + +\c postgres +drop database database_16219; +drop user u16219; +alter system reset password_encryption; +select pg_reload_conf(); + pg_reload_conf +---------------- + t +(1 row) + diff --git a/contrib/postgres_fdw/sql/gp_postgres_fdw.sql b/contrib/postgres_fdw/sql/gp_postgres_fdw.sql index d66ffb6e1c..e259ae07a3 100644 --- a/contrib/postgres_fdw/sql/gp_postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/gp_postgres_fdw.sql @@ -321,3 +321,100 @@ insert into part_mixed_dpe select 6,6 from generate_series(1,10); analyze part_mixed_dpe; explain select * from part_mixed_dpe, non_part where part_mixed_dpe.b=non_part.b; select * from part_mixed_dpe, non_part where part_mixed_dpe.b=non_part.b; + +-- compare difference plans among when mpp_execute set to 'all segments', 'coordinator' and 'any' +explain (costs false) update t1 set b = b + 1 where b in (select a from gp_all where gp_all.a > 10); +explain (costs false) update t1 set b = b + 1 where b in (select a from gp_any where gp_any.a > 10); +explain (costs false) update t1 set b = b + 1 where b in (select a from gp_coord where gp_coord.a > 10); + +--- +--- Test for #16376 of multi-level partition table with foreign table +--- +CREATE TABLE sub_part ( + a int, + b int, + c int) + DISTRIBUTED BY (a) +partition by range(b) subpartition by list(c) + SUBPARTITION TEMPLATE + ( + SUBPARTITION one values (1), + SUBPARTITION two values (2) + ) +( + START (0) INCLUSIVE END (5) EXCLUSIVE EVERY (1) +); + +-- Create foreign tables +CREATE FOREIGN TABLE sub_part_1_prt_1_2_prt_one_foreign ( + a int, + b int, + c int) +SERVER loopback; + +CREATE FOREIGN TABLE sub_part_1_prt_1_2_prt_two_foreign ( + a int, + b int, + c int) +SERVER loopback; + +-- change a sub partition's all leaf table to foreign table +ALTER TABLE sub_part_1_prt_1 EXCHANGE PARTITION for(1) WITH TABLE sub_part_1_prt_1_2_prt_one_foreign; +ALTER TABLE sub_part_1_prt_1 EXCHANGE PARTITION for(2) WITH TABLE sub_part_1_prt_1_2_prt_two_foreign; + +-- explain with ORCA should fall back to planner, rather than raise ERROR +explain select * from sub_part; + +--- Clean up +DROP TABLE sub_part; +DROP TABLE sub_part_1_prt_1_2_prt_one_foreign; +DROP TABLE sub_part_1_prt_1_2_prt_two_foreign; + +-- GPDB #16219: validate scram-sha-256 in postgres_fdw +alter system set password_encryption = 'scram-sha-256'; +-- add created user to pg_hba.conf +\! echo "host all u16219 0.0.0.0/0 scram-sha-256" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf +\! echo "host all u16219 ::1/128 scram-sha-256" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf +\! echo "local all u16219 scram-sha-256" >> $COORDINATOR_DATA_DIRECTORY/pg_hba.conf +select pg_reload_conf(); +\c postgres +create user u16219 password '123456'; + +create database database_16219; +\c database_16219 +create extension postgres_fdw; +grant usage on FOREIGN DATA WRAPPER postgres_fdw to public; + +set role u16219; +create table t1 (a int, b int); +insert into t1 values(generate_series(1,10),generate_series(11,20)); + +DO $d$ + BEGIN + EXECUTE $$CREATE SERVER database_16219 FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname '$$||current_database()||$$', + port '$$||current_setting('port')||$$', + host 'localhost' + )$$; + END; +$d$; + +CREATE USER MAPPING FOR CURRENT_USER SERVER database_16219 + OPTIONS (user 'u16219', password '123456'); + +CREATE FOREIGN TABLE f_t1(a int, b int) + server database_16219 options(schema_name 'public', table_name 't1'); + +select count(*) from f_t1; +DO $d$ + BEGIN + EXECUTE $$ALTER SERVER database_16219 + OPTIONS (SET port '$$||current_setting('port')||$$')$$; + END; +$d$; +select count(*) from f_t1; +\c postgres +drop database database_16219; +drop user u16219; +alter system reset password_encryption; +select pg_reload_conf(); diff --git a/src/backend/gpopt/translate/CTranslatorRelcacheToDXL.cpp b/src/backend/gpopt/translate/CTranslatorRelcacheToDXL.cpp index bca58a4c2d..a2d7b2608e 100644 --- a/src/backend/gpopt/translate/CTranslatorRelcacheToDXL.cpp +++ b/src/backend/gpopt/translate/CTranslatorRelcacheToDXL.cpp @@ -3051,6 +3051,12 @@ CTranslatorRelcacheToDXL::RetrieveStorageTypeForPartitionedTable(Relation rel) gpdb::RelationWrapper child_rel = gpdb::GetRelation(oid); IMDRelation::Erelstoragetype child_storage = RetrieveRelStorageType(child_rel.get()); + // Child rel with partdesc means it's not leaf partition, we don't care about it + if (child_rel->rd_partdesc) + { + continue; + } + if (child_storage == IMDRelation::ErelstorageForeign) { // for partitioned tables with foreign partitions, we want to ignore the foreign partitions --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
