Incident brief
Index expression must be immutable
An expression index may only use IMMUTABLE functions, because a changing result would silently corrupt the index; a non-immutable function in the expression raises this error.
In 10 seconds
- What
- Index expression must be immutable
- What triggers it
- Create an index whose expression calls a STABLE or VOLATILE function (for example a timezone- or now()-dependent expression).
- The fix
- Index only IMMUTABLE expressions (or a plain column).
- Proof
- Reproduced on PostgreSQL 18.4 → A CREATE INDEX reproduces SQLSTATE 42P17; the non-immutable expression is rejected before the index is built.
The fix
What to do right now
The immediate, application-level response to this error.
- Index only IMMUTABLE expressions (or a plain column).
- If a function is genuinely immutable but not marked so, mark it (or a wrapper) IMMUTABLE — only when the output truly never changes for the same input.
-- Index an immutable expression (here, a plain column).
CREATE INDEX idx_ok ON ref_demo (label);Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 18 Documentation — Appendix A. PostgreSQL Error Codes (Table A.1, Class 42 — Syntax Error or Access Rule Violation)
42P17 → invalid_object_definitionRead the full section on postgresql.org →
Indexing a non-immutable expression
Index expressions must be reproducible, so PostgreSQL raises 'functions in index expression must be marked IMMUTABLE' when the expression is not.The session continues normally
The failing statement ran outside a transaction block, so nothing was left in a bad state — the next statement in the same connection runs normally.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Create an index whose expression calls a STABLE or VOLATILE function (for example a timezone- or now()-dependent expression).
- 2PostgreSQL checks that every function in the index expression is IMMUTABLE.
- 3A non-immutable function aborts the CREATE INDEX with SQLSTATE 42P17.
An index on a computed expression failed because the expression depended on a non-immutable function, which would make the index inconsistent over time.
CREATE TABLE ref_demo(label text);
INSERT INTO ref_demo VALUES ('a');CREATE INDEX idx_bad ON ref_demo ((label || clock_timestamp()::text));SELECT 'ok' AS session_after_error;What PostgreSQL actually returned
CREATE TABLE
INSERT 0 1ERROR: functions in index expression must be marked IMMUTABLE session_after_error
---------------------
ok
(1 row)The index builds once its expression is immutable.
We create the index on a plain column (trivially immutable) to show it succeeds.
Without this
Before: a non-immutable expression aborts
With this, tested
After: a plain-column index builds
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-24 (isolated lab, PostgreSQL 18.4)
- Reviewed by
- Verified against PostgreSQL 18.4 in an isolated lab environment
- Audit status
- reviewed