You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/01/08 03:33:13 UTC
(cloudberry) 08/16: Prevent function execution which access distributed table on entrydb query executor.
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 c56911bd19bb3a1e7ee9f7a8b88cefc8f3464d1d
Author: m7onov <m7...@gmail.com>
AuthorDate: Mon Mar 13 09:21:43 2023 +0300
Prevent function execution which access distributed table on entrydb query executor.
I came across a bug related to function execution in entrydb QE. Details provided below.
``` sql
create table invalid_name (name text) distributed replicated;
create table candidate (name text, parent_name text) distributed by (name);
create or replace function is_invalid_name (aname text)
returns boolean
language sql
as $$
select aname in (select name from invalid_name)
$$;
create table tst_valid_name (a text) distributed randomly;
create table tst_invalid_name (a text) distributed randomly;
insert into invalid_name (name) values ('tst_invalid_name');
insert into candidate(name, parent_name) values ('tst_valid_name', 'tst_invalid_name');
```
Table "candidate" contains 1 row.
``` sql
select * from candidate;
name | parent_name
----------------+------------------
tst_valid_name | tst_invalid_name
(1 row)
```
Subquery returns 1 row.
``` sql
select c.relname as name,
'tst_invalid_name' as parent_name
from pg_class c
where c.relname like 'tst%' and
not is_invalid_name(c.relname);
name | parent_name
----------------+------------------
tst_valid_name | tst_invalid_name
(1 row)
```
But when we full join we get 2 rows.
``` sql
select a.name as a_name,
a.parent_name as a_parent_name,
b.name as b_name,
b.parent_name as b_parent_name
from candidate a
full join (select c.relname as name,
'tst_invalid_name' as parent_name
from pg_class c
where c.relname like 'tst%' and
not is_invalid_name(c.relname)) b
on a.name = b.name and
a.parent_name = b.parent_name
order by 1,2,3,4;
a_name | a_parent_name | b_name | b_parent_name
----------------+------------------+------------------+------------------
tst_valid_name | tst_invalid_name | tst_valid_name | tst_invalid_name
| | tst_invalid_name | tst_invalid_name
(2 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice2; segments: 3) (cost=102.58..102.59 rows=4 width=128) (actual time=1.697..1.698 rows=2 loops=1)
Merge Key: a.name, a.parent_name, c.relname, ('tst_invalid_name'::text)
-> Sort (cost=102.58..102.59 rows=2 width=128) (actual time=0.958..0.959 rows=1 loops=1)
Sort Key: a.name, a.parent_name, c.relname, ('tst_invalid_name'::text)
Sort Method: quicksort Memory: 99kB
-> Hash Full Join (cost=1.29..102.55 rows=2 width=128) (actual time=0.370..0.937 rows=1 loops=1)
Hash Cond: ((c.relname)::text = a.name)
Join Filter: (a.parent_name = 'tst_invalid_name'::text)
Extra Text: (seg0) Hash chain length 0.0 avg, 0 max, using 0 of 131072 buckets.
-> Redistribute Motion 1:3 (slice1) (cost=0.27..101.49 rows=1 width=96) (actual time=0.262..0.262 rows=1 loops=1)
Hash Key: (c.relname)::text
-> Index Only Scan using pg_class_relname_nsp_index on pg_class c (cost=0.27..101.47 rows=1 width=96) (actual time=0.214..0.251 rows=2 loops=1)
Index Cond: ((relname >= 'tst'::name) AND (relname < 'tsu'::name))
Filter: ((relname ~~ 'tst%'::text) AND (NOT is_invalid_name((relname)::text)))
Heap Fetches: 0
-> Hash (cost=1.01..1.01 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1)
-> Seq Scan on candidate a (cost=0.00..1.01 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)
Planning time: 3.277 ms
(slice0) Executor memory: 224K bytes.
(slice1) Executor memory: 260K bytes (entry db).
(slice2) Executor memory: 1141K bytes avg x 3 workers, 1144K bytes max (seg0). Work_mem: 33K bytes max.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution time: 2.866 ms
(24 rows)
```
The problem is that `is_invalid_name` function runs in slice1 in entry DB QE which surely does not contain data for `invalid_name` table. This PR adds additional condition to `src/backend/executor/functions.c`, `querytree_safe_for_qe_walker()` which prevents functions execution on entrydb QE. Also PR contains regression test for this bug.
---
src/backend/executor/functions.c | 3 ++-
src/test/regress/expected/qp_functions.out | 22 ++++++++++++++++++++++
src/test/regress/sql/qp_functions.sql | 25 +++++++++++++++++++++++++
3 files changed, 49 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 0b2848e528..9cc613c5b6 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -222,7 +222,8 @@ querytree_safe_for_qe_walker(Node *expr, void *context)
if (!(IsCatalogNamespace(namespaceId) ||
IsToastNamespace(namespaceId) ||
IsAoSegmentNamespace(namespaceId) ||
- IsReplicatedTable(rte->relid)))
+ (IsReplicatedTable(rte->relid) &&
+ !IS_QUERY_DISPATCHER())))
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
diff --git a/src/test/regress/expected/qp_functions.out b/src/test/regress/expected/qp_functions.out
index 9e84f4b630..bc50bd4265 100644
--- a/src/test/regress/expected/qp_functions.out
+++ b/src/test/regress/expected/qp_functions.out
@@ -1132,3 +1132,25 @@ $$;
NOTICE: n: 9.12
NOTICE: t: 9.1
NOTICE: n: 9.12
+create table invalid_name (name text) distributed replicated;
+create table candidate (name text, parent_name text) distributed by (name);
+create or replace function is_invalid_name (aname text) returns boolean language sql as $$ select aname in (select name from invalid_name)$$;
+create table tst_valid_name (a text) distributed randomly;
+create table tst_invalid_name (a text) distributed randomly;
+insert into invalid_name (name) values ('tst_invalid_name');
+insert into candidate(name, parent_name) values ('tst_valid_name', 'tst_invalid_name');
+select a.name as a_name,
+ a.parent_name as a_parent_name,
+ b.name as b_name,
+ b.parent_name as b_parent_name
+ from candidate a
+ full join (select c.relname as name,
+ 'tst_invalid_name' as parent_name
+ from pg_class c
+ where c.relname like 'tst%' and
+ not is_invalid_name(c.relname)) b
+ on a.name = b.name and
+ a.parent_name = b.parent_name
+ order by 1,2,3,4;
+ERROR: function cannot execute on a QE slice because it accesses relation "public.invalid_name"
+CONTEXT: SQL function "is_invalid_name" during startup
diff --git a/src/test/regress/sql/qp_functions.sql b/src/test/regress/sql/qp_functions.sql
index 8b9f65510c..d9eb2bf8fe 100644
--- a/src/test/regress/sql/qp_functions.sql
+++ b/src/test/regress/sql/qp_functions.sql
@@ -510,3 +510,28 @@ begin
raise notice 'n: %', n;
end;
$$;
+
+
+create table invalid_name (name text) distributed replicated;
+create table candidate (name text, parent_name text) distributed by (name);
+create or replace function is_invalid_name (aname text) returns boolean language sql as $$ select aname in (select name from invalid_name)$$;
+
+create table tst_valid_name (a text) distributed randomly;
+create table tst_invalid_name (a text) distributed randomly;
+
+insert into invalid_name (name) values ('tst_invalid_name');
+insert into candidate(name, parent_name) values ('tst_valid_name', 'tst_invalid_name');
+
+select a.name as a_name,
+ a.parent_name as a_parent_name,
+ b.name as b_name,
+ b.parent_name as b_parent_name
+ from candidate a
+ full join (select c.relname as name,
+ 'tst_invalid_name' as parent_name
+ from pg_class c
+ where c.relname like 'tst%' and
+ not is_invalid_name(c.relname)) b
+ on a.name = b.name and
+ a.parent_name = b.parent_name
+ order by 1,2,3,4;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org