skip to content

In PostgreSQL, what do the IMMUTABLE, STABLE and VOLATILE labels on CREATE FUNCTION let the planner do, and which is the default?

level: middleimportance: must knowfreq 58%

answer

  1. a promise, not a check
  2. plan-time constant folding
  3. index scan comparison value
  4. snapshot of the calling query
  5. provolatile i, s, v

basics

~20 s

VOLATILE, the default, allows no optimisation and re-evaluates per row; STABLE lets PostgreSQL evaluate once per statement, so it can drive an index scan; IMMUTABLE allows plan-time folding and is required in index expressions. PostgreSQL trusts the label without verifying it.

solid answer

~50 s

Every PostgreSQL function carries one volatility category, stored in `pg_proc.provolatile`, and `VOLATILE` is the default when `CREATE FUNCTION` names none. A `VOLATILE` function is re-evaluated wherever its value is needed and cannot be the comparison value of an index scan. A `STABLE` function promises the same result for the same arguments within one statement, so the planner may evaluate it once and use it in an index condition, as with `now()`. An `IMMUTABLE` function promises the same result forever, so calls with constant arguments are folded to a constant at plan time, and only `IMMUTABLE` functions are accepted in index expressions and generated columns. The label also sets visibility: `STABLE` and `IMMUTABLE` bodies use the calling query's snapshot and may run only `SELECT`. PostgreSQL does not verify the claim, so a wrong label gives wrong results, not an error.

code

