Plpgsql Internals

plpgsql-internals — the SQL procedural language

PL/pgSQL is PG's most-used procedural language: functions, procedures, DO blocks, triggers. Under the hood it's a parser + compiler + interpreter living in a self-contained subdirectory. The interpreter (pl_exec.c) is 268 KB — the biggest source file in PG core after postmaster.c.

The file map

The 3-stage flow: parse → compile → execute

1. Parse

• SQL parser hands off the function body when LANGUAGE plpgsql is detected.
• pl_gram.y parses the body producing PLpgSQL_stmt AST nodes.
• Grammar interleaves with SQL — nested EXECUTE 'SQL text' re-invokes SQL parser via SPI.

2. Compile

• plpgsql_compile (in pl_comp.c) — turns AST + function catalog metadata into PLpgSQL_function:
  • datums[] array — one entry per variable, cursor, or reference.
  • action — the top-level PLpgSQL_stmt_block.
  • fn_oid, fn_hashkey, resowner_for_use_stack — bookkeeping.
• PLpgSQL_function is CACHED in a hash keyed by (fn_oid, argument types) — so a function compiled once serves many calls.
• Cache invalidation via plpgsql_HashTableDelete — fires on pg_proc invalidation. Historical bug source: some catalog changes don't invalidate this cache correctly (see 2026-06-04 session).

3. Execute

• plpgsql_exec_function (in pl_exec.c) — walks the PLpgSQL_stmt tree:
  • For each statement, dispatches to exec_stmt_<kind> (there are ~30 kinds).
  • Expressions (assignments, conditions) go through SPI — SPI_execute_snapshot — with a per-expression plan cache (PLpgSQL_expr.plan).
  • EXCEPTION blocks use PG_TRY/CATCH at the C level; every EXCEPTION handler enters a subtransaction.

The trusted-language boundary

PL/pgSQL registers TWO handlers:

• plpgsql_call_handler — untrusted. Used when the function is declared LANGUAGE plpgsql with owner=superuser.
• plpgsql_call_handler also serves the trusted variant when checked. Trusted PL/pgSQL is (from a security-review standpoint) SAME as untrusted — the "trust" claim is that PL/pgSQL doesn't have COPY FROM PROGRAM / EXECUTE FILE ... primitives. Every SQL a PL/pgSQL function runs still has the SQL-level GRANT/REVOKE + RLS gates.

Real security issues:

• EXECUTE format('...') is injection-prone — no sanitizing. Use quote_ident, quote_literal, format('%L', ...).
• WHEN OTHERS swallows almost everything, including query_canceled. A misbehaving procedure can be hard to kill.
• COMMIT in a procedure ends the current snapshot — a procedure that reads x, COMMITs, then reads x may see different data. Snapshot invariant intentionally broken.

See 2026-06-04-a9-plpgsql.md session log for the specific findings.

Plan cache in PL/pgSQL

Each EXECUTE / SELECT ... INTO / expression in a function gets its own PLpgSQL_expr struct with a cached SPIPlanPtr. On first call → prepare + save; subsequent calls reuse.

Cache is invalidated when:

• Underlying object (table, function) changes catalogversion.
• Search path or role changes (for generic plans).
• Explicit DEALLOCATE ALL.

But NOT invalidated when:

• A GUC that affects planning changes (mostly).
• A comment on the referenced object changes (correct — no plan impact).

Common patch shapes

Add a new PL/pgSQL statement kind

Scenario: integrate-with-plpgsql. Sequence:

• Extend grammar in pl_gram.y — parser action produces the new AST node.
• Extend PLpgSQL_stmt_type enum in plpgsql.h + AST struct.
• Add exec_stmt_<kind> in pl_exec.c.
• Add support in pl_funcs.c for dumping / freeing the new node.
• Regression tests in src/pl/plpgsql/src/expected/plpgsql_*.out.

Add a new EXCEPTION reason code

• Extend errcodes.txt (in src/backend/utils/errcodes.txt).
• Regenerate plerrcodes.h via generate-plerrcodes.pl.
• Documentation.

Debug "PL/pgSQL is 10x slower than raw SQL"

