Correlated subquery over a UNION ALL of EXECUTE ON COORDINATOR and EXECUTE ON ALL SEGMENTS functions fails with "illegal rescan of motion node"

Đang mở
  • Có gì không ổn?
#2,257 0 bình luận 0 reaction 0 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 2 ngày

Chưa có ai nhận issue này.

Đánh giá

Độ khó
4/5
Thời gian dự kiến
3-5 ngày
Mức phù hợp với người mới
55/100
Loại issue
Lỗi
Độ rõ ràng
Khá rõ ràng
Mức độ hoạt động
Sôi nổi
Công nghệ
c, postgresql, sql
Lĩnh vực
backend, databases, distributed-systems

Hướng nghiên cứu

Start by tracing the PROEXECLOCATION_ALL_SEGMENTS check in pathnode.c and how function scans under an Append are handled for correlated subqueries. Reproduce with the SQL setup in the issue, using both planner modes. Done when the query is rejected at plan time with the existing error or the segment branch is safely materialized so multiple outer rows work.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Mô tả

Apache Cloudberry version

Reproduced on both main (c781604c5ba) and REL_2_STABLE (11e858bc127), with ORCA and with optimizer = off.

What happened

When an EXECUTE ON ALL SEGMENTS function is used on its own inside a subquery that references the outer query, the planner rejects it with a clear error:

ERROR:  cannot execute EXECUTE ON ALL SEGMENTS function in a subquery with arguments from outer query

But if the same function is combined with an EXECUTE ON COORDINATOR function through UNION ALL (for example, in a view), that check no longer fires. The plan is built anyway, and as soon as the outer query produces more than one row, execution fails with an internal error:

ERROR:  illegal rescan of motion node: invalid plan (nodeMotion.c:1368)
HINT:  Likely caused by bad NL-join, try setting enable_nestloop to off

The hint is misleading: the query has nothing to do with a nested-loop join.

How to reproduce
CREATE FUNCTION f_coord() RETURNS SETOF int AS $$ SELECT 1 $$
  LANGUAGE sql EXECUTE ON COORDINATOR;
CREATE FUNCTION f_segs()  RETURNS SETOF int AS $$ SELECT 1 $$
  LANGUAGE sql EXECUTE ON ALL SEGMENTS;
CREATE VIEW v AS
  SELECT * FROM f_coord() x UNION ALL SELECT * FROM f_segs() x;

CREATE TABLE t (id int) DISTRIBUTED REPLICATED;
INSERT INTO t VALUES (1), (2);

-- 1. Segment function alone: correctly rejected at plan time
SELECT id, (SELECT count(*) FROM f_segs() x WHERE x = t.id) FROM t;
-- ERROR:  cannot execute EXECUTE ON ALL SEGMENTS function in a subquery with arguments from outer query

-- 2. Wrapped in UNION ALL: the check is bypassed and execution fails
SELECT id, (SELECT count(*) FROM v WHERE x = t.id) FROM t;
-- ERROR:  illegal rescan of motion node: invalid plan (nodeMotion.c:1368)

-- 3. With a single outer row no rescan is needed, and it succeeds
SELECT id, (SELECT count(*) FROM v WHERE x = t.id) FROM t WHERE id = 1;

-- 4. The equivalent join works
SELECT t.id, count(v.x) FROM t LEFT JOIN v ON v.x = t.id GROUP BY 1;

The LATERAL form fails the same way as query 2.

Plan for query 2 (optimizer = off):

 Gather Motion 1:1  (slice1; segments: 1)
   ->  Seq Scan on t
         SubPlan 1
           ->  Aggregate
                 ->  Result
                       Filter: (x.x = t.id)
                       ->  Append
                             ->  Function Scan on f_coord x
                             ->  Gather Motion 3:1  (slice2; segments: 3)
                                   ->  Function Scan on f_segs x_1

The correlated SubPlan contains a Gather Motion. Every additional outer row rescans the SubPlan, and a Motion cannot be rescanned.

What you think should happen instead

Either of these:

  • reject the query at plan time with the same cannot execute ... in a subquery with arguments from outer query error that the bare segment function gets; or
  • materialize that branch inside the SubPlan so the query runs correctly.

The built-in gp_stat_activity view has the same "segments UNION ALL coordinator" shape, but its segment side uses gp_dist_random(), and it works correctly in the same kind of correlated subquery. So the semantics themselves are supportable.

Anything else

Our guess, not yet confirmed by reading the code closely: the PROEXECLOCATION_ALL_SEGMENTS check in pathnode.c only applies when the function scan is the direct body of the subquery, and is missed when the function scan sits under an Append.

Combining a coordinator-side function and a segment-side function with UNION ALL is a common way for extensions to expose cluster-wide statistics. With such a view, a routine monitoring query that looks up stats for each row of pg_class hits this error.

Ngôn ngữ chính
C
Star
1.4k
Fork
268
Merge trung bình
4 ngày 3 giờ
Pull request đã merge (30 ngày)
78

Chuẩn bị môi trường

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của apache/cloudberry

Tất cả issue của apache/cloudberry

Issue tương tự

Thêm issue về C

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.