cockroachdb / cockroachdb/cockroach
sql/plpgsql: support dynamic EXECUTE inside control-flow blocks
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Is your feature request related to a problem? Please describe.**
Dynamic `EXECUTE` (#172401) is currently supported only as a top-level
statement in a routine body (and, as it turns out, in the guarded body or
handler action of an exception block). It is rejected inside control-flow
constructs — `IF`, `WHILE`, `FOR`/loop, and nested `BEGIN ... END` blocks —
with:
```
unimplemented: PL/pgSQL EXECUTE is not yet supported inside a loop, IF, or nested block
```
Repro:
```sql
CREATE PROCEDURE p(b BOOL) LANGUAGE PLpgSQL AS $$
BEGIN
IF b THEN
EXECUTE 'INSERT INTO t VALUES (1)';
END IF;
END
$$;
-- ERROR: unimplemented: PL/pgSQL EXECUTE is not yet supported inside a loop, IF, or nested block
```
Conditional and looped dynamic DML/DDL is a common shape, so this
restriction is a real gap rather than an edge case.
The cause is the same cross-memo constraint behind #171610: the
continuation created at an `EXECUTE` seam defers its rest-of-body tail to a
fresh execution-time memo, but an `EXECUTE` inside control flow would need
that tail to transfer control back to an enclosing loop/IF/block
continuation whose definition lives in the plan-time memo. The `EXECUTE`
handler guards against this by rejecting whenever there is an enclosing
continuation on the stack (`len(b.continuations) != 0`).
**Describe the solution you'd like**
Allow dynamic `EXECUTE` anywhere in a routine body, including inside
`IF`/`WHILE`/`FOR`/nested blocks, by building on the deferred-continuation
machinery from cockroachlabs/cockroach#1369 (which fixes the analogous
restriction for static DDL-then-use):
- Route `EXECUTE`-containing bodies onto the deferred whole-body build
path, alongside DDL-containing bodies. Today that path is taken only when
the body contains static DDL (`deferBody := dv.foundDDLStmt != nil` in
`routine.go`); extend it to
`dv.foundDDLStmt != nil || dv.foundDynamicExecute`. `foundDynamicExecute`
is already tracked by `ddlVisitor`. On this path every continuation is
lazily planned in one execution-time memo, so an `EXECUTE`'s deferred tail
and its enclosing loop/IF/block continuations coexist in the same memo.
- Reuse cockroachlabs/cockroach#1369's continuation-stack capture/restore so
the `EXECUTE` tail resolves enclosing loop and block continuations via
`getContinuation`/`callContinuation` exactly as the eager build does.
- Relax the `EXECUTE` handler's `len(b.continuations) != 0` rejection on the
deferred path (keep the version gate).
**Known limitation.** Matching cockroachlabs/cockroach#1369, exception
handler *actions* remain eager — they are invoked through the block's
`ExceptionBlock` at runtime, not the deferred routine plan generator, so
their bodies cannot be rebuilt in a fresh memo. An `EXECUTE` that needs to
reference an *enclosing* continuation from inside a handler action would
therefore stay unsupported. A top-level `EXECUTE` in a handler action
already works, and DDL-then-use via `EXECUTE` already works in both the
guarded body and the handler action.
**Describe alternatives you've considered**
Re-implementing the lazy-continuation and continuation-stack capture
independently for the `EXECUTE` path (rather than reusing
cockroachlabs/cockroach#1369) was rejected as redundant — it would
duplicate the mechanism that PR already builds for the deferred body path.
**Additional context**
Depends on cockroachlabs/cockroach#1369 (interleave DDL-then-use inside
nested PL/pgSQL control flow) landing; this reuses its machinery. Part of
the dynamic-SQL EXECUTE effort tracked in #169581, building on basic
`EXECUTE` support (#172401).
Epic CRDB-48117
Jira issue: CRDB-65730
Contributor guide
Research direction
Start in routine.go at the deferred-body decision and follow ddlVisitor's foundDynamicExecute flag, then read the continuation-stack capture and restore from cockroachlabs/cockroach#1369. Reproduce the IF example and verify dynamic EXECUTE works inside IF, WHILE, FOR, and nested BEGIN...END blocks while handler-action limitations remain documented.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 35/100