cockroachdb / cockroachdb/cockroach

sql/plpgsql: support dynamic EXECUTE inside control-flow blocks

Open
#172,491 0 comments 0 reactions 0 assignees View on GitHub
A-sql-plpgsql A-sql-routine C-enhancement O-agent T-sql-foundations
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.