Skip to content
Search

PostgreSQL:Volatility

lastmod 2026-05-12created 2026-05-125 edits

Function Volatility Categories - VOLATILE, STABLE, IMMUTABLE

Three categories

  • VOLATILE - result may differ on every call (default)
  • STABLE - guarantees same arguments → same result within the same transaction
  • IMMUTABLE - guarantees same arguments → same result, always

STABLE

  • Declares that the function does not modify the DB and that its result does not change within the same transaction
  • The planner can trust this and optimize by skipping unnecessary repeated calls
  • Typical examples: NOW(), current_user, lookup functions that only SELECT from tables 
CREATE FUNCTION get_org_name(org_id UUID)
  RETURNS TEXT
  LANGUAGE sql
  STABLE          -- 같은 트랜잭션 내에선 org 이름이 안 바뀜
AS $$
  SELECT name FROM orgs WHERE id = org_id;
$$;

Caveats:

  • STABLE functions cannot be used as the DEFAULT value of an index expression (only IMMUTABLE can)
  • If misclassified, the planner may reuse cached results and return wrong results, so declare it to match the actual behavior.

Key differences

Attribute

Scope in which calls with the same arguments give the same result

DB state access

Examples

IMMUTABLE

Always, forever (completely independent of DB state)

❌ Forbidden (cannot use tables/sequences/now(), etc.)

lower(text), abs(int), md5(text)

STABLE

Same result within a single SQL statement

✅ Read OK, write ❌

now(), current_user, table SELECT

VOLATILE

May differ on every call

✅ Both read/write

random(), nextval(), INSERT/UPDATE side effects

One-line rule of thumb

Condition

Declaration

Never reads the DB and always gives the same answer for the same arguments

IMMUTABLE

Reads the DB but does not write, and gives the same answer within one statement

STABLE

Everything else

VOLATILE

Verification matrix

Declaration

DB read

DB write

FOR UPDATE

random / now

RAISE

IMMUTABLE

❌

❌

❌

❌

✅

STABLE

✅

❌

❌

❌

✅

VOLATILE

*

✅

✅

✅

*

(✅ allowed, ❌ forbidden, * irrelevant)

See also

Favorite site

author Zer0 sitemap