• Check if the plan-cache is warm — first call always compiles + prepares plans.
• Check if EXCEPTION blocks are hot — each entry creates a subtransaction (expensive).
• Use plpgsql.extra_warnings = 'strict_multi_assignment' etc. to catch common footguns.
• EXPLAIN (ANALYZE, BUFFERS) inside the function via RAISE INFO '%', ....

Extend GET DIAGNOSTICS

• New GD_* enum in pl_exec.c.
• Handler in exec_stmt_getdiag.
• Grammar rule in pl_gram.y for the syntax.

Pitfalls

• FOR rec IN SELECT ... is a Portal, not a materialized list — the loop iterates lazily. Modifying the underlying table during the loop may or may not affect what the loop sees (depends on isolation level).
• RETURNS TABLE vs RETURNS SETOF composite — both work; the compiler generates different output-slot handling. Common source of "wrong-column-order" bugs when mixing them.
• RAISE ... without SQLSTATE — defaults to P0001 (raise_exception). Explicit SQLSTATE (RAISE USING ERRCODE = ...) needed for finer-grained handlers.
• EXCEPTION handlers can't COMMIT — subtransactions inside them get rolled back on exit; trying to commit will error.
• GET DIAGNOSTICS row_count = ... applies to the LAST executed statement, not the surrounding block.
• PERFORM x vs SELECT x — PERFORM discards the result; SELECT into a variable requires the INTO clause. SELECT foo() without INTO in PL/pgSQL is a compile error.
• FOUND variable is not always what you expect — set by SELECT (was there a row?), UPDATE / DELETE / INSERT / MERGE (any rows affected?), EXECUTE (last row's presence). It's transactional per-block.
• COMMIT-in-procedure loses the snapshot — the transactional context inside the procedure changes; assumptions about "same snapshot throughout" are wrong.
• RETURN NEXT vs RETURN QUERY — the former accumulates in a tuplestore per-call; the latter iterates. RETURN QUERY of a large result set is much better than looping with RETURN NEXT.
• Trusted PL/pgSQL isn't really sandboxed — every SQL statement respects the calling user's SQL-level privileges. If you thought "trusted" meant "isolated", you're wrong.

Related corpus

• Idiom: fmgr (the entry point via fmgr's PG_FUNCTION_INFO_V1 machinery), syscache-invalidation-flow (relates to plpgsql cache invalidation).
• Subsystems: parser-and-rewrite (SQL parser recursively invoked via EXECUTE), executor (SPI dispatch executes SQL statements plpgsql produces), catalog-conventions (pg_proc / pg_language rows for LANGUAGE plpgsql).
• Scenario: integrate-with-plpgsql (adding a new PL/pgSQL statement/feature).
• Sessions: 2026-06-04-a9-plpgsql.md (deep-read of the whole pl/plpgsql/ directory — security-boundary summary, 87 issues surfaced).
• File docs: knowledge/files/src/pl/plpgsql/src/pl_*.md — one doc per file.

Corpus-chain shortcut

python3 scripts/corpus-chain.py --scenario integrate-with-plpgsql
python3 scripts/corpus-chain.py --file src/pl/plpgsql/src/pl_exec.c

Second surfaces the 268 KB interpreter's neighborhood.

Boundary

Use this skill for src/pl/plpgsql/src/ — the PL/pgSQL implementation.

Don't use for:

• Other PLs — plpython / plperl / pltcl live in sibling subdirs. Each has its own boundary + trust model. Notable: PL/Perl uses opcode-mask (not Safe.pm); PL/Python is untrusted-only by design; PL/Tcl uses Tcl safe interp (structurally strongest). See sessions 2026-06-04-a10-*.md for cross-PL comparison.
• SPI — the Server Programming Interface that PL/pgSQL and every other PL uses to run SQL. See fmgr-and-spi skill.
• CREATE FUNCTION for LANGUAGE C — that's fmgr territory, not PL/pgSQL.
• CREATE FUNCTION for LANGUAGE sql — different implementation (function inlining), lives in src/backend/optimizer/util/.
• DO blocks in languages OTHER than plpgsql — same DO statement, per-language dispatch. This skill covers the plpgsql case only.