sql · 19 lines
sql
-- Pure arithmetic: safe to fold and to index
CREATE FUNCTION round_to_cents(amount numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE PARALLEL SAFE
RETURN round(amount, 2);

-- Depends on the clock: same answer within a statement only
CREATE FUNCTION current_business_day()
RETURNS date
LANGUAGE sql STABLE
RETURN (now() AT TIME ZONE 'UTC')::date;

-- STABLE lets this comparison drive an index scan on booked_on
SELECT id, amount FROM ledger_entry WHERE booked_on = current_business_day();

-- Audit labels: i = immutable, s = stable, v = volatile
SELECT proname, provolatile
  FROM pg_proc
 WHERE proname IN ('round_to_cents', 'current_business_day', 'now', 'random');

go deeper

for a junior

Recall the three labels, that VOLATILE is the default, and that index expressions demand IMMUTABLE functions.

for a middle

Explain what each label permits: constant folding, a once-per-statement index scan value, the caller's snapshot, and the SELECT-only rule for non-volatile bodies.

for a senior

Show the production consequence of a wrong label, stale values baked into cached plans or index entries, and why the weaker honest label is the safe default.

for a principal

Discuss how a team audits volatility labels across many functions, for example a review step that compares pg_proc.provolatile with what each body actually reads.

## A promise to the optimizer The **volatility category** is the function author's promise about how a function's result can change. PostgreSQL stores it in `pg_proc.provolatile` as `i` (immutable), `s` (stable) or `v` (volatile) and uses it in the planner, the executor and several DDL checks. It **does not inspect the body** to confirm the promise: a function that reads a table can be declared `IMMUTABLE` and the server accepts it. | Label | Promise | What PostgreSQL may do | |---|---|---| | `VOLATILE` (default) | the result can change at any call; side effects allowed | nothing: re-evaluate at every row where the value is needed | | `STABLE` | same arguments, same result within one statement; no writes | evaluate once per statement; use the value in an index scan condition | | `IMMUTABLE` | same arguments, same result forever; no writes | fold calls with constant arguments at plan time; use in index expressions | ## What each label unlocks 1. **Index scan conditions.** An index scan computes its comparison value once, not per row. So `WHERE booked_on = current_business_day()` can use an index on `booked_on` only if the function is `STABLE` or `IMMUTABLE`; with a `VOLATILE` function the condition becomes a per-row filter. 2. **Constant folding.** `IMMUTABLE` calls with constant arguments are replaced by their value while planning, the same way `2 + 2` becomes `4`. 3. **DDL acceptance.** `CREATE INDEX` rejects a non-immutable function in an index expression with `functions in index expression must be marked IMMUTABLE` (SQLSTATE `42P17`), and a generated column rejects one with `generation expression is not immutable`. 4. **Inlining.** A `LANGUAGE sql` function is inlined only when its body is no more volatile than its label, so a wrong label can also block inlining. ## Snapshots and what the body may run - `STABLE` and `IMMUTABLE` functions run every query in their body with the **snapshot of the calling query**. They see a fixed view of the database for the whole statement and do not see rows the calling statement has just changed. - `VOLATILE` functions take a fresh snapshot for each query in the body, so they do see changes made earlier by the calling command. - A `STABLE` or `IMMUTABLE` body may contain only `SELECT`. An `INSERT`, `UPDATE` or `DELETE` there fails **when it executes** with `... is not allowed in a non-volatile function`; `CREATE FUNCTION` itself accepts the definition. Calling a volatile function from inside a stable one evades that check, and the volatile function's writes then stay invisible to the stable function's snapshot. ## Choosing the label - Anything with side effects, such as writing a table, `nextval()` or `setval()`, must be `VOLATILE`, otherwise calls can be optimised away. - A result that depends on table contents or on a setting such as `TimeZone` belongs in `STABLE`. Formatting a `timestamptz` with `to_char()` depends on the session time zone, and `to_char()` itself is labelled `STABLE`; wrapping it in an `IMMUTABLE` function is the classic mistake. - `IMMUTABLE` is for pure computations on the arguments, such as rounding an amount to cents or stripping spaces from an account number. - `now()` and the `current_timestamp` family are `STABLE`: they return the transaction start time, so their value does not change within a transaction. ## Why STABLE versus IMMUTABLE matters for cached plans For an ad hoc query that is planned and run at once, folding a function at plan time or calling it once at execution start gives the same answer. The difference appears when a plan is **reused**. A prepared statement or a PL/pgSQL function can keep its plan, and an `IMMUTABLE` call folded into that plan keeps its old value for every later execution. A mislabelled function therefore produces stale results that come and go with plan caching, which makes them hard to reproduce. When in doubt, choose the weaker, honest label; the cost of `STABLE` over `IMMUTABLE` is usually small. ## Inspecting labels The labels of existing functions are visible in `pg_proc`, which is a quick audit when reviewing a schema: look for `IMMUTABLE` functions whose bodies mention a table, `now()`, `current_setting()` or a time-zone-dependent conversion. Before relabelling an `IMMUTABLE` function, find what relies on the promise: an index or stored generated column built on it holds values computed under the old behaviour, and `ALTER FUNCTION ... STABLE` does not rebuild or drop them for you. Rebuild or redesign those objects in the same change.

  • Why is a wrapper around to_char() on a timestamptz a poor candidate for IMMUTABLE?
    `to_char()` renders a `timestamptz` in the session's `TimeZone`, so the same argument yields different text in different sessions, and its own `pg_proc` label is `STABLE` for that reason. Labelling the wrapper `IMMUTABLE` lets PostgreSQL fold it into cached plans, or accept it in an index whose entries were computed under one time zone, and reuse them under another. Label the wrapper `STABLE`.
  • A STABLE function appears in a SELECT list over a million rows. Does PostgreSQL guarantee it runs only once?
    No. `STABLE` permits the planner to evaluate the function once where that helps, as for an index scan comparison value, but it is not a memoisation guarantee; in a target list the function may still run per row. The label grants permission to optimise, it does not promise caching.

  • PostgreSQL inspects the function body and rejects a wrong IMMUTABLE label.
  • A function with no volatility clause defaults to IMMUTABLE.
  • now() is VOLATILE because the clock keeps moving during a transaction.
  • A STABLE function sees the rows its calling UPDATE has just modified.
  • A function used in an index expression only needs to be STABLE.