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

オープン
  • 何か問題がありますか？
#2,257 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 2 日以内に返信

まだ誰も着手していません。

評価

難易度
4/5
見積もり時間
3〜5日
初心者へのやさしさ
55/100
issue の種類
バグ
明瞭さ
おおむね明確
活発さ
活発
技術スタック
c, postgresql, sql
領域
backend, databases, distributed-systems

調査の方向性

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.

索引モデルが issue の本文から書いたものです。

説明

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.

主要言語
C
スター
1.4k
フォーク
268
平均マージ
4日 3時間
マージ済み PR（30日）
78

環境構築

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

apache/cloudberry のほかの issue

apache/cloudberry の issue をすべて見る

似ている issue

C の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。