PostgreSQL Tutorial: Understand PL/pgSQL Function Volatility

December 25, 2024

Summary: in this tutorial, you will understand PL/pgSQL function volatility in PostgreSQL.

Table of Contents

Introduction

The PL/pgSQL language, available as a default extension in PostgreSQL, provides powerful tools and flexibility for application developers to build complex, enterprise-scale functionality within the database. Through PL/pgSQL’s functions and procedures, developers can choose different volatility categories—IMMUTABLE, STABLE, or VOLATILE—that offer varying performance benefits, especially in terms of result caching and data state awareness. For a deeper dive, refer to the official documentation. Choosing the appropriate volatility for a function is crucial.

Example

During one of my migration projects, we ran into unexpected behavior while debugging code converted from Oracle. Below is a recreated example using mock functions to illustrate the issue:

TRUNCATE TABLE user_status;
CREATE TABLE user_status(
  col1 bigint GENERATED BY DEFAULT AS IDENTITY,
  status text);

CREATE OR REPLACE FUNCTION upd_status(text)
RETURNS bigint LANGUAGE plpgsql
AS $$
DECLARE var1 bigint;
BEGIN
  WITH alias1 AS
    (INSERT INTO user_status(status) VALUES($1) RETURNING col1)
      SELECT * INTO var1 FROM alias1;
  RETURN var1;
END;$$;

CREATE OR REPLACE FUNCTION lookup_status(text)
RETURNS boolean LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE
AS $$
BEGIN
  RETURN (SELECT EXISTS (SELECT 1 FROM user_status WHERE status = $1));
END;$$;

CREATE OR REPLACE FUNCTION check_status(text)
RETURNS void LANGUAGE plpgsql 
AS $$ 
DECLARE var1 bigint;
BEGIN
  var1 := upd_status($1);
  IF lookup_status($1) THEN
      RAISE NOTICE 'Status exists as part of immutable check - %', $1;
  ELSE
      RAISE NOTICE 'Status does not exists - %', $1;
  END IF;
  RAISE NOTICE 'Actual Result for status % is %', $1,
    (SELECT EXISTS (SELECT 1 FROM user_status WHERE status = $1 AND col1 = var1));
END;$$;

If you observe the output, the Open status is inserted into user_status via the check_status call, but any call to lookup_status within the same snapshot doesn’t detect this data change.

SELECT check_status('Open');
NOTICE:  Status dose not exists - Open
NOTICE:  Actual Result for status Open is t
 check_status
--------------

(1 row)

Lookup function failed to find new data state. Actual lookup dose shows data being present:

table user_status;
 col1 | status
------+--------
   22 | Open
(1 row)

It took some time to diagnose the root cause: lookup_status was incorrectly labeled as IMMUTABLE by conversion engineer, causing it to return stale results by not capturing changes in data state within the same snapshot.

Note: PostgreSQL does not issue warnings when an IMMUTABLE function executes a SELECT statement on a table. Nonetheless, given that IMMUTABLE functions are predicated on the assumption that the data remains unchanged, any modifications to the table may result in inconsistent outcomes due to the enforced caching of query plans.

Function Volatility – Decision Matrix

Volatility selection provides performance benefits but can also lead to data consistency issues if not carefully considered, especially when migrating code from Oracle or SQL Server. Below is a guide on choosing the appropriate volatility for different use cases:

Use Case	Volatility Category	Reason
Functions with side effects (e.g., updates)	VOLATILE	Ensures functions are re-evaluated each time.
Functions with changing values per call	VOLATILE	Required if values can change within a single query (e.g., `random()`, `currval()`, `timeofday()`).
Functions dependent on transaction context	STABLE	STABLE functions (e.g., `current_timestamp`) remain consistent within a transaction.
Pure functions with no external dependencies	IMMUTABLE	Suitable if function output never changes (e.g., mathematical calculations with no data access or look-ups).
Functions that select data from tables	STABLE	Provides a fixed view within a single query snapshot; IMMUTABLE would cause stale results on data updates.

After updating the volatility of lookup_status to STABLE, it worked as expected, correctly reflecting state changes as part of the caller’s snapshot:

CREATE OR REPLACE FUNCTION lookup_status(text)
RETURNS boolean LANGUAGE plpgsql
STABLE PARALLEL SAFE
AS $$
BEGIN
  RETURN (SELECT EXISTS(SELECT 1 FROM user_status WHERE status = $1));
END;$$;

By carefully setting the appropriate volatility, we can achieve reliable functionality and performance—especially important when working with procedural code across databases.

Conclusion

Setting the right volatility level in PostgreSQL functions can significantly impact performance and data consistency. When developing or migrating code, always assess whether your functions should be marked IMMUTABLE, STABLE, or VOLATILE to avoid unexpected results.