skip to content

In PostgreSQL, when does PL/pgSQL parse and prepare the SQL inside a function, and what production surprises does that statement caching cause?

level: seniorimportance: nice to knowfreq 30%

answer

  1. lazy, per session
  2. first execution of each statement
  3. syntax checked, names not
  4. a frozen 'now'
  5. DDL and search_path re-parse

basics

~20 s

PL/pgSQL compiles a function body on its first call in each session and prepares each SQL statement the first time it executes, then reuses it. So errors in unexecuted branches surface late, names cannot be variables, and literals like 'now' freeze.

solid answer

~40 s

On the first call in a session, PL/pgSQL turns the body into an instruction tree, but each SQL statement is parsed, analysed and prepared through SPI only the first time it actually runs; later visits reuse it, and SPI may cache a plan. The consequences I watch for: a typo in a rarely taken branch fails only when that branch runs, since `check_function_bodies` checks syntax, not table and column names; tables and columns can't come from variables, so varying ones need `EXECUTE`, which re-plans every time; and a literal like `'now'` is converted once and reused for the session. DDL or new statistics on a referenced table, a `search_path` change, or `CREATE OR REPLACE FUNCTION` force re-analysis or recompilation. How the plan itself is chosen belongs to the planner settings.

code

sql · 14 lines
sql
CREATE FUNCTION log_redeem_bad(p_member bigint) RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
  -- 'now' becomes a constant when this INSERT is first analysed
  INSERT INTO points_log (member_id, logged_at) VALUES (p_member, 'now');
END;
$$;

CREATE FUNCTION log_redeem(p_member bigint) RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO points_log (member_id, logged_at) VALUES (p_member, now());
END;
$$;

go deeper

for a junior

Recall that PL/pgSQL checks syntax at creation but runs into missing tables or columns only when a statement first executes. Know that table names cannot be variables.

for a middle

Explain the two stages, compile on first call and prepare on first execution, per session. Cover why EXECUTE re-plans each time and how the 'now' literal gets frozen.

for a senior

Show the operational view: late failures in rare branches, warm-up cost on fresh connections, what invalidates cached statements, the search_path gap, and coordinating DDL with function deploys.

for a principal

Weigh where caching helps and hurts across a codebase of server functions: testing every branch, connection lifetime, and when dynamic SQL's flexibility is worth its planning cost.

## Two stages: compile on first call, prepare on first execution PL/pgSQL does its work lazily and **per session**, that is, per backend process: 1. On the **first call** of a function in a session, the interpreter parses the body into an internal instruction tree. Only the PL/pgSQL structure is translated; the SQL inside is not analysed yet. 2. The **first time each SQL statement or expression executes**, PL/pgSQL parses and analyses it into a prepared statement through SPI (`SPI_prepare`) and reuses that prepared statement on later visits. A branch that never runs is never analysed in that session. 3. SPI may also **cache an execution plan** for the statement, sometimes a generic one reused across variable values. How PostgreSQL chooses between custom and generic plans, and the `plan_cache_mode` setting, belong to the planner settings. At `CREATE FUNCTION` time, with `check_function_bodies` at its default `on`, PostgreSQL checks the body's PL/pgSQL syntax. A misspelled table or column inside an SQL statement is not detected until that statement first runs. ## The surprises - **Late errors in rare branches.** The branch that expires stale loyalty points might run once a month; a typo in its `UPDATE` surfaces then, in production. Exercise every branch in tests. - **Identifiers are fixed.** Variables are substituted as query parameters, so a statement must reference the same tables and columns on every execution. A varying table name needs `EXECUTE`, which is analysed and planned on every run. - **Constants frozen at analysis time.** In `INSERT INTO points_log VALUES (p_member, 'now')` into a timestamp column, the parser converts `'now'` to a timestamp constant when the statement is first analysed, and the analysed statement is reused for the rest of the session. Use `now()` or `current_timestamp`. - **Record field types must stay stable.** Each expression on a `record` variable's fields is analysed with the types seen the first time; a later call that puts differently typed fields there breaks the cached expression. `EXECUTE` sidesteps it. - **Per-session warm-up.** Every new backend repeats the compile and prepare steps, so the first call on a fresh connection is slower, and short-lived connections pay it again and again. - **Ambiguous names.** A variable named like a column produces "column reference ... is ambiguous", because `plpgsql.variable_conflict` defaults to `error`. Prefix variables (`v_`, `p_`) or qualify them with the block label or the function name. ## What invalidates the cache | Event | Effect | |---|---| | `CREATE OR REPLACE FUNCTION` | each session recompiles the function on its next call | | DDL on a referenced object, such as `ALTER TABLE`, or new planner statistics for it | the prepared statement is re-analysed and re-planned before its next use | | `search_path` differs from the previous execution | the statement is re-parsed with the new path | | a new table with the same name created earlier in `search_path` | **no** automatic re-parse, because nothing the statement used has changed | | a statement run through `EXECUTE` | nothing to invalidate: it is planned every time | ## Separate caches for triggers and polymorphic functions The same trigger function attached to several tables keeps one statement cache per **trigger function and table** pair, so a column with the same name but a different type in two tables works. Functions with polymorphic arguments likewise keep a cache per combination of actual argument types, so type differences between calls do not cause failures. ## How to reason about it in production 1. Treat the first call in each session as compile time; longer-lived connections or a warm-up call hide it. 2. Use static SQL wherever the statement's shape is fixed, to keep plan reuse, and `EXECUTE` only where tables or columns really vary. 3. Ship a column rename and the functions that use it together: after the DDL, each session re-analyses the old statement on its next run and fails on the missing column. 4. When a function is slow only for some argument values, suspect plan choice and look at the plan-caching settings before rewriting the PL/pgSQL.

  • Why does `curtime := 'now';` on a timestamp variable behave differently from the same literal inside an INSERT?
    In the assignment the parser cannot tell what type `'now'` should become, so it yields text, and PL/pgSQL casts that text to timestamp on each execution, giving the current time. Inside an `INSERT` into a timestamp column, the parser converts `'now'` to a timestamp constant during analysis, and that analysed statement is reused for the session. `now()` is right in both places.
  • A column is renamed while sessions are running a function that uses it; what happens?
    The DDL invalidates the prepared statements that reference the table, so each session re-analyses the statement before its next execution. Because the function body still names the old column, that execution fails with an undefined-column error until the function is replaced. Deploy the function change together with the rename.

  • CREATE FUNCTION validates every table and column a PL/pgSQL body references.
  • PL/pgSQL plans each statement once at CREATE FUNCTION time for all sessions.
  • Cached PL/pgSQL statements are shared between all backend processes.
  • A function variable can supply the table name of a static SQL statement.
  • EXECUTE reuses its plan across calls just as static SQL does.