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

Abierto
  • ¿Algo no está bien?
#2,257 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 2 días

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
55/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Activo
Stack tecnológico
c, postgresql, sql
Área
backend, databases, distributed-systems

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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.

Lenguaje dominante
C
Estrellas
1.4k
Forks
268
Merge medio
4 d 3 h
PR fusionados (30 d)
78

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de apache/cloudberry

Todos los issues de apache/cloudberry

Issues similares

Más issues de C

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.