skip to content

A PostgreSQL banking schema indexes tax_rate(region_code), a function declared IMMUTABLE that reads a tax_rates table; what goes wrong when a rate changes, and how do you fix it?

level: seniorimportance: should knowfreq 40%

answer

  1. the label is trusted, never checked
  2. entries computed when the row is written
  3. index scan and sequential scan disagree
  4. 17 locks maintenance search_path
  5. store the applied rate on the row

basics

~20 s

PostgreSQL trusts the IMMUTABLE label, so index entries keep the old rate after tax_rates changes and results differ between index and sequential scans. Store the applied rate in a column, or label the function honestly STABLE and stop indexing it.

solid answer

~40 s

`CREATE INDEX` only checks that every function in the expression is labelled `IMMUTABLE`; it never verifies that `tax_rate()` is really pure. Index entries are computed when a `card_payment` row is inserted or updated, so changing a row in `tax_rates` leaves existing entries holding the old rate. A query whose plan uses the index returns rows matching the stale value, while a sequential scan recomputes the function and disagrees, so results change with the plan. On PostgreSQL 17 and later, `CREATE INDEX`, `REINDEX` and `ANALYZE` also run the function with `search_path` set to `pg_catalog, pg_temp`, so a string-bodied function naming `tax_rates` unqualified fails outright. The fix is to stop pretending: record the rate applied on each payment row and index that column, or declare the lookup `STABLE` and join to `tax_rates`; then rebuild the index.

code

sql · 19 lines
sql
-- The trap: a lookup dressed up as IMMUTABLE (string body, names resolved at run time)
CREATE FUNCTION tax_rate(p_region text)
RETURNS numeric
LANGUAGE sql IMMUTABLE
AS $$ SELECT rate FROM tax_rates WHERE region_code = p_region $$;

CREATE INDEX card_payment_tax_rate_idx ON card_payment (tax_rate(region_code));

UPDATE tax_rates SET rate = 0.21 WHERE region_code = 'NL';

-- Through the index: entries for NL payments still hold the old rate
SELECT count(*) FROM card_payment WHERE tax_rate(region_code) = 0.21;

-- Without the index: the function is recomputed per row, so the count differs
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT count(*) FROM card_payment WHERE tax_rate(region_code) = 0.21;
RESET enable_indexscan;
RESET enable_bitmapscan;

go deeper

for a junior

Recall that functions in index expressions must be labelled IMMUTABLE and that the label is a promise the server does not verify.

for a middle

Explain when index entries are computed, why a later change to another table never reaches them, and how constant folding reuses a value in cached plans.

for a senior

Demonstrate the diagnosis, the index-on versus index-off result mismatch, the 17 maintenance search_path failure, and a repair that changes the model before rebuilding the index.

for a principal

Frame the governance question: which derived values a schema may compute in functions versus persist as data, and how reviews catch lookups dressed up as IMMUTABLE.

## What PostgreSQL checks and what it trusts An **expression index** stores the result of an expression for each row, here `tax_rate(region_code)` on `card_payment`. PostgreSQL insists that every function in an index expression is labelled `IMMUTABLE`, and rejects anything else with `functions in index expression must be marked IMMUTABLE` (SQLSTATE `42P17`). But the label is a **promise**, stored in `pg_proc.provolatile`; nothing reads the body to confirm it. A function whose body is `SELECT rate FROM tax_rates WHERE region_code = $1` can be declared `IMMUTABLE`, and the index builds without complaint. ## How the lie shows up 1. **Stale entries.** An entry is computed when its `card_payment` row is inserted or updated, and at no other time except a rebuild. Updating `tax_rates` does not touch `card_payment`, so every existing entry keeps the old rate. Replacing the function with `CREATE OR REPLACE FUNCTION` does not rebuild dependent indexes either. 2. **Plan-dependent answers.** `WHERE tax_rate(region_code) = 0.21` answered through the index finds rows whose stored entry is `0.21`; a sequential scan calls the function again for each row and finds a different set. The same query returns different results depending on the plan. 3. **Folded constants.** An `IMMUTABLE` call with constant arguments, such as `tax_rate('NL')`, is folded during planning; a cached plan in a prepared statement or a PL/pgSQL function keeps the old value. 4. **Maintenance failures.** Autovacuum has always run with an empty `search_path`, and since PostgreSQL 17, manual `ANALYZE`, `VACUUM`, `CREATE INDEX`, `REINDEX`, `CLUSTER` and `REFRESH MATERIALIZED VIEW` run functions with `search_path` set to `pg_catalog, pg_temp`. A dollar-quoted function body that names `tax_rates` unqualified is parsed at execution and can no longer find the table, so `REINDEX` fails and `ANALYZE` of the whole table errors out. A `RETURN` or `BEGIN ATOMIC` body binds its names at creation and avoids this particular failure, but not the staleness. ## Diagnosing it - Run the suspect query twice, once normally and once with `enable_indexscan` and `enable_bitmapscan` turned off for the session, and compare the results. Different row counts mean the index disagrees with the function. - List the index definitions with `pg_get_indexdef()` or `pg_indexes`, then look up each referenced function's `provolatile` and body in `pg_proc`. An `IMMUTABLE` function whose body mentions a table is the culprit. ## Fixing it | Option | When it fits | What it costs | |---|---|---| | Store `tax_rate_applied` on each payment and index that column | the rate in force at booking is what matters, which is normal for money | one column, written by the application or a trigger at insert | | Label the lookup `STABLE`, drop the expression index, join to `tax_rates` | the current rate is wanted at query time | queries filter through the join and an index on `tax_rates` | | Keep `IMMUTABLE` only for a truly pure function, rates hard-coded in the body | rates change only with a deploy | every change needs `CREATE OR REPLACE` plus a rebuild of every dependent index | In a banking schema the first option is usually the right one: the tax applied to a payment is a fact about that payment, and later rate changes must not rewrite history. Schema-qualifying `tax_rates` or attaching `SET search_path` to the function cures only the maintenance error; it does nothing for the stale entries. After correcting the model, rebuild or drop the existing index so no stale entry survives; how to rebuild without blocking writes is covered under index upkeep. ## Why the rule exists - Index lookups assume the stored value equals what the expression would compute now; immutability is the only thing that makes that true forever. - The server cannot prove purity for arbitrary PL/pgSQL, Python or C code, so it trusts the author. That trust is the hole this question probes. - The same promise is relied on by generated columns, which also demand immutable expressions, and by index predicates of partial indexes. A lookup dressed up as `IMMUTABLE` corrupts each of them in the same silent way. - A related false fix is to mark the function `STABLE` and keep querying `WHERE tax_rate(region_code) = ...` without an index. That is correct but scans every payment; the stored column is what makes the query both correct and indexable.

  • The rate on a payment must be the one in force when it was booked. How does that change the design?
    Then the rate is a fact about the payment, not a function of the region. Copy it into a `tax_rate_applied` column when the row is written, from `tax_rates` as of the booking date. An index on that column stays correct forever, and later rate changes cannot rewrite history.
  • After an upgrade to PostgreSQL 17, REINDEX on this index fails with relation tax_rates does not exist, yet queries still work. Why?
    Ordinary queries resolve the unqualified name through the session's `search_path`, which includes `public`. Since 17, maintenance commands run index functions with `search_path` set to `pg_catalog, pg_temp`, so the string-bodied function cannot find `tax_rates`. Schema-qualify the table, attach `SET search_path` to the function, or use a `RETURN` or `BEGIN ATOMIC` body; none of these cures the staleness.

  • CREATE INDEX would have rejected the function if it really read a table.
  • Updating tax_rates automatically refreshes the index entries that depend on it.
  • CREATE OR REPLACE FUNCTION rebuilds every index that uses the function.
  • Adding SET search_path to the function fixes the stale index entries.
  • STABLE is enough for an expression index as long as the data rarely changes.

Assumes PostgreSQL 18; since 17, manual maintenance commands run index functions with search_path pg_catalog, pg_temp, which turns this hidden dependency into a hard REINDEX failure.