9. Server-Side Programming

PostgreSQL can do far more than store rows and answer queries: it can run your own code next to the data, react automatically to changes, publish derived data, and pass messages between processes. This chapter covers functions and procedures, the PL/pgSQL language, triggers, views and other derived objects, asynchronous messaging and scheduling, and the other procedural languages the server supports. Used well, these features remove round trips and enforce rules that no client can bypass; used carelessly, they hide logic where nobody looks and turn simple writes into slow cascades, so the chapter puts as much weight on the trade-offs as on the syntax.

<a id="91-functions-and-procedures"></a>

9.1 Functions and Procedures

This subchapter covers how to define your own routines in SQL, the difference between functions and procedures, and the attributes (volatility, parameter modes, security context, cost, parallel safety) that tell the planner and the security system how a routine may be used.

SQL Functions

Some calculations appear in many queries: a transfer fee, a customer's display name, an account's current balance. Copying the expression into every query means that when the rule changes, every copy must change too, and some copies will be missed. A function gives the logic one name and one definition, stored in the database, so every client (an application, a report, an ad-hoc psql session) uses exactly the same rule.

Analogy. A function is like a named formula in a spreadsheet: you write =FEE(B2) everywhere instead of retyping the formula, and fixing the formula once fixes every cell.

Sample data for this chapter. Most examples reuse the bank schema from Chapter 8. Run this once; later topics add their own tables where needed:

DROP TABLE IF EXISTS transfers, accounts CASCADE;

CREATE TABLE accounts (
  id      int           PRIMARY KEY,
  owner   text          NOT NULL,
  balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);

CREATE TABLE transfers (
  id         bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  from_id    int           NOT NULL REFERENCES accounts,
  to_id      int           NOT NULL REFERENCES accounts,
  amount     numeric(12,2) NOT NULL CHECK (amount > 0),
  created_at timestamptz   NOT NULL DEFAULT now()
);

INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 50), (3, 'carol', 0);
INSERT INTO transfers (from_id, to_id, amount, created_at) VALUES
  (1, 2, 30, now() - interval '3 days'),
  (2, 3, 10, now() - interval '2 days'),
  (1, 3, 25, now() - interval '1 day');

Anatomy of a SQL function. The body of a LANGUAGE sql function is one or more SQL statements; the result of the last statement is the return value:

CREATE FUNCTION transfer_fee(amount numeric)       -- name and typed parameters
RETURNS numeric                                    -- type of the return value
LANGUAGE sql
IMMUTABLE                                          -- same input -> same output (see Function Volatility Categories)
AS $$
  SELECT round(greatest(amount * 0.01, 0.50), 2)   -- 1 % fee, minimum 0.50
$$;

SELECT id, amount, transfer_fee(amount) AS fee FROM transfers;
--  id | amount | fee
-- ----+--------+------
--   1 |  30.00 | 0.50
--   2 |  10.00 | 0.50
--   3 |  25.00 | 0.50

The $$ ... $$ is dollar quoting: everything between the markers is a string literal, so the body can contain single quotes without escaping. Parameters can be referenced by name (amount) or by position ($1).

SQL-standard function bodies (PostgreSQL 14+). Instead of a string, the body can be written directly with RETURN (one expression) or BEGIN ATOMIC ... END (several statements):

CREATE OR REPLACE FUNCTION transfer_fee(amount numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE
RETURN round(greatest(amount * 0.01, 0.50), 2);    -- parsed and checked at CREATE time

CREATE FUNCTION account_balance(acct int)
RETURNS numeric
LANGUAGE sql STABLE
BEGIN ATOMIC
  SELECT balance FROM accounts WHERE id = acct;
END;

	String body (AS $$ ... $$)	SQL-standard body (RETURN / BEGIN ATOMIC)
Stored as	Source text, re-parsed when first called in each session	A parsed query tree
Checked at CREATE	Yes, if check_function_bodies is on (the default)	Always
Dependencies tracked	No: dropping accounts succeeds and leaves a broken function	Yes: DROP TABLE accounts fails unless CASCADE
Renaming a referenced table	Breaks the function	Function follows the rename
Other languages	Required for PL/pgSQL, PL/Python, ...	LANGUAGE sql only

Inlining: why SQL functions can be free. A simple SQL function (a single SELECT of one expression, not SECURITY DEFINER, without SET clauses, and not more volatile than the context) is inlined: the planner substitutes its body into the calling query, exactly like a macro. There is no function-call overhead, and the planner can use indexes on the underlying expression:

EXPLAIN (VERBOSE, COSTS OFF) SELECT transfer_fee(amount) FROM transfers;
--  Seq Scan on public.transfers
--    Output: round(GREATEST((amount * 0.01), 0.50), 2)   -- the body, not a call to transfer_fee

A function written in PL/pgSQL with the same logic is a black box to the planner and is always called once per row.

NULL handling. By default a function is called even if an argument is NULL. Declaring it STRICT (synonym: RETURNS NULL ON NULL INPUT) makes PostgreSQL return NULL without calling it at all, which is both faster and usually correct.

Changing a function. CREATE OR REPLACE FUNCTION changes the body and attributes in place, preserving privileges and dependent objects. It cannot change the return type or the names of parameters; for that you must DROP FUNCTION transfer_fee(numeric) (the argument types identify which function) and create it again.

Key takeaways

• A function gives one name and one definition to logic that many queries share.
• LANGUAGE sql functions return the result of their last statement; dollar quoting avoids escaping quotes.
• SQL-standard bodies (RETURN, BEGIN ATOMIC) are validated at creation and record dependencies on the objects they use.
• Simple SQL functions are inlined into the calling query, so they cost nothing at run time and stay transparent to the planner.
• STRICT skips the call for NULL arguments; CREATE OR REPLACE cannot change the return type.

🧪 Practice

1. (Beginner) Write a SQL function display_name(owner text, id int) that returns strings like alice (#1), and use it in a query over accounts.
2. (Intermediate) Create account_balance once with a string body and once with BEGIN ATOMIC under different names. Try DROP TABLE accounts (without CASCADE) and ALTER TABLE accounts RENAME TO accts, and explain the different outcomes.
3. (Interview) "We moved a frequently used expression into a function, and a query that used an expression index became slow. Why might that happen, and how would you check?" Hint: Compare the plan of a SQL function that is inlined with one that is not (wrong language, SECURITY DEFINER, or the wrong volatility), and look at the Output and Filter lines of EXPLAIN VERBOSE.

Stored Procedures and Transaction Control

A function always runs inside the transaction of the statement that called it; it can never commit. That is a problem for maintenance jobs such as "move ten million old transfers to an archive table": doing it in one transaction holds locks for a long time, bloats the table, and loses all progress if it fails at row 9,999,999. A procedure (PostgreSQL 11+) is a routine that is invoked with CALL instead of inside a query, and that may COMMIT and ROLLBACK in the middle of its work.

Analogy. A function is a calculator: you hand it inputs mid-sentence and it hands back a value. A procedure is a script you run: it does a sequence of steps and can save its progress along the way.

Functions vs. procedures

	Function	Procedure
Invoked with	Any expression: SELECT f(x), WHERE f(x) > 0	CALL p(x) only, as a statement
Return value	Required (can be void, a scalar, a row, or a set)	None; results go through OUT/INOUT parameters
Transaction control	Never	COMMIT and ROLLBACK allowed (with restrictions)
Volatility, cost, parallel attributes	Yes	No (not used in queries)
Typical use	Calculations, lookups, trigger bodies	Batch jobs, multi-step maintenance, ETL

A batched archive procedure

CREATE TABLE transfers_archive (LIKE transfers);   -- same columns, without the identity property

CREATE PROCEDURE archive_transfers(cutoff timestamptz, batch_size int DEFAULT 10000)
LANGUAGE plpgsql
AS $$
DECLARE
  moved int;
BEGIN
  LOOP
    WITH batch AS (
      DELETE FROM transfers
      WHERE id IN (SELECT id FROM transfers
                   WHERE created_at < cutoff
                   ORDER BY id
                   LIMIT batch_size)
      RETURNING *
    )
    INSERT INTO transfers_archive SELECT * FROM batch;

    GET DIAGNOSTICS moved = ROW_COUNT;             -- rows inserted by the last statement
    COMMIT;                                        -- make this batch durable, release its locks
    RAISE NOTICE 'archived % rows', moved;
    EXIT WHEN moved < batch_size;                  -- last, partial batch done
  END LOOP;
END;
$$;

CALL archive_transfers(now() - interval '1 year', batch_size => 1000);
-- NOTICE:  archived 0 rows              -- nothing that old in the sample data; exercise 2 uses a big table

Each COMMIT ends the current transaction and immediately starts a new one, so the procedure keeps running. If the server crashes in batch 57, batches 1 to 56 are safely archived and the procedure can simply be called again.

Where transaction control is allowed. COMMIT and ROLLBACK work only when nothing above the procedure holds the transaction open:

BEGIN;
CALL archive_transfers(now(), 1000);
-- ERROR:  invalid transaction termination
ROLLBACK;

Situation	Transaction control allowed?
CALL at top level (autocommit)	Yes
CALL from a DO block or another procedure called at top level	Yes
CALL inside an explicit BEGIN ... COMMIT block	No
CALL from inside a function or SELECT	No
Procedure declared SECURITY DEFINER or with a SET clause	No
Inside a PL/pgSQL block that has an EXCEPTION clause	No (the block is a subtransaction)

OUT parameters. Procedures return data through OUT or INOUT parameters; when calling from SQL, pass NULL as a placeholder for each OUT argument:

CREATE PROCEDURE count_old(cutoff timestamptz, OUT n bigint)
LANGUAGE sql
AS $$ SELECT count(*) FROM transfers WHERE created_at < cutoff $$;

CALL count_old(now() - interval '36 hours', NULL);
--  n
-- ---
--  2

Client drivers. Many drivers and ORMs open a transaction implicitly before every statement. A procedure that commits will then fail with "invalid transaction termination"; call it with autocommit enabled.

Key takeaways

• Functions run inside the caller's transaction; procedures, invoked with CALL, may COMMIT and ROLLBACK.
• Batched procedures keep transactions short, release locks regularly, and preserve progress after a failure.
• Transaction control fails inside an explicit transaction block, inside a function, in SECURITY DEFINER procedures, and in blocks with an EXCEPTION clause.
• Procedures return data through OUT/INOUT parameters, not a return value.

🧪 Practice

1. (Beginner) Write a procedure reset_balances() that sets every balance to 0 and commits, then call it both at top level and inside BEGIN ... ROLLBACK. Explain both outcomes.
2. (Intermediate) Load 500,000 transfers with generate_series, run archive_transfers with batch sizes of 1,000 and 50,000, and compare total time and the maximum lock duration observed in pg_locks from a second session.
3. (Interview) "Why not write a batch archival job as a function that loops and deletes in chunks?" Hint: Think about where the transaction boundary is, how long locks are held, how much WAL and dead tuples accumulate before vacuum can clean anything, and what happens on failure.

Function Volatility Categories

The planner wants to call functions as rarely as possible: evaluate lower('ABC') once at plan time instead of a million times, or use an index to compare created_at > now() - interval '1 day'. Whether that is safe depends on how the function behaves. lower('ABC') always returns abc; now() is fixed within a transaction but differs between transactions; random() differs on every call. Each function declares which kind it is through its volatility category, and the planner trusts that declaration completely.

The three categories

Category	Promise	Planner may	Examples
IMMUTABLE	Same arguments always give the same result, forever; no database lookups	Pre-evaluate at plan time with constant arguments; use in index expressions and partition keys	lower, abs, int4pl, transfer_fee
STABLE	Same result for the same arguments within one statement; may read the database	Evaluate once per index scan instead of once per row	now, current_setting, to_char(timestamptz, ...), account_balance
VOLATILE (default)	Can change at any time, or has side effects	Nothing: called every time it appears, for every row	random, clock_timestamp, nextval, any function that writes

SELECT DISTINCT proname, provolatile FROM pg_proc
WHERE proname IN ('lower', 'now', 'random', 'clock_timestamp')
ORDER BY 2, 1;
--      proname     | provolatile
-- -----------------+-------------
--  lower           | i            -- immutable
--  now             | s            -- stable
--  clock_timestamp | v            -- volatile
--  random          | v

Seeing the difference

SELECT count(DISTINCT now())             AS now_values,
       count(DISTINCT clock_timestamp()) AS clock_values
FROM generate_series(1, 1000);
--  now_values | clock_values
-- ------------+--------------
--           1 |          163    -- now() is the transaction start time; clock_timestamp() advances

SELECT count(*) FROM generate_series(1, 1000) WHERE random() < 0.5;
--  count
-- -------
--    497                  -- random() is called once per row, as it must be

Index expressions require IMMUTABLE. An index stores computed values. If the function could return something different tomorrow, the stored values would silently become wrong, so PostgreSQL refuses:

CREATE INDEX ON transfers ((created_at::date));
-- ERROR:  functions in index expression must be marked IMMUTABLE

The cast from timestamptz to date depends on the session's TimeZone setting, so it is only STABLE. Fix it by making the time zone explicit:

CREATE INDEX transfers_day_idx ON transfers (((created_at AT TIME ZONE 'UTC')::date));   -- immutable
SELECT count(*) FROM transfers WHERE (created_at AT TIME ZONE 'UTC')::date = '2026-10-01';  -- can use the index

Visibility of database changes. STABLE and IMMUTABLE functions see the snapshot of the query that called them. A VOLATILE function takes a fresh snapshot for each statement it runs, so it can see rows changed by the same outer query or committed by others in the meantime.

Lying about volatility. Marking a function IMMUTABLE when it is not leads to wrong results, not errors:

CREATE TABLE settings (key text PRIMARY KEY, rate numeric);
INSERT INTO settings VALUES ('fee', 0.01);

CREATE FUNCTION fee_rate() RETURNS numeric LANGUAGE sql IMMUTABLE     -- wrong: reads a table
AS $$ SELECT rate FROM settings WHERE key = 'fee' $$;

The planner may fold fee_rate() into a constant when a query is planned. A prepared statement or a PL/pgSQL function that caches its plan keeps using the old rate after the table changes, and an index built on it holds stale values. Mark it STABLE.

Choosing a category

 does it modify data, or must it differ between calls in one statement?
   yes -> VOLATILE
   no  -> does it read tables, or depend on settings (TimeZone, search_path, DateStyle)?
            yes -> STABLE
            no  -> IMMUTABLE

Key takeaways

• Volatility is a promise to the planner: IMMUTABLE (pure), STABLE (constant within a statement), VOLATILE (anything goes, the default).
• Index expressions, generated columns, and partition keys require IMMUTABLE functions.
• Settings such as TimeZone make many functions only STABLE; make the dependency explicit to get an immutable expression.
• An overstated category produces silently wrong results; an understated one only costs performance.

🧪 Practice

1. (Beginner) Query pg_proc for the volatility of upper, current_date, gen_random_uuid, and to_char. Explain each answer.
2. (Intermediate) Create a VOLATILE and a STABLE version of account_balance and use each in WHERE id = 1 AND balance = account_balance(1) on a table with 100,000 accounts. Compare EXPLAIN ANALYZE output and the number of calls (enable track_functions = 'all' and check pg_stat_user_functions).
3. (Interview) "A developer marks a function that formats a timestamp as IMMUTABLE so it can be used in an index. What can go wrong?" Hint: Think about which session settings the formatting depends on, and what the index contains for sessions with a different setting.

Parameter Modes and Overloading

A routine often needs to return more than one value, accept a variable number of arguments, or be callable with only some arguments specified. PostgreSQL handles these needs with parameter modes, default values, and named notation. It also allows several functions to share one name if their argument types differ, which is called overloading.

Parameter modes

Mode	Direction	Notes
IN (default)	Caller to routine	Read-only inside the routine
OUT	Routine to caller	Not passed when calling a function; together, OUT parameters form the result row
INOUT	Both	Passed in, and returned as part of the result
VARIADIC	Caller to routine	Last parameter only; collects any number of trailing arguments into an array

OUT parameters: returning a record

CREATE FUNCTION account_stats(acct int, OUT sent numeric, OUT received numeric)
LANGUAGE sql STABLE
AS $$
  SELECT coalesce(sum(amount) FILTER (WHERE from_id = acct), 0),
         coalesce(sum(amount) FILTER (WHERE to_id   = acct), 0)
  FROM transfers
$$;                                               -- no RETURNS clause needed: the OUT params define it

SELECT * FROM account_stats(1);
--  sent  | received
-- -------+----------
--  55.00 |     0

Defaults and named notation

CREATE FUNCTION recent_transfers(acct int, days int DEFAULT 30, min_amount numeric DEFAULT 0)
RETURNS bigint LANGUAGE sql STABLE
AS $$
  SELECT count(*) FROM transfers
  WHERE (from_id = acct OR to_id = acct)
    AND created_at > now() - make_interval(days => days)
    AND amount >= min_amount
$$;

SELECT recent_transfers(1);                         -- positional, both defaults
SELECT recent_transfers(1, 7);                      -- positional, days = 7
SELECT recent_transfers(1, min_amount => 20);       -- named: skip days, set min_amount
SELECT recent_transfers(acct => 1, days => 7);      -- fully named, order-independent

Named notation makes calls self-documenting and lets you skip any defaulted argument, not only trailing ones.

VARIADIC

CREATE FUNCTION total_of(VARIADIC amounts numeric[])
RETURNS numeric LANGUAGE sql IMMUTABLE
AS $$ SELECT sum(a) FROM unnest(amounts) AS a $$;

SELECT total_of(10, 20.5, 3);                       -- 33.5
SELECT total_of(VARIADIC ARRAY[1, 2, 3]);           -- pass an existing array

Overloading. A function is identified by its name and its input argument types (its signature). Two functions with the same name but different input types are separate objects:

CREATE FUNCTION find_account(acct_id int)  RETURNS SETOF accounts LANGUAGE sql STABLE
AS $$ SELECT * FROM accounts WHERE id = acct_id $$;

CREATE FUNCTION find_account(owner_name text) RETURNS SETOF accounts LANGUAGE sql STABLE
AS $$ SELECT * FROM accounts WHERE owner = owner_name $$;

SELECT * FROM find_account(2);          -- int version
SELECT * FROM find_account('bob');      -- text version
SELECT * FROM find_account('2');        -- text version! an untyped literal prefers the string category
-- (0 rows)                             -- nobody is named '2'

OUT parameters are not part of a function's signature, so you cannot overload on them, and you refer to the function as find_account(int) when dropping or granting.

Ambiguity traps. Defaults and overloads can make a call match more than one function:

CREATE FUNCTION greet(name text) RETURNS text LANGUAGE sql RETURN 'hi ' || name;
CREATE FUNCTION greet(name text, punct text DEFAULT '!') RETURNS text LANGUAGE sql RETURN 'hi ' || name || punct;

SELECT greet('ann');
-- ERROR:  function greet(unknown) is not unique
-- HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

Both definitions are created without complaint; the error appears only when called. Avoid overloading where defaults would do, and prefer distinct names when arguments mean different things.

Key takeaways

• OUT and INOUT parameters return multiple values as a record; VARIADIC collects trailing arguments into an array.
• Defaults plus named notation (param => value) make calls readable and let callers skip any defaulted argument.
• A function's identity is its name plus input types; OUT parameters are not part of it.
• Untyped string literals resolve to text overloads; defaults combined with overloads can create calls that are "not unique."

🧪 Practice

1. (Beginner) Write account_summary(acct int, OUT owner text, OUT balance numeric, OUT transfer_count bigint) and call it for every account using SELECT a.id, s.* FROM accounts a, account_summary(a.id) s.
2. (Intermediate) Create overloads fee(numeric) and fee(numeric, text DEFAULT 'standard'). Find which calls are ambiguous, then redesign the API so that no call is.
3. (Interview) "A library exposes log_event(text) and later adds log_event(jsonb). Some callers start getting different behavior without changing their code. Why?" Hint: Consider how an untyped literal such as '{"a":1}' is resolved, and which overload wins.

Returning Sets and Tables

Many useful routines produce rows, not a single value: "all transfers of an account," "a daily balance series," "the subtree under this node." A set-returning function (SRF) can be used in FROM just like a table, which makes it a parameterized view: the caller passes arguments, and the function returns a table built from them.

Declaring the result

Declaration	Result shape
RETURNS SETOF accounts	Rows of an existing table or composite type
RETURNS SETOF int	One column of a scalar type
RETURNS TABLE (col1 type1, col2 type2)	New row shape, declared inline (shorthand for OUT params plus SETOF record)
RETURNS SETOF record	Shape chosen by the caller with a column definition list; rarely needed

A table function in SQL

CREATE FUNCTION account_history(acct int)
RETURNS TABLE (happened_at timestamptz, delta numeric, counterparty int)
LANGUAGE sql STABLE
AS $$
  SELECT created_at, -amount, to_id   FROM transfers WHERE from_id = acct
  UNION ALL
  SELECT created_at,  amount, from_id FROM transfers WHERE to_id = acct
  ORDER BY 1
$$;

SELECT * FROM account_history(1);
--          happened_at          | delta  | counterparty
-- -------------------------------+--------+--------------
--  2026-10-02 09:14:03.512+00    | -30.00 |            2
--  2026-10-04 09:14:03.512+00    | -25.00 |            3

-- per-row call with LATERAL: history of every account owned by alice or bob
SELECT a.owner, h.*
FROM accounts AS a
CROSS JOIN LATERAL account_history(a.id) AS h
WHERE a.owner IN ('alice', 'bob');

LATERAL lets the function's argument reference columns of tables earlier in FROM (LATERAL Joins).

Building results in PL/pgSQL. RETURN NEXT appends one row; RETURN QUERY appends the result of a query. Neither ends the function; a plain RETURN (or reaching END) does:

CREATE FUNCTION balance_series(acct int, days int)
RETURNS TABLE (on_day date, closing_balance numeric)
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  running numeric := account_balance(acct);
BEGIN
  FOR d IN 0 .. days - 1 LOOP
    on_day := current_date - d;                               -- assign the OUT columns ...
    closing_balance := running;
    RETURN NEXT;                                              -- ... and emit them as one row
    running := running - coalesce((SELECT sum(delta) FROM account_history(acct) AS h
                                   WHERE h.happened_at::date = current_date - d), 0);
  END LOOP;
END;
$$;

How the two kinds execute

	SQL function	PL/pgSQL function
Inlining	A single SELECT with no side effects (and not VOLATILE, STRICT, or SECURITY DEFINER) is inlined: the planner sees the underlying query and can push filters into it	Never
Result delivery	Streamed when inlined	Fully materialized into a tuplestore before the caller sees row 1 (spills to disk past work_mem)
Row estimate	The real estimate of the underlying query, when inlined	The ROWS attribute (default 1000)

So in SELECT * FROM account_history(1) WHERE delta < -20, the planner merges the outer filter into the function's query and can use indexes on transfers, while a PL/pgSQL SRF always builds its whole result first and filters afterward. For PL/pgSQL SRFs, set a realistic estimate with ROWS (Function Cost and Parallel Safety).

Name collisions in PL/pgSQL. The columns of RETURNS TABLE are variables inside the body. A query that mentions a table column with the same name fails:

-- inside a function that RETURNS TABLE (id int, owner text)
RETURN QUERY SELECT id, owner FROM accounts;
-- ERROR:  column reference "id" is ambiguous
-- DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

Qualify the columns (SELECT a.id, a.owner FROM accounts AS a) or choose distinct output names.

WITH ORDINALITY adds a row number to any SRF's output: SELECT * FROM account_history(1) WITH ORDINALITY AS h(happened_at, delta, cp, n). See also Set-Returning Functions and unnest.

Key takeaways

• RETURNS SETOF type or RETURNS TABLE (...) makes a function usable in FROM, like a parameterized view.
• Combine SRFs with LATERAL to call them once per row of another table.
• Single-SELECT SQL SRFs are inlined and streamed; PL/pgSQL SRFs materialize their full result first and report a fixed row estimate.
• In PL/pgSQL, RETURN NEXT and RETURN QUERY add rows; qualify table columns to avoid conflicts with RETURNS TABLE names.

🧪 Practice

1. (Beginner) Write transfers_between(a int, b int) RETURNS SETOF transfers and query it with an additional WHERE amount > 20.
2. (Intermediate) Load 1 million transfers. Implement account_history in SQL and in PL/pgSQL, then compare EXPLAIN ANALYZE SELECT * FROM account_history(1) LIMIT 5 for both. Explain the timing and row-estimate differences.
3. (Interview) "When would you use a set-returning function instead of a view?" Hint: Think about parameters that cannot be expressed as an outer WHERE, procedural logic, and what the planner can still optimize in each case.

Security Definer vs. Invoker

Suppose the application role should be able to transfer money, but must never run UPDATE accounts SET balance = 1000000 directly. Table privileges are all-or-nothing per command: if the role may update balance, it may set it to anything. What is needed is permission to run one specific, validated operation. A security definer function provides exactly that: it runs with the privileges of its owner, not of the caller.

Analogy. A security definer function is a bank teller. You cannot walk into the vault, but you can ask the teller, who has the vault key, to perform a specific transaction under the bank's rules.

The two modes

	SECURITY INVOKER (default)	SECURITY DEFINER
Runs with privileges of	The calling role	The function's owner
current_user inside	Caller	Owner
session_user inside	Caller's login role	Caller's login role (unchanged)
Typical use	Ordinary logic	Controlled privilege escalation: the "teller" pattern

The teller pattern

CREATE ROLE app_user LOGIN PASSWORD 'secret';
REVOKE ALL ON accounts, transfers FROM app_user;          -- no direct table access

CREATE FUNCTION transfer(src int, dst int, amt numeric)
RETURNS bigint
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp                     -- essential, see below
AS $$
DECLARE
  new_id bigint;
BEGIN
  IF amt <= 0 THEN
    RAISE EXCEPTION 'amount must be positive';
  END IF;
  UPDATE public.accounts SET balance = balance - amt WHERE id = src;
  UPDATE public.accounts SET balance = balance + amt WHERE id = dst;
  INSERT INTO public.transfers (from_id, to_id, amount)
  VALUES (src, dst, amt) RETURNING id INTO new_id;
  RETURN new_id;
END;
$$;

REVOKE EXECUTE ON FUNCTION transfer(int, int, numeric) FROM PUBLIC;   -- functions are executable by everyone by default
GRANT  EXECUTE ON FUNCTION transfer(int, int, numeric) TO app_user;

-- as app_user
SELECT transfer(1, 2, 10);          -- works: runs as the owner
UPDATE accounts SET balance = 0;    -- ERROR:  permission denied for table accounts

The search_path attack. Unqualified names inside the function are resolved through search_path, and without the SET clause that is the caller's search_path. If a caller can create objects in any schema on their path, they can plant a table, function, or operator with a matching name, and the definer's code calls it with the owner's privileges:

 caller:   CREATE SCHEMA evil; CREATE FUNCTION evil.lower(text) ... (does something malicious)
           SET search_path = evil, public;
           SELECT definer_function('x');   -- the definer's call to lower() now runs evil.lower as the owner

Defenses, all of which should be applied:

Defense	Why
SET search_path = pg_catalog, pg_temp on the function	Fixes name resolution; listing pg_temp last stops temporary tables from shadowing real ones
Schema-qualify every object (public.accounts)	Correct under any path
REVOKE EXECUTE ... FROM PUBLIC, then grant to specific roles	Only intended callers can use the escalation
Own the function by a dedicated role with minimal privileges	Limits damage if something is wrong
Validate every argument	The function is a security boundary, like a public API
Never build dynamic SQL from arguments without format('%I', ...)/USING	Otherwise SQL injection runs as the owner (Dynamic SQL with EXECUTE)

Finding definer functions

SELECT p.oid::regprocedure AS function, pg_get_userbyid(p.proowner) AS owner, p.proconfig
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE p.prosecdef AND n.nspname NOT IN ('pg_catalog', 'information_schema');
--              function             |  owner   |              proconfig
-- ----------------------------------+----------+-------------------------------------
--  transfer(integer,integer,numeric) | postgres | {"search_path=pg_catalog, pg_temp"}

A definer function whose proconfig does not set search_path is an audit finding. Owning definer functions by a superuser turns any flaw into full server compromise.

Key takeaways

• SECURITY DEFINER runs a function with its owner's privileges, granting a narrow, validated operation instead of broad table rights.
• Always pin search_path (ending with pg_temp) and schema-qualify names in definer functions.
• EXECUTE is granted to PUBLIC by default; revoke it and grant only to intended roles.
• Treat definer functions as an API boundary: validate inputs, avoid unsafe dynamic SQL, and audit them via pg_proc.prosecdef.

🧪 Practice

1. (Beginner) Create app_user and the transfer function above. Show that app_user can transfer but cannot read accounts directly. Print current_user and session_user from inside the function using RAISE NOTICE.
2. (Intermediate) Create a definer function without the SET search_path clause that calls lower() unqualified. As an unprivileged role, demonstrate the hijack with your own lower in a schema you control, then fix the function and show the attack fails.
3. (Interview) "How would you let support staff look up a customer's account by email without giving them read access to the whole customers table?" Hint: Compare a definer function, a view with restricted columns, and row-level security; consider auditing and how each limits what can be read in bulk.

Function Cost and Parallel Safety

The planner knows how expensive it is to read a page or compare two integers, but it knows nothing about your function: it might add two numbers or call out to a slow external service. Several function attributes tell the planner how to treat calls: how expensive they are, how many rows they return, whether they may run in parallel workers, and whether they might leak data through errors.

Planner-related attributes

Attribute	Default	Effect
COST n	1 for C/internal functions, 100 for other languages	Estimated cost per call, in units of cpu_operator_cost; cheap conditions are evaluated before expensive ones
ROWS n	1000	Estimated rows from a set-returning function (non-inlined)
PARALLEL UNSAFE (default) / RESTRICTED / SAFE	UNSAFE	Whether parallel plans may be used, and where the function may run
LEAKPROOF	Off	Promise that the function reveals nothing about its arguments other than its result; allows pushing it below security barriers (Security Barriers in Views)
SUPPORT fn	None	A C helper that gives the planner custom selectivity, row estimates, or simplifications

COST orders conditions. When a WHERE clause has several conditions, PostgreSQL evaluates them in increasing order of cost, so a cheap test can filter rows before an expensive one runs:

CREATE FUNCTION looks_fraudulent(t transfers) RETURNS boolean
LANGUAGE plpgsql STABLE COST 10000                -- tell the planner this is expensive
AS $$ BEGIN PERFORM pg_sleep(0.001); RETURN t.amount > 1000; END $$;

EXPLAIN (COSTS OFF)
SELECT * FROM transfers AS t WHERE looks_fraudulent(t) AND t.amount > 20;
--  Seq Scan on transfers t
--    Filter: ((amount > '20'::numeric) AND looks_fraudulent(t.*))    -- cheap test first

ROWS fixes join plans. If a PL/pgSQL SRF really returns 3 rows but the planner assumes 1000, it may pick a hash join where a nested loop with index lookups would be far faster:

ALTER FUNCTION balance_series(int, int) ROWS 30;

Parallel safety. Parallel workers (Parallel Query Plans) are separate processes that share the leader's snapshot but not its session state. A function that writes data, uses temporary tables, changes settings, or relies on session-level state cannot safely run in them.

Label	May run in	Use for
PARALLEL SAFE	Any process, including parallel workers	Pure calculations; read-only lookups of ordinary tables
PARALLEL RESTRICTED	Leader only, but the query can still be parallel elsewhere	Functions touching temporary tables, cursors, prepared statements, or client connection state
PARALLEL UNSAFE	Leader, and the whole query must run serially	Anything that writes to the database, uses sequences, or changes settings

Because user-defined functions default to UNSAFE, a single function in the select list can prevent a large aggregation from going parallel:

CREATE TABLE big_transfers AS
SELECT g AS id, round((random() * 1000)::numeric, 2) + 0.01 AS amount
FROM generate_series(1, 5000000) AS g;
ANALYZE big_transfers;

CREATE FUNCTION net_amount(amount numeric) RETURNS numeric
LANGUAGE plpgsql IMMUTABLE
AS $$ BEGIN RETURN amount - transfer_fee(amount); END $$;

EXPLAIN (COSTS OFF) SELECT sum(net_amount(amount)) FROM big_transfers;
--  Aggregate
--    ->  Seq Scan on big_transfers                  -- no parallelism

ALTER FUNCTION net_amount(numeric) PARALLEL SAFE;

EXPLAIN (COSTS OFF) SELECT sum(net_amount(amount)) FROM big_transfers;
--  Finalize Aggregate
--    ->  Gather
--          Workers Planned: 2
--          ->  Partial Aggregate
--                ->  Parallel Seq Scan on big_transfers

Labeling a function SAFE when it is not can produce errors or wrong results; the server does not verify the label. The check runs before inlining, so even simple SQL functions such as transfer_fee need the label: an inlinable but UNSAFE SQL function still forces a serial plan.

Key takeaways

• COST tells the planner how expensive a call is and controls the order of filter evaluation.
• ROWS corrects the default estimate of 1000 rows for non-inlined set-returning functions.
• User functions are PARALLEL UNSAFE by default and can silently disable parallel plans; mark pure functions PARALLEL SAFE.
• LEAKPROOF and SUPPORT are specialized attributes for security barriers and custom planner support.
• None of these labels are verified; incorrect labels give wrong plans at best and wrong results at worst.

🧪 Practice

1. (Beginner) Inspect procost, prorows, and proparallel in pg_proc for transfer_fee, account_history, and random.
2. (Intermediate) Reproduce the serial and parallel plans above with big_transfers and compare run times with EXPLAIN ANALYZE. Then show that the SQL function transfer_fee also blocks parallelism until it is marked PARALLEL SAFE, even though it is inlined.
3. (Interview) "A report query got 4x slower after someone wrapped a calculation in a PL/pgSQL function. Nothing else changed. What are the likely causes?" Hint: List what the planner lost: inlining, parallel safety, a realistic cost and row estimate, and possibly an expression index.

<a id="92-plpgsql"></a>

9.2 PL/pgSQL

This subchapter teaches PL/pgSQL, PostgreSQL's built-in procedural language: how code is structured, how variables, conditions, loops, and cursors work, how to build SQL dynamically without opening an injection hole, how to handle and raise errors, and what makes PL/pgSQL code fast or slow.

Block Structure and Declarations

SQL describes what result you want, but some tasks need steps: check a balance, branch on the result, loop over a set of accounts, catch a specific error and try something else. Doing those steps from the application costs a network round trip for every statement and leaves the logic outside the database. PL/pgSQL ("Procedural Language/PostgreSQL") adds variables, control flow, and error handling on top of SQL, and runs inside the server process, right next to the data.

Analogy. If SQL is a single sentence ("give me all overdrawn accounts"), PL/pgSQL is a recipe: a sequence of sentences with ingredients (variables), conditions ("if the sauce is too thick, add water"), and repetition ("stir every two minutes until done").

The block. All PL/pgSQL code is organized in blocks. A function or procedure body is one block, and blocks can be nested:

 [ <<label>> ]
 [ DECLARE
     declarations; ]          -- variables, constants, cursors, aliases
 BEGIN
     statements;              -- SQL statements and PL/pgSQL control structures
 [ EXCEPTION
     WHEN condition THEN
         handler statements; ]
 END [ label ];

BEGIN and END here only group statements; they do not start or end a transaction, unlike the SQL commands with the same names.

A complete example

CREATE FUNCTION describe_account(acct int)
RETURNS text
LANGUAGE plpgsql STABLE
AS $$
<<outer_block>>
DECLARE
  v_owner   accounts.owner%TYPE;                  -- same type as the column, follows ALTER TABLE
  v_balance accounts.balance%TYPE;
  v_limit   CONSTANT numeric := 20;               -- cannot be reassigned
  v_status  text NOT NULL := 'unknown';           -- NOT NULL requires an initial value
BEGIN
  SELECT owner, balance INTO v_owner, v_balance
  FROM accounts WHERE id = acct;

  IF NOT FOUND THEN
    RETURN 'no such account';
  END IF;

  DECLARE                                         -- nested block with its own scope
    v_status text;                                -- shadows the outer v_status
  BEGIN
    v_status := CASE WHEN v_balance < v_limit THEN 'low' ELSE 'ok' END;
    outer_block.v_status := v_status;             -- label qualifies the outer variable
  END;

  RETURN format('%s: %s (%s)', v_owner, v_balance, v_status);
END outer_block;
$$;

SELECT describe_account(id) FROM accounts ORDER BY id;
--  describe_account
-- -------------------
--  alice: 90.00 (ok)
--  bob: 60.00 (ok)
--  carol: 0.00 (low)

(The balances reflect the transfer call from Security Definer vs. Invoker; yours may differ.)

Declaration forms

Form	Example	Meaning
Scalar	n int := 0;	Ordinary variable with an optional default (otherwise NULL)
Column type	v accounts.balance%TYPE;	Same type as a table column; adapts if the column type changes
Row type	r accounts%ROWTYPE;	A full row of a table; fields accessed as r.balance
Record	rec record;	A row of whatever shape is assigned to it; shape fixed at first assignment
Constant	fee CONSTANT numeric := 0.5;	Read-only
Not null	s text NOT NULL := '';	Assigning NULL raises an error
Alias	src ALIAS FOR $1;	Another name for a parameter (mostly for old code using $1)
Cursor	c CURSOR FOR SELECT ...;	See Cursors and Row Iteration

Anonymous blocks. DO runs a block once without creating a function, which is useful for scripts and migrations. It takes no parameters and returns nothing:

DO $$
DECLARE
  n bigint;
BEGIN
  SELECT count(*) INTO n FROM accounts;
  RAISE NOTICE 'there are % accounts', n;
END;
$$;
-- NOTICE:  there are 3 accounts

When code is checked. CREATE FUNCTION checks only PL/pgSQL syntax. Table and column names inside SQL statements are resolved the first time each statement runs, so a misspelled column is reported at run time, possibly in a rarely taken branch. The plpgsql_check extension finds such errors ahead of time (RAISE and Debugging).

Key takeaways

• PL/pgSQL adds variables, control flow, and error handling to SQL, executing inside the server.
• Code is organized in blocks of DECLARE / BEGIN / EXCEPTION / END; blocks nest, and labels qualify names across scopes.
• PL/pgSQL BEGIN/END group statements; they do not control transactions.
• %TYPE and %ROWTYPE keep variable types in sync with table definitions.
• Syntax is checked at creation; references to tables and columns are checked only when each statement first runs.

🧪 Practice

1. (Beginner) Write a DO block that prints the owner with the highest balance using RAISE NOTICE.
2. (Intermediate) Create a function with a misspelled column in a branch that only runs for negative arguments. Show that CREATE FUNCTION succeeds, that calls with positive arguments succeed, and that the first negative call fails.
3. (Interview) "Why might a team choose PL/pgSQL for a piece of business logic instead of application code, and what would make you argue against it?" Hint: Weigh round trips, data locality, and enforcement for all clients against testing, deployment, version control, debugging tools, and scaling CPU on the database server.

Variables and Assignment

Variables hold intermediate values between statements: a balance read in one step and checked in the next, a counter, a row fetched for later use. PL/pgSQL offers several ways to put values into variables, and choosing the right one decides whether missing or duplicate rows are caught or silently ignored.

Assignment

DO $$
DECLARE
  total   numeric := 0;
  r       accounts%ROWTYPE;
  ids     int[] := '{}';
BEGIN
  total := total + 10;                  -- := (or =) evaluates an expression
  total = total * 2;                    -- equivalent

  SELECT * INTO r FROM accounts WHERE id = 1;     -- whole row into a row variable
  r.balance := r.balance + 1;           -- assign one field (does not touch the table)

  ids := ids || 42;                     -- append to an array
  ids[2] := 7;                          -- assign an array element

  RAISE NOTICE 'total=%, owner=%, ids=%', total, r.owner, ids;
END;
$$;
-- NOTICE:  total=20, owner=alice, ids={42,7}

Behind the scenes, every expression such as total + 10 is evaluated as a small SELECT by the SQL engine, so any SQL expression or function works on the right-hand side.

SELECT INTO and its edge cases. SELECT ... INTO (and INSERT/UPDATE/DELETE ... RETURNING ... INTO) stores the first row of a result in variables:

Rows returned	INTO	INTO STRICT
0	Variables set to NULL, FOUND is false	Error P0002 no_data_found
1	Variables set, FOUND is true	Variables set
More than 1	First row kept silently (order undefined without ORDER BY)	Error P0003 too_many_rows

DO $$
DECLARE
  v_balance numeric;
BEGIN
  SELECT balance INTO STRICT v_balance FROM accounts WHERE owner = 'nobody';
END;
$$;
-- ERROR:  query returned no rows
-- CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

Use STRICT whenever exactly one row is expected (lookup by key); it turns data bugs into loud errors instead of NULLs that propagate.

Status after a statement

Tool	What it tells you
FOUND	Boolean: did the last SELECT INTO, PERFORM, UPDATE, INSERT, DELETE, FETCH, or FOR loop process at least one row?
GET DIAGNOSTICS n = ROW_COUNT;	Number of rows processed by the last SQL command
RETURNING ... INTO	Values generated by the statement itself (identity values, defaults)

CREATE FUNCTION deposit(acct int, amt numeric) RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
  new_balance numeric;
BEGIN
  UPDATE accounts SET balance = balance + amt
  WHERE id = acct
  RETURNING balance INTO new_balance;               -- capture the updated value without a second query

  IF NOT FOUND THEN
    RAISE EXCEPTION 'account % does not exist', acct;
  END IF;
  RETURN new_balance;
END;
$$;

SELECT deposit(3, 5);      -- 5.00
SELECT deposit(99, 5);     -- ERROR:  account 99 does not exist

PERFORM: run a query, discard the result. A SELECT whose result is not stored is an error in PL/pgSQL ("query has no destination for result data"). Use PERFORM instead, for example PERFORM pg_advisory_xact_lock(acct);.

How variables reach SQL. When a variable appears in an SQL statement, PL/pgSQL passes it as a query parameter, not by pasting its text. This makes ordinary statements immune to SQL injection and lets PostgreSQL cache their plans. It also means variables can stand only where a value is allowed; a table or column name in a variable requires dynamic SQL (Dynamic SQL with EXECUTE).

Name conflicts. If a variable and a column share a name, PL/pgSQL raises "column reference is ambiguous". The common convention is a prefix for variables (v_) and parameters (p_), or qualifying with the function name (deposit.acct) or a block label.

Key takeaways

• := evaluates any SQL expression; SELECT INTO and RETURNING INTO copy query results into variables.
• Plain INTO silently accepts zero or many rows; INTO STRICT raises no_data_found or too_many_rows.
• Check FOUND or GET DIAGNOSTICS ... ROW_COUNT after DML to detect "nothing happened."
• Use PERFORM to run a query for its side effects only.
• Variables are passed to SQL as parameters, never spliced in as text; prefix them to avoid column-name conflicts.

🧪 Practice

1. (Beginner) Write withdraw(acct int, amt numeric) using UPDATE ... RETURNING ... INTO that raises an error when the account does not exist.
2. (Intermediate) Add a second account owned by alice. Write a function that looks up a balance by owner with plain INTO, then with INTO STRICT, and show the difference. Decide which behavior a payment system needs.
3. (Interview) "A PL/pgSQL function sometimes returns NULL for a customer's balance even though the customer exists. Where would you look?" Hint: Consider SELECT INTO without STRICT against a wrong or duplicate key, a variable name that shadows a column, and FOUND checked after the wrong statement.

Conditional and Loop Constructs

Business rules branch ("accounts below 10 pay a fee, accounts above 1000 earn interest") and repeat ("for each account, apply the rule"). PL/pgSQL provides the familiar structures of procedural languages. The important skill is not the syntax but knowing when not to loop: a single set-based SQL statement is almost always faster than a loop that processes one row at a time (Performance Considerations).

Conditionals

CREATE FUNCTION fee_tier(balance numeric) RETURNS text
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
  IF balance IS NULL THEN
    RETURN 'unknown';
  ELSIF balance < 10 THEN                       -- note the spelling: ELSIF (or ELSEIF)
    RETURN 'fee';
  ELSIF balance > 1000 THEN
    RETURN 'interest';
  ELSE
    RETURN 'standard';
  END IF;
END;
$$;

CASE comes in two forms. Unlike the SQL CASE expression, the PL/pgSQL CASE statement raises CASE_NOT_FOUND when no branch matches and there is no ELSE:

CASE tier                                       -- simple CASE: compares one value
  WHEN 'fee'      THEN amount := 2;
  WHEN 'interest' THEN amount := -5;
  ELSE                 amount := 0;
END CASE;

CASE                                            -- searched CASE: arbitrary conditions
  WHEN balance < 0  THEN RAISE EXCEPTION 'negative balance';
  WHEN balance = 0  THEN status := 'empty';
  ELSE                   status := 'active';
END CASE;

Loops

Construct	Iterates over	Example
LOOP ... END LOOP	Until EXIT	Retry loops, polling
WHILE cond LOOP	While a condition holds	Halving until a threshold
FOR i IN a .. b [BY s] LOOP	An integer range (REVERSE counts down)	Generating days, batches
FOR rec IN query LOOP	Rows of a query	Per-row processing that needs procedural logic
FOREACH x IN ARRAY arr LOOP	Array elements (SLICE n for subarrays)	Processing a list of IDs passed as a parameter

EXIT [label] [WHEN cond] leaves a loop; CONTINUE [label] [WHEN cond] skips to the next iteration. Labels let an inner loop exit an outer one.

CREATE PROCEDURE apply_monthly_fees()
LANGUAGE plpgsql
AS $$
DECLARE
  acc  record;
  fee  numeric;
  charged int := 0;
BEGIN
  FOR acc IN SELECT id, balance FROM accounts ORDER BY id LOOP
    CONTINUE WHEN fee_tier(acc.balance) <> 'fee';       -- only low balances pay
    fee := least(2, acc.balance);                       -- never take the balance below 0
    UPDATE accounts SET balance = balance - fee WHERE id = acc.id;
    charged := charged + 1;
  END LOOP;
  RAISE NOTICE '% accounts charged', charged;
END;
$$;

The same rule as one set-based statement, which is shorter and much faster on large tables:

UPDATE accounts
SET balance = balance - least(2, balance)
WHERE balance < 10;

FOREACH over an array parameter

CREATE FUNCTION total_balance(acct_ids int[]) RETURNS numeric
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  acct  int;
  total numeric := 0;
BEGIN
  FOREACH acct IN ARRAY acct_ids LOOP
    total := total + coalesce(account_balance(acct), 0);
  END LOOP;
  RETURN total;
END;
$$;
-- set-based alternative: SELECT sum(balance) FROM accounts WHERE id = ANY(acct_ids);

A retry loop with a label

<<retry>>
FOR attempt IN 1 .. 3 LOOP
  BEGIN
    PERFORM transfer(1, 2, 10);
    EXIT retry;                                         -- success: leave the labeled loop
  EXCEPTION WHEN serialization_failure OR deadlock_detected THEN
    RAISE NOTICE 'attempt % failed, retrying', attempt;
    PERFORM pg_sleep(0.05 * attempt);                   -- simple backoff
  END;
END LOOP retry;

Retrying inside the database only helps for errors that affect a single statement or subtransaction. A serialization failure invalidates the whole transaction at SERIALIZABLE, so such retries belong in the client (Serialization Failures and Retries).

Key takeaways

• IF ... ELSIF ... ELSE and CASE provide branching; a PL/pgSQL CASE statement without ELSE raises an error when nothing matches.
• LOOP, WHILE, integer FOR, query FOR, and FOREACH cover all iteration needs; labels control nested exits.
• EXIT WHEN and CONTINUE WHEN keep loop bodies flat and readable.
• Before writing a loop over rows, try to express the logic as one set-based statement.

🧪 Practice

1. (Beginner) Write a function fizzbuzz(n int) RETURNS SETOF text using an integer FOR loop and RETURN NEXT.
2. (Intermediate) Generate 200,000 accounts with random balances, then time apply_monthly_fees() against the single UPDATE. Report both times and explain the gap.
3. (Interview) "When is a row-by-row loop in PL/pgSQL justified?" Hint: Think about per-row logic that depends on results of earlier iterations, calls to external side effects, committing in batches inside a procedure, and per-row error isolation.

Cursors and Row Iteration

A query that returns ten million rows cannot be loaded into memory in one piece. A cursor is a handle to a running query that produces rows on demand: you fetch a few, process them, and fetch more. PL/pgSQL uses cursors automatically in every FOR rec IN query loop; explicit cursors are needed when you want to control fetching yourself, pass a result set to a client, or update the row you are positioned on.

Analogy. A cursor is a bookmark in a very long book. Instead of photocopying the whole book, you read a page, move the bookmark, and read the next one.

Implicit cursor: the FOR loop. The loop below never holds more than a small batch of rows in memory, however large transfers is:

FOR rec IN SELECT * FROM transfers ORDER BY id LOOP
  -- process rec
END LOOP;

Explicit cursors

CREATE FUNCTION largest_transfers(n int) RETURNS SETOF transfers
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  c CURSOR (min_amt numeric) FOR                 -- bound cursor with a parameter
    SELECT * FROM transfers WHERE amount >= min_amt ORDER BY amount DESC;
  t transfers%ROWTYPE;
BEGIN
  OPEN c(min_amt := 20);                         -- the query starts executing (named argument uses :=)
  FOR i IN 1 .. n LOOP
    FETCH c INTO t;                              -- one row
    EXIT WHEN NOT FOUND;                         -- no more rows
    RETURN NEXT t;
  END LOOP;
  CLOSE c;                                       -- release resources (also done at transaction end)
END;
$$;

Command	Purpose
DECLARE c CURSOR FOR query	Bound cursor: query fixed at declaration (may take parameters)
DECLARE c refcursor	Unbound cursor: query supplied at OPEN
OPEN c FOR query / OPEN c FOR EXECUTE sql	Start a static or dynamic query
FETCH [direction] c INTO vars	Read one row; directions include NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, RELATIVE n (backward moves need SCROLL)
MOVE [direction] c	Reposition without reading
UPDATE ... WHERE CURRENT OF c	Modify the row the cursor is positioned on
CLOSE c	Close the cursor

Updating the current row. WHERE CURRENT OF updates exactly the row last fetched, without looking it up again:

DO $$
DECLARE
  c CURSOR FOR SELECT id, balance FROM accounts FOR UPDATE;   -- lock rows as they are fetched
  r record;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO r;
    EXIT WHEN NOT FOUND;
    IF r.balance > 60 THEN
      UPDATE accounts SET balance = balance * 1.01 WHERE CURRENT OF c;   -- 1 % interest
    END IF;
  END LOOP;
  CLOSE c;
END;
$$;

Returning a cursor to the client. A function can open a cursor and return its name (refcursor); the client then fetches from it in pieces, within the same transaction. This streams a large result through a function call without materializing it:

CREATE FUNCTION open_history(acct int) RETURNS refcursor
LANGUAGE plpgsql
AS $$
DECLARE
  c refcursor := 'history_cur';                 -- a fixed portal name the client can use
BEGIN
  OPEN c FOR SELECT * FROM account_history(acct);
  RETURN c;
END;
$$;

BEGIN;
SELECT open_history(1);                          -- returns 'history_cur'
FETCH 100 FROM history_cur;                      -- client fetches in batches
FETCH 100 FROM history_cur;
COMMIT;                                          -- cursor closed at transaction end

Cursor lifetime. Cursors live until they are closed or the transaction ends. In a procedure, a COMMIT inside a FOR loop over a query is allowed: PostgreSQL converts the loop's cursor into a holdable one, which materializes the remaining result. Server-side cursors with WITH HOLD in plain SQL behave similarly.

Key takeaways

• Cursors produce rows on demand, keeping memory use flat for large results; every FOR rec IN query loop uses one internally.
• Explicit cursors (OPEN, FETCH, MOVE, CLOSE) give manual control, parameters, and dynamic queries.
• FOR UPDATE cursors combined with WHERE CURRENT OF modify the current row without a second lookup.
• Returning a refcursor lets a client stream a function's result in batches within one transaction.

🧪 Practice

1. (Beginner) Use an explicit cursor to print the first three transfers ordered by amount, then use MOVE to skip one row and print the next.
2. (Intermediate) Write open_transfers(min_amount numeric) RETURNS refcursor, call it from psql, and fetch the result in batches of 2. Then show what happens when you try to fetch after COMMIT.
3. (Interview) "A report function in PL/pgSQL runs out of memory when it processes a large table. How would you restructure it?" Hint: Distinguish between building a huge array or RETURN NEXT result (materialized) and streaming rows through a cursor to the client or processing them in batches.

Dynamic SQL with EXECUTE

Ordinary statements in PL/pgSQL are fixed when the function is written; only values can vary, through variables. Some tasks need the structure of the statement to vary: create next month's partition named transfers_2026_11, count the rows of whichever table the caller names, or sort by a column chosen at run time. Dynamic SQL builds the statement as a string and runs it with EXECUTE. It is powerful and the single most common source of SQL injection inside the database.

Basic form

EXECUTE command_string [ INTO [STRICT] target ] [ USING expr [, ...] ];

• command_string is the SQL text, typically built with format().
• USING passes values as parameters $1, $2, ..., safely and without quoting.
• INTO captures the first result row, as with SELECT INTO.

Identifiers vs. values

Part of the statement	How to insert it	Example
Table, column, schema name	format('%I', name) or quote_ident(name)	format('SELECT count(*) FROM %I', tbl)
Data value	USING parameter (best), or format('%L', val) / quote_literal	EXECUTE '... WHERE id = $1' USING acct
Fixed SQL keywords or trusted fragments	format('%s', txt)	Only for text you fully control (for example, a whitelist)

%I adds double quotes when needed ("Weird Name"), %L adds single quotes and escapes embedded quotes, and NULL with %L becomes the unquoted keyword NULL.

A safe dynamic function

CREATE FUNCTION count_rows(p_schema text, p_table text, p_min_id bigint DEFAULT 0)
RETURNS bigint
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  n bigint;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I.%I WHERE id >= $1', p_schema, p_table)
    INTO n
    USING p_min_id;                               -- value passed as a parameter, never as text
  RETURN n;
END;
$$;

SELECT count_rows('public', 'transfers');         -- 4
SELECT count_rows('public', 'transfers; DROP TABLE accounts');
-- ERROR:  relation "public.transfers; DROP TABLE accounts" does not exist    -- treated as a name, harmless

The vulnerable version. Concatenating input into the command lets the input change the statement:

-- DO NOT DO THIS
EXECUTE 'SELECT count(*) FROM transfers WHERE from_id = ' || p_filter INTO n;
-- p_filter = '1 OR true'                         -> counts every row
-- p_filter = '1; UPDATE accounts SET balance = 1e9' -> runs a second statement

In a SECURITY DEFINER function the injected statement runs with the owner's privileges, which is why dynamic SQL is the first thing to review in such functions (SQL Injection and Parameterized Queries).

Typical use: partition maintenance

CREATE PROCEDURE create_month_partition(p_parent regclass, p_month date)
LANGUAGE plpgsql
AS $$
DECLARE
  v_start date := date_trunc('month', p_month);
  v_end   date := v_start + interval '1 month';
  v_name  text := format('%s_%s', p_parent::text, to_char(v_start, 'YYYY_MM'));
BEGIN
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS %I PARTITION OF %s FOR VALUES FROM (%L) TO (%L)',
    v_name, p_parent, v_start, v_end);            -- regclass prints as a correctly quoted name
  RAISE NOTICE 'ensured partition %', v_name;
END;
$$;

Dynamic results. RETURN QUERY EXECUTE returns the rows of a dynamic query from a set-returning function, and OPEN c FOR EXECUTE opens a dynamic cursor. After EXECUTE, GET DIAGNOSTICS n = ROW_COUNT reports the affected rows (FOUND is not set by EXECUTE).

Planning. A dynamic statement is parsed and planned on every execution; its plan is never cached. That adds overhead in tight loops, but it can be an advantage when parameter values are so skewed that a cached generic plan would be poor (Generic vs. Custom Plans).

Key takeaways

• Use EXECUTE only when identifiers or statement structure vary; values can always be plain variables.
• Insert identifiers with format('%I') (or a regclass parameter) and pass values with USING.
• Never concatenate untrusted text into a command; in definer functions this is privilege escalation.
• EXECUTE does not set FOUND; use GET DIAGNOSTICS. Dynamic statements are planned on every run.

🧪 Practice

1. (Beginner) Write max_value(p_table text, p_column text) RETURNS numeric using format('%I'), and test it with a column whose name contains a space.
2. (Intermediate) Write a deliberately vulnerable function that filters transfers by a text argument concatenated into the query. Exploit it to return all rows, then fix it with USING and show the exploit fails.
3. (Interview) "An API lets users sort a result by any column name they pass in. How would you implement this safely in PL/pgSQL?" Hint: Combine %I quoting with a whitelist checked against the expected column names, and think about the sort direction, which is a keyword, not an identifier.

Exception Handling and Error Codes

Errors are a normal part of database work: a unique constraint is violated because two users picked the same name, a foreign key is missing, a lock cannot be acquired. By default, any error aborts the whole statement and the transaction around it. PL/pgSQL can catch errors in a block, undo only that block's work, and continue, for example returning the existing row instead of failing, or translating a low-level constraint error into a message the application understands.

Syntax

BEGIN
  -- statements that might fail
EXCEPTION
  WHEN unique_violation THEN                       -- by condition name
    ...
  WHEN SQLSTATE '23503' THEN                       -- or by five-character SQLSTATE code
    ...
  WHEN check_violation OR not_null_violation THEN  -- several conditions in one handler
    ...
  WHEN OTHERS THEN                                 -- anything except query_canceled and assert_failure
    ...
END;

What happens on an error. When an error occurs inside a block that has an EXCEPTION clause, every database change made in that block is rolled back, and control jumps to the first matching handler. Variables keep their values. Errors without a matching handler propagate to the enclosing block, and finally to the caller.

 BEGIN (block with EXCEPTION)      <- implicit savepoint taken here
   INSERT ...  ok
   UPDATE ...  ERROR 23505
 EXCEPTION WHEN unique_violation   <- rollback to the savepoint: the INSERT is undone too
   handler runs
 END                               <- execution continues after the block

Example: get or create

CREATE TABLE customers (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text UNIQUE NOT NULL);

CREATE FUNCTION customer_id_for(p_email text) RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
  v_id int;
BEGIN
  BEGIN
    INSERT INTO customers (email) VALUES (p_email) RETURNING id INTO v_id;
  EXCEPTION WHEN unique_violation THEN
    SELECT id INTO STRICT v_id FROM customers WHERE email = p_email;   -- someone else created it first
  END;
  RETURN v_id;
END;
$$;

(INSERT ... ON CONFLICT solves this particular case without a subtransaction; see INSERT ON CONFLICT Upserts. The pattern matters for errors that have no such shortcut.)

Inspecting the error. Inside a handler, SQLSTATE and SQLERRM hold the code and message. GET STACKED DIAGNOSTICS gives every detail:

DO $$
DECLARE
  v_state text; v_msg text; v_detail text; v_constraint text; v_context text;
BEGIN
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS
    v_state      = RETURNED_SQLSTATE,
    v_msg        = MESSAGE_TEXT,
    v_detail     = PG_EXCEPTION_DETAIL,
    v_constraint = CONSTRAINT_NAME,
    v_context    = PG_EXCEPTION_CONTEXT;
  RAISE NOTICE 'state=% constraint=% msg=%', v_state, v_constraint, v_msg;
END;
$$;
-- NOTICE:  state=23514 constraint=accounts_balance_check msg=new row for relation "accounts" violates check constraint "accounts_balance_check"

Common error codes. The first two characters are the class:

SQLSTATE	Condition name	Typical cause
23505	unique_violation	Duplicate key
23503	foreign_key_violation	Missing parent row, or deleting a referenced row
23514	check_violation	CHECK constraint failed
23502	not_null_violation	NULL in a NOT NULL column
40001	serialization_failure	Conflict under REPEATABLE READ/SERIALIZABLE
40P01	deadlock_detected	Deadlock victim
55P03	lock_not_available	NOWAIT or lock_timeout
57014	query_canceled	Cancel request or statement_timeout
22012	division_by_zero	Arithmetic error
P0001	raise_exception	RAISE EXCEPTION without a specific code
P0002 / P0003	no_data_found / too_many_rows	INTO STRICT

Translating errors. A handler can re-raise with a clearer message, keeping a stable code for the application:

CREATE OR REPLACE FUNCTION withdraw(acct int, amt numeric) RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts SET balance = balance - amt WHERE id = acct;
EXCEPTION WHEN check_violation THEN
  RAISE EXCEPTION 'insufficient funds in account %', acct
    USING ERRCODE = 'BNK01',                       -- custom SQLSTATE the app can match on
          HINT    = 'Check the balance before withdrawing.';
END;
$$;

Inside a handler, a bare RAISE; re-throws the original error unchanged.

The cost of EXCEPTION blocks. Each entry into a block with an EXCEPTION clause starts a subtransaction (an internal savepoint), whether or not an error occurs. This has real costs:

• Entering and leaving the block is noticeably slower than a plain block; in a loop over millions of rows it adds up.
• A subtransaction that writes data gets its own transaction ID. Once a top-level transaction has more than 64 of them, the per-backend subtransaction cache overflows, which slows down visibility checks for every session (and on replicas) until that transaction ends.
• COMMIT is not allowed inside such a block in a procedure.

Catch errors where you can do something useful about them, and keep EXCEPTION blocks out of tight loops (Savepoints and Nested Rollbacks explains subtransactions in depth).

Key takeaways

• An EXCEPTION clause catches errors by condition name or SQLSTATE; the block's database changes are rolled back, but variables survive.
• SQLSTATE, SQLERRM, and GET STACKED DIAGNOSTICS describe the caught error; RAISE; re-throws it.
• Match on SQLSTATE codes, not message text; translate low-level errors into stable, application-level codes.
• WHEN OTHERS does not catch query cancellation; avoid using it to hide errors.
• Every EXCEPTION block is a subtransaction, with CPU, XID, and concurrency costs in loops.

🧪 Practice

1. (Beginner) Write a function safe_divide(a numeric, b numeric) that returns NULL instead of failing on division by zero.
2. (Intermediate) Write transfer_safe(src, dst, amt) that maps check_violation to a custom BNK01 error and foreign_key_violation to BNK02. Verify from psql with \set VERBOSITY verbose that the codes come through.
3. (Interview) "A batch import wraps each row's insert in a PL/pgSQL BEGIN ... EXCEPTION block to skip bad rows. It becomes very slow and the whole cluster's read performance degrades. Why?" Hint: Count subtransactions per transaction, think about the 64-entry subtransaction cache, and consider validating rows in bulk first or using a staging table.

RAISE and Debugging

Code running inside the server has no console or debugger attached by default. RAISE is the built-in way to emit messages, from progress notes to fatal errors, and it is also the main way to report errors with useful details. For deeper problems, several tools let you trace, profile, and statically check PL/pgSQL code.

Message levels

Level	Aborts the transaction?	Default visibility
DEBUG	No	Hidden unless client_min_messages / log_min_messages is lowered to debug1–debug5
LOG	No	Written to the server log; not sent to the client by default
INFO	No	Always sent to the client
NOTICE	No	Sent to the client (default client_min_messages = notice)
WARNING	No	Sent to the client and logged
EXCEPTION (default)	Yes	Error returned to the client and logged

Formatting and extra fields. Each % in the message is replaced by the next argument. USING attaches structured fields that clients can read separately:

DROP FUNCTION withdraw(int, numeric);              -- the return type changes, so replace is not enough

CREATE FUNCTION withdraw(acct int, amt numeric) RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
  v_balance numeric;
BEGIN
  RAISE DEBUG 'withdraw(%, %) called', acct, amt;          -- visible only when debugging

  SELECT balance INTO STRICT v_balance FROM accounts WHERE id = acct FOR UPDATE;

  IF v_balance < amt THEN
    RAISE EXCEPTION 'insufficient funds'
      USING ERRCODE = 'BNK01',
            DETAIL  = format('Account %s has %s, requested %s.', acct, v_balance, amt),
            HINT    = 'Use a smaller amount or deposit first.';
  END IF;

  UPDATE accounts SET balance = balance - amt WHERE id = acct
  RETURNING balance INTO v_balance;
  RETURN v_balance;
END;
$$;

SELECT withdraw(3, 1000);
-- ERROR:  insufficient funds
-- DETAIL:  Account 3 has 3.00, requested 1000.
-- HINT:  Use a smaller amount or deposit first.
-- CONTEXT:  PL/pgSQL function withdraw(integer,numeric) line 10 at RAISE

The CONTEXT line shows the function and line number. Other USING options are MESSAGE, COLUMN, CONSTRAINT, DATATYPE, TABLE, and SCHEMA. A custom SQLSTATE should be five characters; avoid codes ending in 000 (which denote a whole class) and pick a class not used by PostgreSQL.

Seeing DEBUG messages in one session

SET client_min_messages = debug1;
SELECT withdraw(1, 1);
-- DEBUG:  withdraw(1, 1) called
RESET client_min_messages;

ASSERT: checks for impossible states. ASSERT condition, 'message' raises assert_failure (P0004) if the condition is false or NULL. Assertions can be switched off globally with plpgsql.check_asserts = off, so never use them for input validation:

ASSERT v_balance >= 0, format('balance went negative for account %s', acct);

Compiler warnings. PL/pgSQL can warn about suspicious code at creation time:

SET plpgsql.extra_warnings = 'shadowed_variables, strict_multi_assignment, too_many_rows';
-- shadowed_variables: an inner variable hides an outer one
-- strict_multi_assignment: SELECT INTO with a different number of columns and targets
-- too_many_rows: plain SELECT INTO that returns more than one row (checked at run time)

plpgsql.extra_errors accepts the same list but turns warnings into errors; use it in development and CI.

Debugging and profiling tools

Tool	What it does
RAISE NOTICE / RAISE DEBUG	Print-style tracing
plpgsql_check extension	Static analysis (unknown columns, type mismatches, unused variables, dead code) and a line-level profiler
pldebugger (pldbgapi)	Breakpoints and stepping from pgAdmin
track_functions = 'pl' + pg_stat_user_functions	Call counts, total and self time per function
pg_stat_statements.track = 'all'	Records the SQL statements executed inside functions
auto_explain with log_nested_statements = on	Logs execution plans of statements inside functions (auto_explain Module)

CREATE EXTENSION plpgsql_check;
SELECT * FROM plpgsql_check_function('withdraw(int, numeric)');   -- empty result = no issues found

SET track_functions = 'pl';                                       -- superuser or via postgresql.conf
SELECT funcname, calls, round(total_time) AS total_ms, round(self_time) AS self_ms
FROM pg_stat_user_functions ORDER BY total_time DESC LIMIT 5;

Key takeaways

• RAISE emits messages at levels DEBUG to EXCEPTION; only EXCEPTION aborts.
• USING ERRCODE, DETAIL, HINT produces structured, actionable errors; the CONTEXT line pinpoints the source line.
• ASSERT is for internal invariants and can be disabled; never use it to validate input.
• plpgsql.extra_warnings/extra_errors and plpgsql_check catch bugs before run time.
• pg_stat_user_functions, pg_stat_statements with track = 'all', and auto_explain show where time is spent inside functions.

🧪 Practice

1. (Beginner) Add RAISE DEBUG messages to transfer and display them in your session only.
2. (Intermediate) Write a function with a shadowed variable and a SELECT INTO with mismatched column counts. Enable plpgsql.extra_errors = 'all' and show that CREATE FUNCTION now fails. If available, run plpgsql_check_function on it.
3. (Interview) "Users report that one API call is slow; it calls a single PL/pgSQL function that runs a dozen queries. How do you find which statement is slow?" Hint: Function-level statistics only give totals; consider pg_stat_statements with nested tracking, auto_explain.log_nested_statements, and a line profiler.

Performance Considerations

PL/pgSQL is fast at what it is designed for: coordinating a handful of SQL statements next to the data. It is slow at what it is not designed for: crunching millions of rows one at a time, or heavy computation that a compiled language would do far better. Knowing how it executes explains both.

How a PL/pgSQL function runs

 first call in a session
   source text --parse--> instruction tree (cached for the session)
 each SQL statement inside, on first execution
   statement --prepare--> cached plan (custom plans first, possibly generic later)
 each simple expression (x := a + 1, IF n > 0)
   evaluated by a fast path in the executor, without full query overhead

Each SQL statement in a function is a prepared statement. That saves planning time on repeated calls, but it also inherits the generic-plan behavior described in Generic vs. Custom Plans: after five executions, PostgreSQL may switch to a plan that ignores the actual parameter values.

Rule 1: think in sets. Every statement executed from a loop pays for a call into the SQL executor. A set-based statement does the same work in one call:

CREATE TABLE ledger AS
SELECT g AS id, (random() * 100)::numeric(12,2) AS amount, false AS processed
FROM generate_series(1, 200000) AS g;
ALTER TABLE ledger ADD PRIMARY KEY (id);

VACUUM ANALYZE ledger;

-- row by row: 200,000 UPDATE statements, each planned (once) and executed separately
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT id FROM ledger LOOP
    UPDATE ledger SET processed = true WHERE id = r.id;     -- one index lookup per row
  END LOOP;
END $$;
-- Time: 2167 ms

-- set-based (measured on a freshly created copy of the table): one UPDATE, one sequential pass
UPDATE ledger SET processed = true;
-- Time: 894 ms

-- the loop again, with an EXCEPTION block around the UPDATE (one subtransaction per row)
-- Time: 2880 ms

Even in this best case for the loop (a trivial body and a primary-key lookup), the set-based statement is about 2.5 times faster, and each EXCEPTION block adds another third. The gap widens with any extra per-row work, and it becomes orders of magnitude when the loop runs in the application with a network round trip per row.

Rule 2: keep EXCEPTION blocks out of hot loops. Each entry creates a subtransaction (Exception Handling and Error Codes). Validate data up front with set-based checks, or isolate failures in batches instead of per row.

Rule 3: let SQL functions be inlined. A one-line calculation written in PL/pgSQL is a black-box call per row; the same logic in LANGUAGE sql is inlined into the query and can use indexes (SQL Functions).

Rule 4: declare attributes honestly. Correct volatility allows constant folding and index use; PARALLEL SAFE allows parallel plans; ROWS gives the planner realistic estimates (Function Cost and Parallel Safety).

Rule 5: watch plan caching. If a statement inside a function performs well for most arguments but badly for some (for example, an account with millions of transfers), the cached generic plan may be the cause. Options:

ALTER FUNCTION account_history(int) SET plan_cache_mode = force_custom_plan;   -- replan per call
-- or build that one statement with EXECUTE, which is planned on every run

Rule 6: avoid repeated work in loops. Move invariant queries out of loops, fetch needed data with one join instead of a lookup per iteration, and accumulate results in arrays or temporary tables only when necessary.

Choosing the right tool

Task	Best fit
Transform or aggregate many rows	Plain SQL (CTEs, window functions, UPDATE ... FROM)
Small reusable expression	LANGUAGE sql function (inlined)
Multi-step logic with branching and error handling	PL/pgSQL
Batched maintenance with commits	PL/pgSQL procedure
Heavy numeric or string computation	C, or PL/Python with libraries, or do it in the application (9.6)

Key takeaways

• PL/pgSQL compiles functions once per session and caches a prepared plan for each embedded statement.
• Set-based SQL beats row-by-row loops by large factors; loop only when logic truly depends on per-row state.
• EXCEPTION blocks in loops create costly subtransactions.
• Inlinable SQL functions, honest volatility, parallel safety, and ROWS estimates let the planner optimize around your code.
• For statements with skewed data, generic cached plans can be slow; use plan_cache_mode or EXECUTE.

🧪 Practice

1. (Beginner) Reproduce the ledger timing comparison on your machine using \timing on.
2. (Intermediate) Add an EXCEPTION WHEN OTHERS THEN NULL block inside the row-by-row loop and measure again. Then check pg_stat_activity.backend_xid and pg_stat_get_backend_subxact (PostgreSQL 16+) from another session while the loop runs.
3. (Interview) "A PL/pgSQL function is fast for most customers but takes 30 seconds for a few large ones, and only after it has been called several times in a session. Explain what is happening." Hint: Think about prepared statements inside PL/pgSQL, the switch from custom to generic plans after five executions, and how plan_cache_mode or EXPLAIN of the generic plan would confirm it.

<a id="93-triggers-and-event-handling"></a>

9.3 Triggers and Event Handling

This subchapter explains triggers, functions that PostgreSQL runs automatically when data changes, and event triggers, which react to schema changes. It covers when triggers fire, what they can see and change, how to use them for validation and auditing, and the pitfalls that make trigger-heavy schemas hard to reason about.

Row-Level vs. Statement-Level Triggers

Some rules must hold no matter which client changes the data: "every change to accounts is logged," "updated_at always reflects the last modification," "a transfer always updates both balances." If each application is responsible for this, one forgotten code path (a script, a migration, an admin running SQL by hand) breaks the rule. A trigger attaches the rule to the table itself, so the database runs it on every matching change.

Analogy. A trigger is a motion-sensor light: nobody has to remember to switch it on, because it reacts to the event itself.

Two parts. A trigger always consists of a trigger function (the code, declared RETURNS trigger) and a CREATE TRIGGER statement that binds it to a table, an event, a timing, and a level:

ALTER TABLE accounts ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();              -- NEW is the row about to be written
  RETURN NEW;                           -- write this (modified) row
END;
$$;

CREATE TRIGGER accounts_set_updated_at
BEFORE UPDATE ON accounts               -- timing and event
FOR EACH ROW                            -- level
EXECUTE FUNCTION set_updated_at();

The two levels. The level decides how often the function runs:

 UPDATE accounts SET balance = balance + 1 WHERE id IN (1, 2, 3);

 FOR EACH ROW:        fires 3 times   (row 1) (row 2) (row 3)
 FOR EACH STATEMENT:  fires 1 time    (statement)
 ... WHERE id = 999:  row-level fires 0 times; statement-level still fires once

	Row-level (FOR EACH ROW)	Statement-level (FOR EACH STATEMENT, default)
Fires	Once per affected row	Once per statement, even if no rows are affected
Sees OLD/NEW	Yes	No (NULL)
Can modify or skip the row	Yes, in BEFORE triggers	No
Sees all changed rows	Only via transition tables in AFTER triggers	Via transition tables (REFERENCING)
Cost on a 1 million-row statement	1 million function calls	1 call
Typical use	Defaults, validation, per-row derived values	Bulk summaries, logging, refreshing caches

Transition tables. An AFTER trigger can declare REFERENCING OLD TABLE AS ... NEW TABLE AS ... to receive all affected rows as a read-only table. Combined with a statement-level trigger, this processes a bulk change with one set-based query instead of a million calls:

CREATE TABLE account_totals (owner text PRIMARY KEY, total numeric NOT NULL);
INSERT INTO account_totals SELECT owner, sum(balance) FROM accounts GROUP BY owner;

CREATE FUNCTION refresh_totals() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO account_totals AS t (owner, total)
  SELECT owner, sum(delta)
  FROM (SELECT owner,  balance AS delta FROM new_rows
        UNION ALL
        SELECT owner, -balance        FROM old_rows) AS changes
  GROUP BY owner
  ON CONFLICT (owner) DO UPDATE SET total = t.total + excluded.total;
  RETURN NULL;                                  -- return value of AFTER triggers is ignored
END;
$$;

CREATE TRIGGER accounts_totals
AFTER UPDATE ON accounts
REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows   -- every row before and after the update
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_totals();

UPDATE accounts SET balance = balance + 1;      -- one trigger call for all rows
SELECT * FROM account_totals ORDER BY owner;

Transition tables are available only for AFTER triggers on tables (not views), and an INSERT trigger has only NEW TABLE, a DELETE trigger only OLD TABLE.

Key takeaways

• A trigger is a RETURNS trigger function bound to a table, event, timing, and level by CREATE TRIGGER.
• Row-level triggers fire once per affected row and can see and change OLD/NEW; statement-level triggers fire once per statement, even for zero rows.
• Transition tables give AFTER triggers all changed rows at once, enabling set-based processing of bulk changes.
• Prefer statement-level triggers with transition tables for summaries over large changes; row-level triggers for per-row defaults and validation.

🧪 Practice

1. (Beginner) Create a statement-level AFTER DELETE trigger on transfers that prints RAISE NOTICE 'delete statement on transfers'. Run a DELETE that matches no rows and confirm it still fires.
2. (Intermediate) Implement account_totals maintenance twice: once as a row-level trigger and once with transition tables. Update 500,000 accounts and compare the run times.
3. (Interview) "When would you choose a statement-level trigger over a row-level trigger?" Hint: Think about bulk operations, the need to see individual rows, the number of function calls, and what transition tables make possible.

BEFORE, AFTER, and INSTEAD OF Triggers

The timing of a trigger decides what it can do. A trigger that fires before the row is written can still change it or veto it; a trigger that fires after the row is written sees the final result, including values filled in by defaults and other triggers, and can safely act on other tables. Views, which have no storage of their own, need a third kind of trigger that replaces the operation entirely.

What each timing can do

Timing	Level	On	Can modify the row?	Can cancel the row?	Typical use
BEFORE	Row	Tables	Yes, by changing NEW	Yes, by returning NULL	Normalization, defaults, validation
BEFORE	Statement	Tables, views	No	Only by raising an error	Permission or maintenance-window checks
AFTER	Row	Tables	No (row already written)	Only by raising an error	Changes to other tables, notifications, auditing
AFTER	Statement	Tables, views	No	Only by raising an error	Bulk summaries with transition tables
INSTEAD OF	Row	Views only	Performs the operation itself	Yes, by returning NULL	Making complex views writable

Where triggers fire in a statement

 BEFORE STATEMENT triggers
 for each row:
     BEFORE ROW triggers      (may change NEW or skip the row)
     constraint checks:       NOT NULL, CHECK, unique/primary key
     row written to the table (and its indexes)
     AFTER ROW triggers       queued
 AFTER ROW triggers           run from the queue, in row order
 AFTER STATEMENT triggers
 (deferred constraint triggers and deferred FKs run at COMMIT)

Because CHECK and NOT NULL are verified after BEFORE triggers run, a BEFORE trigger can fill a NOT NULL column or fix a value that would otherwise fail a check. AFTER ROW triggers are queued until the statement finishes, so they see the state after the entire statement, including all other rows it changed.

BEFORE: normalize and validate

CREATE TABLE customers_v2 (
  id    int  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name  text NOT NULL
);

CREATE FUNCTION normalize_customer() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));             -- the UNIQUE check sees the normalized value
  NEW.name  := initcap(trim(NEW.name));
  IF NEW.email !~ '^[^@\s]+@[^@\s]+$' THEN
    RAISE EXCEPTION 'invalid email: %', NEW.email USING ERRCODE = 'check_violation';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER customers_v2_normalize
BEFORE INSERT OR UPDATE OF email, name ON customers_v2    -- UPDATE OF: only when these columns are SET
FOR EACH ROW EXECUTE FUNCTION normalize_customer();

INSERT INTO customers_v2 (email, name) VALUES ('  Ann@Example.COM ', 'ann lee');
INSERT INTO customers_v2 (email, name) VALUES ('ann@example.com', 'Someone Else');
-- ERROR:  duplicate key value violates unique constraint "customers_v2_email_key"

A simple rule like this one could also be a CHECK constraint or a generated column (Generated Columns); prefer declarative features where they suffice and use triggers for logic they cannot express.

AFTER: keep other tables in sync. An AFTER INSERT trigger on transfers can apply the movement to both balances, so no client can record a transfer without moving money:

CREATE FUNCTION apply_transfer() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts SET balance = balance - NEW.amount WHERE id = NEW.from_id;
  UPDATE accounts SET balance = balance + NEW.amount WHERE id = NEW.to_id;
  RETURN NULL;
END;
$$;

CREATE TRIGGER transfers_apply
AFTER INSERT ON transfers
FOR EACH ROW EXECUTE FUNCTION apply_transfer();

INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 3, 5);
-- both balances change in the same transaction; a CHECK failure on accounts rolls back the insert too

INSTEAD OF: writable complex views. A view that joins tables is not automatically updatable (Updatable Views and Rules). An INSTEAD OF trigger defines what an INSERT, UPDATE, or DELETE on the view means:

CREATE VIEW transfer_details AS
SELECT t.id, f.owner AS from_owner, d.owner AS to_owner, t.amount, t.created_at
FROM transfers AS t
JOIN accounts AS f ON f.id = t.from_id
JOIN accounts AS d ON d.id = t.to_id;

CREATE FUNCTION transfer_details_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO transfers (from_id, to_id, amount)
  SELECT f.id, d.id, NEW.amount
  FROM accounts AS f, accounts AS d
  WHERE f.owner = NEW.from_owner AND d.owner = NEW.to_owner
  RETURNING id, created_at INTO NEW.id, NEW.created_at;   -- report generated values back
  IF NOT FOUND THEN
    RAISE EXCEPTION 'unknown owner in % -> %', NEW.from_owner, NEW.to_owner;
  END IF;
  RETURN NEW;                                              -- non-NULL: row counts as inserted
END;
$$;

CREATE TRIGGER transfer_details_ins
INSTEAD OF INSERT ON transfer_details
FOR EACH ROW EXECUTE FUNCTION transfer_details_insert();

INSERT INTO transfer_details (from_owner, to_owner, amount)
VALUES ('bob', 'carol', 2)
RETURNING id, created_at;

Key takeaways

• BEFORE ROW triggers can modify NEW or skip the row by returning NULL; constraints are checked after them.
• AFTER triggers see the final row and are the right place to change other tables; their return value is ignored.
• AFTER ROW triggers are queued and run after the whole statement has modified the table.
• INSTEAD OF row triggers make views writable by performing the real operation on base tables.
• Use declarative constraints and generated columns when they suffice; triggers are for logic they cannot express.

🧪 Practice

1. (Beginner) Write a BEFORE INSERT trigger on transfers that rejects transfers where from_id = to_id. Then replace it with a CHECK constraint and compare.
2. (Intermediate) Add INSTEAD OF UPDATE and INSTEAD OF DELETE triggers to transfer_details so that changing amount through the view updates transfers.
3. (Interview) "Should derived data such as an account balance be maintained by an AFTER trigger on transfers, computed in a view, or maintained by the application?" Hint: Compare read cost, write cost, lock contention on hot accounts, consistency guarantees, and how each behaves under concurrent inserts.

Trigger Functions and Special Variables

A trigger function receives no ordinary arguments. Instead, PostgreSQL sets a group of special variables that describe the event: which table, which operation, what the row looked like before and after. One well-written function can therefore serve many tables and many events, and the trigger definition decides when it runs.

Special variables in PL/pgSQL

Variable	Type	Content
NEW	record	The new row for INSERT/UPDATE in row-level triggers; NULL for DELETE and statement-level
OLD	record	The old row for UPDATE/DELETE in row-level triggers; NULL for INSERT and statement-level
TG_OP	text	INSERT, UPDATE, DELETE, or TRUNCATE
TG_WHEN	text	BEFORE, AFTER, or INSTEAD OF
TG_LEVEL	text	ROW or STATEMENT
TG_NAME	name	Name of the trigger that fired
TG_TABLE_NAME, TG_TABLE_SCHEMA	name	Table the trigger is on
TG_RELID	oid	OID of that table (TG_RELID::regclass gives a qualified name)
TG_NARGS, TG_ARGV[]	int, text[]	Arguments given in CREATE TRIGGER ... EXECUTE FUNCTION f('a', 'b') (indexed from 0)

What to return

Trigger type	Return NEW (or a modified row)	Return NULL	Return OLD
BEFORE ROW INSERT/UPDATE	Write this row	Skip this row silently	(UPDATE) keep the old values: no change
BEFORE ROW DELETE	—	Skip the delete	Proceed with the delete
INSTEAD OF ROW	Row counts as processed (feeds RETURNING)	Row counts as not processed	Same as NEW for DELETE
AFTER, any statement-level	Ignored (by convention RETURN NULL)	Ignored	Ignored

One function for many events

CREATE FUNCTION guard_balance_change() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  max_change numeric := TG_ARGV[0]::numeric;                  -- configured per trigger
BEGIN
  IF TG_OP = 'DELETE' THEN
    IF OLD.balance <> 0 THEN
      RAISE EXCEPTION '%: cannot delete account % with non-zero balance', TG_NAME, OLD.id;
    END IF;
    RETURN OLD;                                               -- allow the delete
  END IF;

  IF TG_OP = 'UPDATE' AND abs(NEW.balance - OLD.balance) > max_change THEN
    RAISE EXCEPTION '%: change of % on %.% exceeds limit %',
      TG_NAME, NEW.balance - OLD.balance, TG_TABLE_SCHEMA, TG_TABLE_NAME, max_change;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER accounts_guard
BEFORE UPDATE OR DELETE ON accounts
FOR EACH ROW
EXECUTE FUNCTION guard_balance_change('10000');              -- argument becomes TG_ARGV[0]

UPDATE accounts SET balance = balance + 50000 WHERE id = 1;
-- ERROR:  accounts_guard: change of 50000.00 on public.accounts exceeds limit 10000

Filtering with WHEN. A WHEN condition on the trigger is evaluated before the function is called, so rows that do not qualify cost almost nothing. It can reference OLD and NEW (row-level only):

DROP TRIGGER accounts_set_updated_at ON accounts;

CREATE TRIGGER accounts_set_updated_at
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)           -- skip no-op updates such as SET balance = balance
EXECUTE FUNCTION set_updated_at();

IS DISTINCT FROM treats two NULLs as equal, unlike <>, which makes it the right comparison for "did anything change."

UPDATE OF columns. BEFORE UPDATE OF balance ON accounts fires only when balance appears in the SET list, regardless of whether its value actually changed. Combine it with WHEN (OLD.balance IS DISTINCT FROM NEW.balance) to fire only on real changes.

Inspecting triggers

SELECT tgname, tgenabled, pg_get_triggerdef(oid) AS definition
FROM pg_trigger
WHERE tgrelid = 'accounts'::regclass AND NOT tgisinternal;   -- internal = FK enforcement triggers

In psql, \d accounts lists the triggers under the table definition.

Key takeaways

• Trigger functions take no ordinary parameters; NEW, OLD, and the TG_* variables describe the event.
• The return value matters only for BEFORE ROW and INSTEAD OF triggers: the row to write, or NULL to skip.
• TG_ARGV lets one function serve many triggers with different settings.
• WHEN conditions and UPDATE OF column lists avoid calling the function for rows that do not matter.

🧪 Practice

1. (Beginner) Write a trigger function that prints TG_OP, TG_WHEN, TG_LEVEL, and TG_TABLE_NAME, and attach it to transfers as both a row-level and a statement-level trigger. Run one INSERT and read the output.
2. (Intermediate) Write a BEFORE UPDATE trigger that makes the created_at column of transfers immutable by returning a row with the old value restored. Compare this with raising an error: which is better for an API, and why?
3. (Interview) "Your updated_at trigger is making a bulk UPDATE ... SET flag = flag slow and generating lots of WAL even though nothing really changes. How would you fix it?" Hint: Consider a WHEN (OLD.* IS DISTINCT FROM NEW.*) clause, the suppress_redundant_updates_trigger() built-in, and fixing the statement's WHERE.

Constraint Triggers

Some business rules involve several rows that must be consistent together. In double-entry bookkeeping, every journal entry consists of several lines whose amounts must sum to zero. That cannot be checked after each row is inserted, because the first line on its own is always unbalanced. It can only be checked once all lines are in, at the end of the transaction. A constraint trigger is an AFTER ROW trigger whose firing can be deferred until commit, just like a deferrable foreign key.

Analogy. A normal trigger is a cashier checking each item as you place it on the belt. A deferred constraint trigger is the receipt check at the exit: it waits until you have finished shopping and checks the whole basket.

Definition

CREATE CONSTRAINT TRIGGER name
AFTER INSERT OR UPDATE OR DELETE ON table
[ DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
FOR EACH ROW                                       -- always row-level
[ WHEN (condition) ]
EXECUTE FUNCTION function();

Example: balanced journal entries

CREATE TABLE journal_lines (
  journal_id bigint        NOT NULL,
  account_id int           NOT NULL REFERENCES accounts,
  amount     numeric(12,2) NOT NULL,              -- positive = debit, negative = credit
  PRIMARY KEY (journal_id, account_id)
);

CREATE FUNCTION check_journal_balanced() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_journal bigint := coalesce(NEW.journal_id, OLD.journal_id);
  v_sum     numeric;
BEGIN
  SELECT coalesce(sum(amount), 0) INTO v_sum
  FROM journal_lines WHERE journal_id = v_journal;

  IF v_sum <> 0 THEN
    RAISE EXCEPTION 'journal % is unbalanced by %', v_journal, v_sum
      USING ERRCODE = 'integrity_constraint_violation';
  END IF;
  RETURN NULL;
END;
$$;

CREATE CONSTRAINT TRIGGER journal_balanced
AFTER INSERT OR UPDATE OR DELETE ON journal_lines
DEFERRABLE INITIALLY DEFERRED                       -- check at COMMIT, not after each row
FOR EACH ROW EXECUTE FUNCTION check_journal_balanced();

BEGIN;
INSERT INTO journal_lines VALUES (1, 1, -25.00);    -- unbalanced so far: allowed
INSERT INTO journal_lines VALUES (1, 2,  25.00);    -- now balanced
COMMIT;                                             -- trigger runs here, finds sum = 0: OK

BEGIN;
INSERT INTO journal_lines VALUES (2, 1, -10.00);
COMMIT;
-- ERROR:  journal 2 is unbalanced by -10.00        -- the whole transaction is rolled back

Controlling timing in a transaction. SET CONSTRAINTS journal_balanced IMMEDIATE (or SET CONSTRAINTS ALL IMMEDIATE) runs pending checks right away, which lets an application see the error before doing more work. A constraint declared DEFERRABLE INITIALLY IMMEDIATE checks after each statement by default but can be deferred per transaction with SET CONSTRAINTS ... DEFERRED.

Not a real constraint under concurrency. The trigger checks the data visible to its own transaction. Two concurrent transactions can each pass the check and together produce a state that violates the rule, especially for rules that span rows other transactions may write (for example, "an account may have at most three open cards"). To make such checks reliable:

Approach	How
Serialize by key	Lock a parent row (SELECT ... FOR UPDATE on the journal header) or take pg_advisory_xact_lock(journal_id) before checking
SERIALIZABLE isolation	Concurrent conflicting transactions are detected and one fails with 40001 (Serializable)
Declarative alternative	Use an exclusion constraint, unique index, or check constraint when the rule fits one

Cost. A deferred row-level trigger runs once per changed row at commit; a journal with 50 lines runs the same sum 50 times. For large batches, add a WHEN clause, or record affected keys in a temporary table and check each key once.

Key takeaways

• Constraint triggers are AFTER ROW triggers that can be DEFERRABLE and fire at commit time.
• They enforce multi-row rules that are temporarily violated during a transaction, such as balanced journal entries.
• SET CONSTRAINTS ... IMMEDIATE/DEFERRED controls when pending checks run.
• They do not see uncommitted work of other transactions; add locking or use SERIALIZABLE for rules that span concurrently written rows.

🧪 Practice

1. (Beginner) Insert a balanced three-line journal entry in one transaction, then try an unbalanced one, and observe when the error appears. Repeat with SET CONSTRAINTS ALL IMMEDIATE.
2. (Intermediate) Write a constraint trigger enforcing "an owner may have at most two accounts." In two concurrent sessions, insert a third account for the same owner in each and commit both. Show the violation, then fix it with an advisory lock on the owner.
3. (Interview) "Why can't a CHECK constraint enforce that the lines of a journal sum to zero, and what are the options?" Hint: CHECK sees only one row; consider constraint triggers, storing lines as an array or JSON in one row, and enforcing it in a single function that writes all lines.

Event Triggers for DDL

Ordinary triggers react to changes in data. Sometimes you need to react to changes in the schema: record every ALTER TABLE for an audit, block DROP TABLE in production, or warn when a migration rewrites a large table. Event triggers fire on DDL commands for the whole database, not on a specific table.

Events

Event	Fires	Useful information
ddl_command_start	Before a DDL command executes	TG_TAG (for example DROP TABLE); can block the command by raising an error
ddl_command_end	After a DDL command, before commit	pg_event_trigger_ddl_commands(): objects created or altered
sql_drop	After objects are dropped	pg_event_trigger_dropped_objects(): every object dropped, including by CASCADE
table_rewrite	Before ALTER TABLE rewrites a table	pg_event_trigger_table_rewrite_oid() and the rewrite reason
login (PostgreSQL 17+)	When a user connects	Session setup, connection policies

Event triggers are created by superusers only, and their functions declare RETURNS event_trigger. They do not fire for commands on shared objects (databases, roles, tablespaces) or on event triggers themselves.

Logging all DDL

CREATE TABLE ddl_log (
  id          bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  at          timestamptz NOT NULL DEFAULT now(),
  username    text        NOT NULL DEFAULT current_user,
  command_tag text        NOT NULL,
  object_type text,
  object_name text,
  query       text
);

CREATE FUNCTION log_ddl() RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
  cmd record;
BEGIN
  FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    INSERT INTO public.ddl_log (command_tag, object_type, object_name, query)   -- schema-qualified, see below
    VALUES (cmd.command_tag, cmd.object_type, cmd.object_identity, current_query());
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER log_ddl_end ON ddl_command_end EXECUTE FUNCTION log_ddl();

CREATE INDEX transfers_to_idx ON transfers (to_id);
SELECT command_tag, object_type, object_name FROM ddl_log;
--  command_tag  | object_type |       object_name
-- --------------+-------------+-------------------------
--  CREATE INDEX | index       | public.transfers_to_idx

Event trigger functions run with whatever search_path the DDL command runs with. CREATE EXTENSION, for instance, runs its script with the search path set to the extension's schema, so an unqualified ddl_log would not be found and the error would make every CREATE EXTENSION fail. Always schema-qualify tables in event trigger functions.

Blocking dangerous commands

CREATE FUNCTION forbid_drop_table() RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF current_setting('app.allow_drop', true) IS DISTINCT FROM 'on'
     AND EXISTS (SELECT 1 FROM pg_event_trigger_dropped_objects()
                 WHERE object_type IN ('table', 'schema')
                   AND NOT is_temporary)                  -- ignore temporary tables
  THEN
    RAISE EXCEPTION '% is disabled; SET app.allow_drop = on to proceed', TG_TAG;
  END IF;
END;
$$;

CREATE EVENT TRIGGER no_drop_table
ON sql_drop                                         -- fires after the drop, before commit
WHEN TAG IN ('DROP TABLE', 'DROP SCHEMA')           -- filter by command tag
EXECUTE FUNCTION forbid_drop_table();

DROP TABLE ddl_log;
-- ERROR:  DROP TABLE is disabled; SET app.allow_drop = on to proceed    -- the drop is rolled back

Raising an error in sql_drop rolls back the whole command, so nothing is lost. A simpler ddl_command_start trigger that rejects every DROP TABLE tag looks tempting, but event triggers also see DDL that PostgreSQL issues internally: REFRESH MATERIALIZED VIEW CONCURRENTLY (9.4), for example, drops a temporary table when it finishes and would be blocked too. Checking is_temporary in sql_drop avoids that.

Watching for table rewrites. Some ALTER TABLE forms (changing a column type, adding a column with a volatile default) rewrite the whole table under an ACCESS EXCLUSIVE lock. A table_rewrite trigger can refuse them on large tables during business hours (Lock-Free Migration Patterns):

CREATE FUNCTION no_big_rewrites() RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
  tbl regclass := pg_event_trigger_table_rewrite_oid();
BEGIN
  IF pg_total_relation_size(tbl) > 1e9 THEN          -- larger than about 1 GB
    RAISE EXCEPTION 'refusing to rewrite % (%), run it in a maintenance window',
      tbl, pg_size_pretty(pg_total_relation_size(tbl));
  END IF;
END;
$$;

CREATE EVENT TRIGGER guard_rewrites ON table_rewrite EXECUTE FUNCTION no_big_rewrites();

Recovering from a broken event trigger. An event trigger that always fails can block all DDL; a broken login trigger can block all connections. Disable it with ALTER EVENT TRIGGER name DISABLE from a superuser session. If that is impossible, set event_triggers = off (PostgreSQL 17+) in the connection options of a superuser, or start the server in single-user mode, where event triggers do not fire.

Key takeaways

• Event triggers fire on DDL events database-wide: ddl_command_start, ddl_command_end, sql_drop, table_rewrite, and login (17+).
• Helper functions such as pg_event_trigger_ddl_commands() and pg_event_trigger_dropped_objects() describe what changed.
• They can audit schema changes, block dangerous commands, and stop accidental table rewrites.
• Only superusers can create them; keep them simple, because a failing event trigger blocks DDL (or logins) for everyone.

🧪 Practice

1. (Beginner) Create the ddl_log event trigger and run CREATE TABLE, ALTER TABLE ... ADD COLUMN, and CREATE INDEX. Inspect what was logged.
2. (Intermediate) Write an sql_drop event trigger that logs every dropped object, then run DROP SCHEMA ... CASCADE on a schema with tables, indexes, and a view. Explain why sql_drop sees objects that ddl_command_start does not.
3. (Interview) "How would you prevent developers from running migrations that lock large tables for minutes?" Hint: Combine a table_rewrite event trigger, lock_timeout in migration sessions, review tooling, and knowledge of which ALTER TABLE forms rewrite or scan the table.

Audit Logging with Triggers

Regulators, security teams, and support staff regularly ask "who changed this balance, when, and what was it before?" Application logs answer only for changes made through the application. A trigger-based audit log records every change in the database itself, in the same transaction as the change, so a change cannot be committed without its audit record (and an aborted change leaves no record).

What to capture

Question	Source
What table and operation	TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP
Which row, before and after	to_jsonb(OLD), to_jsonb(NEW)
Who	current_user, session_user, an application-set user (current_setting('app.user_id', true))
From where	inet_client_addr(), current_setting('application_name')
When	now() (transaction start, groups a transaction's changes) or clock_timestamp() (actual time)
Which transaction	pg_current_xact_id() (groups all changes of one transaction)

A generic audit trigger

CREATE SCHEMA audit;

CREATE TABLE audit.log (
  id          bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  changed_at  timestamptz NOT NULL DEFAULT now(),
  xact_id     xid8        NOT NULL DEFAULT pg_current_xact_id(),
  db_user     text        NOT NULL DEFAULT session_user,
  app_user    text        DEFAULT current_setting('app.user_id', true),   -- set by the application
  table_name  text        NOT NULL,
  operation   text        NOT NULL,
  row_key     jsonb,
  old_row     jsonb,
  new_row     jsonb,
  changed     jsonb                                                         -- only the modified columns
);

CREATE FUNCTION audit.log_change() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER                                  -- writers need no privileges on audit.log
SET search_path = pg_catalog, pg_temp
AS $$
DECLARE
  v_old jsonb := CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END;
  v_new jsonb := CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END;
  v_changed jsonb;
BEGIN
  IF TG_OP = 'UPDATE' THEN
    SELECT jsonb_object_agg(n.key, n.value) INTO v_changed
    FROM jsonb_each(v_new) AS n
    WHERE n.value IS DISTINCT FROM v_old -> n.key;          -- keep only columns whose value changed
    IF v_changed IS NULL THEN
      RETURN NULL;                                          -- no-op update: nothing to log
    END IF;
  END IF;

  INSERT INTO audit.log (table_name, operation, row_key, old_row, new_row, changed)
  VALUES (TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, TG_OP,
          jsonb_build_object('id', coalesce(v_new, v_old) -> 'id'),
          v_old, v_new, v_changed);
  RETURN NULL;
END;
$$;

REVOKE ALL ON SCHEMA audit FROM PUBLIC;           -- ordinary users cannot read or tamper with the log

CREATE TRIGGER accounts_audit
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION audit.log_change();

SET app.user_id = 'support-agent-17';             -- the application sets this per request/transaction
UPDATE accounts SET balance = balance + 5 WHERE id = 2;

SELECT changed_at::time(0), db_user, app_user, operation, changed
FROM audit.log ORDER BY id DESC LIMIT 1;
--  changed_at | db_user  |     app_user     | operation |                               changed
-- ------------+----------+------------------+-----------+----------------------------------------------------------------------
--  10:42:07   | postgres | support-agent-17 | UPDATE    | {"balance": 64.00, "updated_at": "2026-10-06T10:42:07.394947+00:00"}

Statement-level variant for bulk changes. A row-level audit trigger doubles the number of rows written by every statement. For tables with large batch updates, a statement-level AFTER trigger with transition tables writes all audit rows with one INSERT ... SELECT from old_rows/new_rows, which is considerably cheaper.

Operating an audit log

Concern	Recommendation
Growth	Partition audit.log by month and detach/archive old partitions (Partition Maintenance Strategies)
Tampering	Owner-only access; the trigger function is SECURITY DEFINER; consider shipping rows to external storage
Sensitive columns	Exclude or hash them before logging (v_new - 'ssn')
Who is the user	With a shared database role, the application must set app.user_id (use SET LOCAL with transaction pooling)
Bypass	Triggers do not fire for TRUNCATE (unless a TRUNCATE trigger exists) or with session_replication_role = replica; superusers can disable triggers
Querying	Index (table_name, (row_key ->> 'id')) and changed_at

Alternatives. pgaudit logs statements to the server log, including reads, which triggers cannot see (Auditing with pgaudit). Logical decoding streams every committed row change to an external system without adding write work to the transaction (Change Data Capture Pipelines). Trigger-based auditing remains the simplest way to get a queryable, transactional row history inside the database.

Key takeaways

• Trigger-based audit logs record changes in the same transaction, so every committed change has a record.
• to_jsonb(OLD) and to_jsonb(NEW) make one generic function work for any table; storing only changed columns saves space.
• Capture the application user via a custom setting, and the transaction with pg_current_xact_id().
• Protect the log with a separate schema and a SECURITY DEFINER function; partition it to control growth.
• Know the bypasses (TRUNCATE, replica mode, superusers) and the alternatives (pgaudit, logical decoding).

🧪 Practice

1. (Beginner) Attach audit.log_change() to transfers, insert, update, and delete a row, and query the log.
2. (Intermediate) Write a function audit.row_history(tbl text, id int) that reconstructs every version of a row from the log in chronological order.
3. (Interview) "Design an audit trail for a payments table with 5,000 writes per second, a 7-year retention requirement, and a rule that DBAs must not be able to alter history unnoticed." Hint: Think about write amplification, partitioning, shipping to append-only external storage, hash chaining of records, and whether triggers or logical decoding fit better.

Trigger Pitfalls and Ordering

Triggers run invisibly: the person writing UPDATE accounts ... may have no idea that five functions will run, each possibly changing other tables that have triggers of their own. That invisibility is both the strength and the main danger of triggers. This topic collects the rules and failure modes that cause most trigger-related surprises.

Order of multiple triggers. Triggers with the same timing, event, and level fire in alphabetical order of their names. BEFORE ROW triggers form a chain: each receives the NEW returned by the previous one, and if any returns NULL, the rest are skipped and the row is not written.

 BEFORE UPDATE ROW triggers on accounts:
   a10_normalize  -> NEW modified
   a20_validate   -> sees modified NEW; RAISE or RETURN NEW
   b10_set_updated_at

Name triggers with a prefix that encodes the intended order instead of relying on creation order.

Recursion. A trigger that modifies its own table fires itself again:

CREATE FUNCTION bad_touch() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET updated_at = now() WHERE id = NEW.id;   -- fires the AFTER trigger again
  RETURN NULL;
END $$;
-- result: ERROR:  stack depth limit exceeded

Fixes, best first: change NEW in a BEFORE trigger instead of issuing an UPDATE; restrict the trigger with WHEN or UPDATE OF so the inner update does not match; as a last resort, check pg_trigger_depth() (the current nesting level) and exit early.

Events that do or do not fire triggers

Operation	Fires row-level DML triggers?
INSERT, UPDATE, DELETE, MERGE	Yes
INSERT ... ON CONFLICT DO UPDATE	BEFORE INSERT for every row; then either INSERT or UPDATE triggers
COPY FROM	Yes (bulk loads become slower)
TRUNCATE	No; only TRUNCATE triggers (statement-level)
Foreign key ON DELETE CASCADE / SET NULL	Yes, on the child table
Logical replication apply on a subscriber	No, unless the trigger is ENABLE REPLICA or ENABLE ALWAYS
session_replication_role = replica	No (ordinary triggers are skipped; used by some restore and sync tools)
ALTER TABLE ... DISABLE TRIGGER	No

ALTER TABLE t DISABLE TRIGGER ALL also disables the internal triggers that enforce foreign keys; data loaded while they are off is never checked. Prefer DISABLE TRIGGER USER.

Partitioned tables. A trigger created on a partitioned table is cloned to every existing and future partition. BEFORE ROW triggers on partitioned tables (PostgreSQL 13+) may not change the partition key so that the row would belong in a different partition.

Performance traps

• Row-level triggers turn every bulk operation into a per-row function call; an AFTER row trigger also queues every row in memory until the end of the statement.
• A trigger that updates a summary row (for example, a per-owner total) serializes all concurrent writers on that row. Under load, that row becomes a lock hot spot (Row-Level Locks).
• Chains of triggers that write to other tables with their own triggers multiply the work of a single statement.

Visibility. Inside an AFTER ROW trigger, queries see the effects of the whole triggering statement. Inside a BEFORE ROW trigger, they see rows processed earlier in the statement but not the current one, so a BEFORE trigger that counts rows in its own table gives results that depend on row processing order. Do not depend on that.

Managing triggers safely

CREATE OR REPLACE TRIGGER accounts_audit              -- PostgreSQL 14+: replace in place
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION audit.log_change();

ALTER TABLE accounts DISABLE TRIGGER accounts_audit;  -- takes a lock that blocks writes; use briefly
ALTER TABLE accounts ENABLE TRIGGER accounts_audit;

SELECT tgname, tgenabled FROM pg_trigger              -- O = enabled, D = disabled,
WHERE tgrelid = 'accounts'::regclass                  -- R = replica only, A = always
  AND NOT tgisinternal ORDER BY tgname;               -- ordered as they fire

Guidelines

Do	Avoid
Keep trigger functions short and fast	Network calls, long computations, or waiting on locks inside triggers
Document triggers next to the table (COMMENT ON TRIGGER)	Business workflows spread across chains of triggers
Use declarative constraints, generated columns, and defaults first	Triggers that duplicate what a constraint would do
Name triggers to encode their order	Relying on creation order
Test bulk operations with triggers enabled	Assuming TRUNCATE or replica mode respects your triggers

Key takeaways

• Triggers on the same event fire alphabetically by name; BEFORE ROW triggers chain their NEW values.
• A trigger that writes to its own table recurses; modify NEW in a BEFORE trigger, or limit firing with WHEN.
• TRUNCATE, replica mode, logical replication apply, and disabled triggers skip ordinary triggers; COPY and FK cascades fire them.
• Row-level triggers multiply the cost of bulk operations and can create lock hot spots on summary rows.
• Keep triggers small, documented, and ordered by name; prefer declarative features when they suffice.

🧪 Practice

1. (Beginner) Create three BEFORE INSERT triggers on a test table named t_c, t_a, and t_b, each appending its name to a text column. Insert a row and confirm the firing order.
2. (Intermediate) Reproduce the recursion error with bad_touch, then fix it in two different ways: once with a BEFORE trigger and once with pg_trigger_depth(). Explain why the first fix is better.
3. (Interview) "After a data migration, an audit team finds that thousands of changes are missing from the trigger-based audit log. The migration ran without errors. What could explain this?" Hint: List the ways data can change without firing ordinary triggers: TRUNCATE plus reload, session_replication_role, disabled triggers, restores with pg_restore --disable-triggers, and logical replication.

<a id="94-views-and-derived-objects"></a>

9.4 Views and Derived Objects

This subchapter covers objects whose content is derived from other data: views (saved queries), updatable views, materialized views (saved results), sequences and identity columns (generated keys), and generated columns (values computed from other columns of the same row).

Creating and Using Views

The same joins and filters tend to appear in many queries: "active accounts with their owner's name," "transfers with both parties resolved." Copying the query everywhere spreads the logic, and changing the underlying tables then means hunting down every copy. A view is a named, stored query that can be used like a table. It stores no data; every query against the view runs the stored query against the current base tables.

Analogy. A view is a window onto a room: it shows a particular angle of what is inside, always live, but the window itself contains nothing.

Creating and querying

CREATE VIEW account_overview AS
SELECT a.id,
       a.owner,
       a.balance,
       count(t.id)                AS transfers_sent,
       coalesce(sum(t.amount), 0) AS total_sent
FROM accounts AS a
LEFT JOIN transfers AS t ON t.from_id = a.id
GROUP BY a.id;                                    -- a.id is the primary key, so other a.* columns are allowed

SELECT owner, balance, transfers_sent FROM account_overview WHERE total_sent > 20;

A view adds no run-time cost by itself: the rewriter substitutes its query, and the planner optimizes the combined query as if you had written it inline (Rewrite System and Rules).

What views are good for

Purpose	Example
Simplification	Hide a five-table join behind one name
Stable interface	Applications query customer_v; the tables underneath can be refactored
Security	Grant SELECT on a view that exposes some columns or rows, not on the base table
Consistency	One definition of "active customer" for all reports

How a view is stored. The query is parsed and stored as a query tree when the view is created, with references to columns by internal number, not by name. This has consequences:

• SELECT * in a view is expanded at creation time. Columns added to the base table later do not appear in the view.
• Renaming a base column does not break the view; it keeps working with the new name.
• Dropping or changing the type of a column the view uses fails until the view is dropped (or use DROP ... CASCADE, which drops the view too).

ALTER TABLE accounts ALTER COLUMN owner TYPE varchar(100);
-- ERROR:  cannot alter type of a column used by a view or rule
-- DETAIL:  rule _RETURN on view transfer_details depends on column "owner"    -- the first dependent view found

Changing a view. CREATE OR REPLACE VIEW may only add new columns at the end; it cannot remove, rename, reorder, or change the type of existing columns. For anything else, drop and recreate the view in one transaction, together with its dependent views and grants:

BEGIN;
DROP VIEW account_overview;
CREATE VIEW account_overview AS SELECT ...;       -- new definition (elided)
GRANT SELECT ON account_overview TO reporting;
COMMIT;                                           -- other sessions never see the view missing

View options

Option	Effect
security_invoker = true (PostgreSQL 15+)	Base-table privileges and row-level security are checked as the querying user, not the view owner
security_barrier = true	User-supplied conditions cannot run before the view's own filters, so they cannot leak hidden rows (Security Barriers in Views)
check_option = local / cascaded	For updatable views, see Updatable Views and Rules

By default, a view accesses its base tables with the privileges of the view owner. That is what lets a view expose part of a table that the caller cannot read directly, but it can also bypass row-level security unintentionally. Use WITH (security_invoker = true) for views that are meant as convenience, not as a security boundary.

Inspecting views

SELECT pg_get_viewdef('account_overview'::regclass, true);   -- the stored definition, pretty-printed

SELECT DISTINCT dependent.relname AS view_name                -- views that depend on accounts
FROM pg_depend AS d
JOIN pg_rewrite AS r        ON r.oid = d.objid
JOIN pg_class   AS dependent ON dependent.oid = r.ev_class
WHERE d.refobjid = 'accounts'::regclass AND dependent.relname <> 'accounts';

Pitfalls. Views stacked on views are easy to write and hard to tune: a filter in the outermost query may not be pushed through aggregates, DISTINCT, or window functions in inner views, and a view that joins five tables still joins them when you only need one column (unless the planner can remove an unneeded LEFT JOIN because of a unique key). Check EXPLAIN of queries over deep view stacks.

Key takeaways

• A view is a stored query; querying it runs that query against current data, with no extra cost after rewriting.
• Views simplify queries, provide a stable interface, and can expose a restricted subset of data.
• SELECT * is frozen at creation; base columns used by a view cannot be dropped or retyped without dropping the view.
• CREATE OR REPLACE VIEW can only append columns; otherwise drop and recreate in one transaction.
• Views run with the owner's privileges unless security_invoker = true.

🧪 Practice

1. (Beginner) Create a view recent_transfers showing transfers from the last 7 days with both owners' names. Query it with an extra filter and inspect the plan.
2. (Intermediate) Create a view with SELECT * from accounts, then add a column to accounts. Show the view does not include it, and fix it. Then try to change the type of a column the view uses, and write the transaction that performs the change safely.
3. (Interview) "A team wants to rename and split a heavily used table without changing all applications at once. How can views help?" Hint: Consider creating the new tables, replacing the old name with an (updatable) view over them, and migrating clients gradually; think about writes through the view.

Updatable Views and Rules

If applications read through a view, they often also need to write through it: an API layer that only exposes customer_v, or a renamed table kept available under its old name. PostgreSQL makes simple views automatically updatable: an INSERT, UPDATE, or DELETE on the view is translated into the same command on the base table. More complex views need an INSTEAD OF trigger or, in legacy code, a rule.

When a view is automatically updatable

Requirement	Reason
Exactly one entry in FROM, which is a table or another updatable view	Each view row must map to exactly one base row
No DISTINCT, GROUP BY, HAVING, aggregates, or window functions	Rows would be combined, so a write has no single target
No LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, or top-level WITH	Same reason
Columns that are expressions are read-only	UPDATE may set only the plain columns

CREATE VIEW low_balance_accounts AS
SELECT id, owner, balance, upper(owner) AS owner_upper   -- owner_upper is read-only
FROM accounts
WHERE balance < 50;

UPDATE low_balance_accounts SET balance = balance + 1 WHERE owner = 'carol';   -- updates accounts
UPDATE low_balance_accounts SET owner_upper = 'X';
-- ERROR:  cannot update column "owner_upper" of view "low_balance_accounts"
-- DETAIL:  View columns that are not columns of their base relation are not updatable.

SELECT table_name, is_updatable, is_insertable_into
FROM information_schema.views WHERE table_name = 'low_balance_accounts';

The disappearing-row problem: WITH CHECK OPTION. By default, you can write rows through a view that the view itself will not show:

BEGIN;
UPDATE low_balance_accounts SET balance = 500 WHERE owner = 'carol';
-- UPDATE 1, and carol vanishes from the view: she no longer matches balance < 50
SELECT owner FROM low_balance_accounts;           -- (0 rows)
ROLLBACK;

That is surprising, and for a view used as a security boundary it is dangerous (a user restricted to "their" rows could move a row out of their scope). WITH CHECK OPTION rejects writes that would produce rows invisible through the view:

CREATE OR REPLACE VIEW low_balance_accounts AS
SELECT id, owner, balance, upper(owner) AS owner_upper
FROM accounts
WHERE balance < 50
WITH CHECK OPTION;

UPDATE low_balance_accounts SET balance = 500 WHERE owner = 'carol';
-- ERROR:  new row violates check option for view "low_balance_accounts"

Option	Checks
WITH LOCAL CHECK OPTION	Only this view's WHERE condition
WITH CASCADED CHECK OPTION (default when WITH CHECK OPTION is given)	This view's condition and those of all underlying views

Defaults for inserts through views. For an automatically updatable view, columns omitted from an INSERT get the base table's defaults. A view column can also have its own default (ALTER VIEW v ALTER COLUMN c SET DEFAULT ...), which takes precedence over the base table's. Views made writable with INSTEAD OF triggers do not see base-table defaults at all: omitted columns arrive in NEW as NULL unless the view column has its own default.

Beyond automatic updatability

Mechanism	Use
INSTEAD OF trigger	Joins, aggregates, or any custom mapping; row-level, easy to reason about (BEFORE, AFTER, and INSTEAD OF Triggers)
Rule (CREATE RULE ... DO INSTEAD)	Legacy; rewrites the query instead of handling rows

For completeness, this is what a rule on a view looks like:

CREATE VIEW owners AS SELECT DISTINCT owner FROM accounts;   -- DISTINCT: not auto-updatable

CREATE RULE owners_rename AS ON UPDATE TO owners
DO INSTEAD
  UPDATE accounts SET owner = NEW.owner WHERE owner = OLD.owner;

BEGIN;
UPDATE owners SET owner = 'robert' WHERE owner = 'bob';      -- rewritten into an UPDATE on accounts
SELECT id, owner FROM accounts WHERE id = 2;                 -- robert
ROLLBACK;

Rules operate on the whole query tree, are skipped by COPY, and behave surprisingly with volatile functions; the documentation recommends triggers for new code (Rewrite System and Rules).

Key takeaways

• Single-table views without aggregation, DISTINCT, set operations, or LIMIT are automatically updatable; computed columns are read-only.
• Without WITH CHECK OPTION, writes through a view can create or move rows outside the view's filter.
• CASCADED checks all underlying views' conditions; LOCAL checks only this view's.
• Use INSTEAD OF triggers for complex views; rules are a legacy mechanism.

🧪 Practice

1. (Beginner) Create an updatable view alice_accounts (WHERE owner = 'alice'), insert a row through it for owner = 'bob', and observe the result. Add WITH CHECK OPTION and try again.
2. (Intermediate) Build view v2 on top of v1 with an additional filter. Show a write that passes WITH LOCAL CHECK OPTION on v2 but fails with CASCADED.
3. (Interview) "A multi-tenant application restricts each tenant to a view filtered by tenant_id = current_setting('app.tenant')::int. What can go wrong with writes, and what would you use instead?" Hint: Think about WITH CHECK OPTION, security_barrier, security_invoker, and row-level security policies with WITH CHECK (Multi-Tenant Isolation with RLS).

Materialized Views

A regular view recomputes its query on every access. That is perfect when the query is cheap, but a dashboard that aggregates two years of payments on every page load wastes the same seconds over and over to produce numbers that change slowly. A materialized view runs the query once and stores the result as a real table. Reads are fast and cheap; the price is that the data is a snapshot that must be refreshed explicitly.

Analogy. A view is a live video feed; a materialized view is a photograph. The photo loads instantly but shows the scene as it was when it was taken.

Views, materialized views, and summary tables

	View	Materialized view	Summary table maintained by triggers
Stores data	No	Yes	Yes
Freshness	Always current	As of the last refresh	Current (updated in each write transaction)
Read cost	Full query every time	Scan of stored result, can be indexed	Scan of stored result
Write overhead	None	None; cost is paid at refresh	Every write pays to maintain it
Effort	Minimal	Minimal plus a refresh schedule	Trigger code, concurrency care

Example: daily payment statistics

CREATE TABLE card_payments (
  id         bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id int           NOT NULL,
  amount     numeric(12,2) NOT NULL,
  paid_at    timestamptz   NOT NULL
);

INSERT INTO card_payments (account_id, amount, paid_at)
SELECT 1 + (random() * 999)::int,
       round((random() * 200)::numeric, 2),
       now() - random() * interval '730 days'
FROM generate_series(1, 2000000);
ANALYZE card_payments;

CREATE MATERIALIZED VIEW daily_payments AS
SELECT (paid_at AT TIME ZONE 'UTC')::date AS day,
       count(*)    AS payments,
       sum(amount) AS total,
       avg(amount) AS average
FROM card_payments
GROUP BY 1;

CREATE UNIQUE INDEX daily_payments_day_idx ON daily_payments (day);   -- fast lookups, and needed for CONCURRENTLY

SELECT * FROM daily_payments WHERE day = current_date - 1;
-- Time: 0.3 ms, versus about 190 ms for the same aggregate computed from card_payments

Refreshing

REFRESH MATERIALIZED VIEW daily_payments;      -- reruns the query and replaces the contents

A plain refresh takes an ACCESS EXCLUSIVE lock on the materialized view: while it runs, every query against daily_payments waits. For a refresh that takes minutes, that means a dashboard that hangs for minutes. The next topic covers the concurrent alternative.

Creating without data. CREATE MATERIALIZED VIEW ... WITH NO DATA defines the view without running the query, which is useful in migrations. Querying it before the first refresh fails with "materialized view has not been populated." pg_matviews.ispopulated shows the state.

What you can and cannot do

Allowed	Not allowed
Indexes of any kind	INSERT, UPDATE, DELETE
ANALYZE, VACUUM, CLUSTER	Primary keys, foreign keys, and constraints
ALTER MATERIALIZED VIEW ... RENAME, SET SCHEMA, ownership	Changing the defining query (drop and recreate instead)
Granting SELECT	Automatic refresh on base-table changes (not built in)

Refreshing requires ownership of the materialized view, or, from PostgreSQL 17, the MAINTAIN privilege, which can be granted to a scheduler role without handing over ownership.

Tracking freshness. PostgreSQL does not record when a materialized view was last refreshed. If readers need to know how stale the data is, include it in the view (SELECT now() AS refreshed_at, ... in a one-row companion view) or log refreshes in a table.

Key takeaways

• A materialized view stores the result of its query; reads are fast and indexable, but the data is only as fresh as the last refresh.
• REFRESH MATERIALIZED VIEW recomputes everything and, without CONCURRENTLY, blocks all readers while it runs.
• Materialized views cannot be written to or constrained, but can be indexed, analyzed, and granted.
• Choose between a view, a materialized view, and a trigger-maintained summary table by trading freshness against read and write cost.

🧪 Practice

1. (Beginner) Create daily_payments, time a query for one day against the materialized view and against the equivalent aggregate on card_payments.
2. (Intermediate) Start REFRESH MATERIALIZED VIEW daily_payments in one session (after inserting another 5 million rows to make it slow) and query the view from a second session. Observe the wait in pg_stat_activity and pg_locks.
3. (Interview) "Product wants a dashboard of per-merchant revenue, updated 'in real time,' over a table with billions of rows. Discuss views, materialized views, and summary tables." Hint: Define what freshness is really needed, consider incremental aggregation by time bucket, and think about the write amplification and hot rows of trigger-maintained totals.

Refreshing Materialized Views Concurrently

A plain REFRESH MATERIALIZED VIEW blocks every reader until it finishes. REFRESH MATERIALIZED VIEW CONCURRENTLY lets readers continue to see the old contents while the new result is computed, then applies only the differences. It is the default choice for materialized views that are read in production, with some requirements and costs.

How it works

 1. run the view's query into a temporary table           (readers still see the old data)
 2. compare new and old contents with a full outer join on all columns
 3. delete rows that disappeared, insert rows that are new
    (a changed row = delete old version + insert new version)
 4. commit                                                (readers see the new data from now on)

Because the result is applied as ordinary deletes and inserts, readers keep using the old rows through MVCC until the refresh commits. The command takes an EXCLUSIVE lock, which allows SELECT but blocks a second concurrent refresh.

Requirements

Requirement	Why
At least one UNIQUE index on the materialized view, using plain columns only (no expressions, no WHERE clause)	Identifies which old row corresponds to which new row
The view must already be populated	There is nothing to compare against after WITH NO DATA

Unlike CREATE INDEX CONCURRENTLY, a concurrent refresh may run inside a transaction block.

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_payments;   -- works: daily_payments_day_idx is unique

CREATE MATERIALIZED VIEW top_accounts AS
SELECT account_id, sum(amount) AS total FROM card_payments GROUP BY account_id;
REFRESH MATERIALIZED VIEW CONCURRENTLY top_accounts;
-- ERROR:  cannot refresh materialized view "public.top_accounts" concurrently
-- HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

Plain vs. concurrent

	REFRESH	REFRESH ... CONCURRENTLY
Readers during refresh	Blocked	Continue, see old data
Lock	ACCESS EXCLUSIVE	EXCLUSIVE
Work done	Rebuild the result into a new file and swap it in	Rebuild the result, diff it against the old, apply changes
Speed	Faster	Slower, especially when most rows change
Bloat	None (fresh storage)	Dead tuples from deleted/updated rows; needs vacuum
Unique index required	No	Yes
Usable after WITH NO DATA	Yes (first population)	No

On the 2 million-row example, with a few thousand result rows, the difference is small:

 REFRESH MATERIALIZED VIEW daily_payments;                 ~ 760 ms   (readers blocked the whole time)
 REFRESH MATERIALIZED VIEW CONCURRENTLY daily_payments;    ~ 750 ms   (readers never blocked)

Almost all of the time goes into rerunning the aggregate over 2 million rows; the diff over 731 result rows is negligible.

For a materialized view with millions of rows of which most change, the diff step can make a concurrent refresh several times slower than a plain one.

Scheduling refreshes. PostgreSQL has no built-in automatic refresh. Common options:

-- with pg_cron (see Job Scheduling with pg_cron)
SELECT cron.schedule('refresh-daily-payments', '*/15 * * * *',
                     'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_payments');

Other options are an external scheduler (cron, Kubernetes CronJob, Airflow) or a refresh triggered by the loading job right after new data arrives. Because a second concurrent refresh waits for the first, overlapping schedules queue up rather than run in parallel; choose an interval longer than the refresh time.

Beyond full refreshes. Every refresh recomputes the full query, even if only yesterday's rows changed. If that becomes too expensive:

Approach	Idea
Partition the work	Keep historical days in a regular table (computed once) and only today in a small materialized view; combine with UNION ALL in a view
Incremental summary table	INSERT ... ON CONFLICT DO UPDATE aggregates for new rows only, run periodically from a high-water mark
pg_ivm extension	Incrementally maintained materialized views, updated by triggers on base tables
Continuous aggregates (TimescaleDB)	Automatic incremental refresh of time-bucketed aggregates (TimescaleDB for Time Series)

Key takeaways

• REFRESH ... CONCURRENTLY computes the new result, diffs it against the old, and applies the changes while readers keep reading.
• It requires a populated view and a unique, non-partial, column-only index.
• It is slower than a plain refresh and creates dead tuples, so vacuum must keep up.
• Schedule refreshes with pg_cron or an external scheduler; for very large views, switch to incremental approaches.

🧪 Practice

1. (Beginner) Add a unique index to top_accounts and refresh it concurrently. Then check n_dead_tup for it in pg_stat_user_tables.
2. (Intermediate) With a materialized view of 1 million rows, measure plain and concurrent refresh times when 1 %, 50 %, and 100 % of the rows change. Explain the trend.
3. (Interview) "A concurrent refresh of a materialized view takes 40 minutes and is scheduled every 30 minutes. What happens, and how would you redesign it?" Hint: Consider lock queuing between refreshes, how much of the data actually changes, splitting historical from recent data, and incremental aggregation.

Sequences and Identity Columns

Surrogate keys need a source of unique numbers that works under heavy concurrency. A sequence is a special single-row object that hands out numbers without making transactions wait for each other. Identity columns, introduced in Integer and Serial Types, are columns whose default comes from a sequence the column owns. This topic looks at sequences as objects in their own right: their options, functions, behavior under concurrency and failover, and maintenance.

Analogy. A sequence is the ticket dispenser at a deli counter. Each customer pulls a number without coordinating with anyone; if a customer leaves without being served, that number is simply never called.

Creating and using a sequence

CREATE SEQUENCE invoice_seq
  AS bigint
  START WITH 1000
  INCREMENT BY 1
  MINVALUE 1000
  NO MAXVALUE                                     -- up to the type's maximum
  CACHE 1;                                        -- values preallocated per session (1 = no caching)

SELECT nextval('invoice_seq');                    -- 1000: advances the sequence and returns the value
SELECT nextval('invoice_seq');                    -- 1001
SELECT currval('invoice_seq');                    -- 1001: last value this session got from this sequence
SELECT lastval();                                 -- 1001: last value from any sequence in this session
SELECT setval('invoice_seq', 5000);               -- next nextval returns 5001
SELECT setval('invoice_seq', 5000, false);        -- next nextval returns 5000

currval is per session: it is safe to use right after your own nextval, regardless of what other sessions do. It fails if this session has not called nextval on that sequence yet.

Sequence properties that matter

Property	Behavior
Not transactional	nextval is never rolled back; aborted inserts leave gaps
No ordering guarantee across sessions	With CACHE 50, session A may insert 51 before session B inserts 1
Concurrency	nextval takes a very brief lock; it never waits for other transactions
CYCLE	Wraps around at the maximum instead of failing (rarely sensible for keys)
Crash or failover	Values may jump forward: the server logs sequence advances in batches of 32 to WAL, so a promoted standby can skip up to 32 values

Since values have gaps and are not ordered by commit time, never use them to infer "row 1001 was committed after row 1000," and never promise gap-free numbers (Integer and Serial Types shows a gap-free counter pattern).

Identity columns under the hood

CREATE TABLE invoices (
  id     bigint GENERATED ALWAYS AS IDENTITY (START WITH 1000 CACHE 20) PRIMARY KEY,
  amount numeric(12,2) NOT NULL
);

SELECT pg_get_serial_sequence('invoices', 'id');  -- public.invoices_id_seq: the owned sequence

INSERT INTO invoices (amount) VALUES (10) RETURNING id;                        -- 1000
INSERT INTO invoices (id, amount) VALUES (1, 10);
-- ERROR:  cannot insert a non-DEFAULT value into column "id"
INSERT INTO invoices (id, amount) OVERRIDING SYSTEM VALUE VALUES (1, 10);    -- explicit override (migrations)

ALTER TABLE invoices ALTER COLUMN id RESTART WITH 50000;                     -- change the identity's sequence
ALTER TABLE invoices ALTER COLUMN id SET GENERATED BY DEFAULT;               -- switch ALWAYS -> BY DEFAULT

	GENERATED ALWAYS	GENERATED BY DEFAULT
Explicit value in INSERT	Error, unless OVERRIDING SYSTEM VALUE	Accepted (the sequence is not advanced)
UPDATE of the column	Error, unless set to DEFAULT	Allowed
Typical use	Keys the database owns	Tables loaded from other systems with existing keys

Resetting after a bulk load. Loading rows with explicit ids leaves the sequence behind; the next generated id collides:

SELECT setval(pg_get_serial_sequence('invoices', 'id'),
              (SELECT coalesce(max(id), 0) FROM invoices) + 1, false);

Monitoring for exhaustion. An integer key runs out at 2,147,483,647. On a busy table, that can happen in a few years, and the fix (changing the column to bigint) rewrites the table. Check how much of each sequence is used:

SELECT schemaname, sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY pct_used DESC;

Sequences in replication and dumps. pg_dump saves each sequence's current value. Logical replication in PostgreSQL 18 and earlier does not replicate sequence values (the subscriber's sequences stay where they were), so after switching over to a logical replica, advance every sequence past the maximum key before accepting writes. Physical replicas follow the primary, subject to the jump of up to 32 values on promotion.

Alternatives. UUIDs (especially time-ordered uuidv7(), built in from PostgreSQL 18) need no central counter and can be generated by clients or several databases independently (UUID Type and Generation).

Key takeaways

• nextval hands out numbers without waiting and is never rolled back; gaps and out-of-order commits are normal.
• currval and lastval are per session; setval repositions a sequence.
• Identity columns own their sequence; ALWAYS rejects explicit values unless OVERRIDING SYSTEM VALUE is used.
• After loading explicit ids, reset the sequence with setval(pg_get_serial_sequence(...)).
• Monitor pg_sequences for exhaustion of integer keys; resync sequences manually after a logical-replication switchover.

🧪 Practice

1. (Beginner) Create a sequence with INCREMENT BY 10 and CACHE 5, call nextval from two sessions alternately, and explain the values each session receives.
2. (Intermediate) Create a table with an integer identity column, set its sequence near the maximum with ALTER TABLE ... RESTART, and insert until it fails. Then write the migration to bigint and estimate its lock duration on a 100 million-row table.
3. (Interview) "After failing over to a logical replica, inserts fail with duplicate key errors on several tables. Why, and how do you fix and prevent it?" Hint: Think about what logical replication does and does not copy, and which query moves each sequence past the existing maximum.

Generated Columns

Some columns are pure functions of other columns in the same row: a total from quantity and price, a lowercase copy of an email for lookups, a search vector built from a title and body. Computing them in the application risks inconsistencies when one code path forgets, and computing them with a trigger is extra code. A generated column declares the formula in the table definition, and the database keeps the value correct automatically.

Analogy. A generated column is a spreadsheet cell with a formula: you never type into it, and it always shows the result for the other cells in its row.

Stored vs. virtual

	STORED (PostgreSQL 12+)	VIRTUAL (PostgreSQL 18+, the default when neither is specified)
When computed	On INSERT and UPDATE, written to disk	On read, every time the column is selected
Disk space	Yes	None
Write cost	Computed on every write	None
Read cost	None	Expression evaluated per read
Indexable	Yes	No (index the expression instead)
Expression may use	Immutable built-in or user-defined functions	Immutable built-in functions and types only
Changing the expression	ALTER COLUMN ... SET EXPRESSION (17+), rewrites the table	SET EXPRESSION, no rewrite (not allowed on tables with check constraints)

Examples

CREATE TABLE order_lines (
  id          bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  quantity    int           NOT NULL CHECK (quantity > 0),
  unit_price  numeric(12,2) NOT NULL,
  discount    numeric(4,3)  NOT NULL DEFAULT 0,
  line_total  numeric(12,2) GENERATED ALWAYS AS (round(quantity * unit_price * (1 - discount), 2)) STORED,
  is_bulk     boolean       GENERATED ALWAYS AS (quantity >= 100) VIRTUAL    -- computed on read
);

INSERT INTO order_lines (quantity, unit_price, discount) VALUES (3, 9.99, 0.1), (150, 1.20, 0);
SELECT quantity, unit_price, line_total, is_bulk FROM order_lines;
--  quantity | unit_price | line_total | is_bulk
-- ----------+------------+------------+---------
--         3 |       9.99 |      26.97 | f
--       150 |       1.20 |     180.00 | t

UPDATE order_lines SET quantity = 4 WHERE id = 1;     -- line_total recomputed automatically
UPDATE order_lines SET line_total = 0;
-- ERROR:  column "line_total" can only be updated to DEFAULT

Common uses

-- case-insensitive lookups with an ordinary unique index
ALTER TABLE customers ADD COLUMN email_norm text GENERATED ALWAYS AS (lower(email)) STORED;
CREATE UNIQUE INDEX customers_email_norm_idx ON customers (email_norm);

-- extracting a JSON attribute for indexing and constraints
CREATE TABLE webhook_events (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, payload jsonb NOT NULL);
ALTER TABLE webhook_events ADD COLUMN country text GENERATED ALWAYS AS (payload ->> 'country') STORED;
CREATE INDEX webhook_events_country_idx ON webhook_events (country);

Full-text search vectors are another classic use (Parsing Documents into tsvector).

Rules and restrictions

Rule	Consequence
Expression must be IMMUTABLE	No now(), no lookups in other tables, no TimeZone-dependent casts
Only the same row	Use a view, trigger, or materialized view for values derived from other rows
Cannot reference another generated column	Repeat the expression or use a view
Cannot be written	INSERT/UPDATE may only specify DEFAULT
Cannot have a DEFAULT or be part of a partition key	It already has a value expression
Adding a STORED column to an existing table	Rewrites the table under an ACCESS EXCLUSIVE lock

Choosing a mechanism for derived values

Mechanism	Pick it when
Virtual generated column	Cheap expression, read occasionally, not indexed
Stored generated column	Expression is expensive or must be indexed, constrained, or replicated as data
Expression index alone	You only need to search by the expression, not return it (Expression Indexes)
View	The value depends on other rows or tables, or on volatile functions
Trigger	The value depends on other tables, needs now(), or must be written once and then frozen

Replication and dumps. pg_dump does not dump generated values; they are recomputed on restore. Logical replication (PostgreSQL 18+) can publish stored generated columns as ordinary data with the publish_generated_columns publication option, which helps when the subscriber is not PostgreSQL or does not have the same expression.

Key takeaways

• Generated columns keep a value derived from the same row correct automatically; they cannot be written directly.
• STORED columns are computed on write and can be indexed; VIRTUAL columns (default from PostgreSQL 18) are computed on read and take no space.
• Expressions must be immutable and refer only to the current row; virtual columns are further limited to built-in functions.
• Prefer generated columns over triggers for same-row derivations; use views, triggers, or materialized views for anything wider.

🧪 Practice

1. (Beginner) Add a stored generated column amount_cents bigint to transfers computed from amount, and verify it updates when amount changes.
2. (Intermediate) Try to add a generated column age_days computed from now() - created_at. Explain the error, then provide the same information with a view.
3. (Interview) "Should a full_name derived from first_name and last_name be a virtual column, a stored column, or computed in the application?" Hint: Consider whether it is searched or indexed, collation and locale rules, how often it is read versus written, and whether other systems read the table directly.

<a id="95-asynchronous-messaging"></a>

9.5 Asynchronous Messaging

This subchapter covers how PostgreSQL lets processes communicate and do work asynchronously: the built-in LISTEN/NOTIFY publish-subscribe mechanism and its delivery guarantees, durable queue tables, scheduled jobs with pg_cron, and the background worker processes that extensions use to run code inside the server.

LISTEN and NOTIFY

An application often needs to react when data changes: refresh a cache when prices change, push a live update to a dashboard when a transfer arrives, wake a worker when a job is queued. The naive solution is polling: run SELECT ... WHERE created_at > last_seen every second. Polling wastes queries when nothing happens and adds up to a second of delay when something does. LISTEN/NOTIFY is a built-in publish-subscribe mechanism: sessions subscribe to a named channel, and any session can send a message on it.

Analogy. Polling is walking to the mailbox every five minutes to check for letters. LISTEN is a doorbell: you do other things until someone rings.

The commands

Command	Effect
LISTEN channel	This session subscribes to channel
UNLISTEN channel / UNLISTEN *	Unsubscribe from one or all channels
NOTIFY channel [, 'payload']	Send a message; the payload is an optional string
SELECT pg_notify('channel', payload)	Same as NOTIFY, but channel and payload can be expressions

Trying it in psql

-- session A
LISTEN transfers;

-- session B
NOTIFY transfers, 'hello';

-- session A (psql checks for notifications after each command; run any command, even ";", to see them)
-- Asynchronous notification "transfers" with payload "hello" received from server process with PID 4711.

Notifications are transactional. A NOTIFY is delivered only if, and when, the sending transaction commits:

BEGIN;
NOTIFY transfers, 'never delivered';
ROLLBACK;                                         -- listeners receive nothing

BEGIN;
INSERT INTO transfers (from_id, to_id, amount) VALUES (2, 3, 1);
NOTIFY transfers, 'new transfer';                 -- queued
-- ... more work ...
COMMIT;                                           -- delivered now, together with the committed data

This is the property that makes NOTIFY useful: a listener that receives the message can immediately query the data it refers to and is guaranteed to see it.

Notifying from a trigger. A trigger makes every change announce itself, no matter which client made it:

CREATE FUNCTION notify_transfer() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM pg_notify(
    'transfers',
    json_build_object('id', NEW.id, 'from', NEW.from_id, 'to', NEW.to_id, 'amount', NEW.amount)::text
  );
  RETURN NULL;
END;
$$;

CREATE TRIGGER transfers_notify
AFTER INSERT ON transfers
FOR EACH ROW EXECUTE FUNCTION notify_transfer();

Receiving in application code. The client keeps a dedicated connection open and waits on its socket; no queries are sent while idle.

# Python, psycopg 3.2+
import json
import psycopg

with psycopg.connect("dbname=bank", autocommit=True) as conn:   # autocommit: deliveries arrive between transactions
    conn.execute("LISTEN transfers")
    for note in conn.notifies():                                # blocks until a notification arrives
        event = json.loads(note.payload)
        print(f"transfer {event['id']}: {event['amount']} from {event['from']} to {event['to']}")

// Node.js, node-postgres
const { Client } = require('pg');

const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
client.on('notification', (msg) => {
  const event = JSON.parse(msg.payload);
  console.log(`transfer ${event.id} on channel ${msg.channel}`);
});
await client.query('LISTEN transfers');                         // keep this client open; do not return it to a pool

Typical uses

Use	Pattern
Cache invalidation	Notify with the changed key; each app server evicts it
Waking workers	Notify after inserting a job; workers sleep until notified instead of polling (Queue Tables with SKIP LOCKED)
Live UI updates	A backend service listens and forwards events over WebSockets
Configuration reload	Notify after changing a settings table

Key takeaways

• LISTEN/NOTIFY is a lightweight publish-subscribe mechanism on named channels, built into PostgreSQL.
• Notifications are delivered only when the sending transaction commits, so listeners can read the data they refer to.
• pg_notify() allows computed channels and payloads, for example from triggers.
• Listening requires a dedicated, long-lived connection; clients wait on the socket without polling.

🧪 Practice

1. (Beginner) Open two psql sessions, LISTEN in one, and send notifications from the other inside a committed and a rolled-back transaction.
2. (Intermediate) Write a small Python or Node.js program that listens on transfers and prints a running total per account, driven by the trigger above.
3. (Interview) "How would you invalidate an application-level cache across 20 app servers when a row in the prices table changes?" Hint: Compare NOTIFY from a trigger, polling an updated_at column, and logical decoding; consider what happens when an app server is disconnected at the moment of the change.

Payload Limits and Delivery Semantics

NOTIFY is convenient, but it is not a message broker. Messages are kept in memory-backed storage only until every listener has read them, are never written to the WAL, and are delivered only to sessions connected at the time. Designing around these limits is what separates a reliable LISTEN/NOTIFY integration from one that silently loses events.

Limits

Limit	Value	Consequence
Payload size	Less than 8000 bytes (compile-time constant)	ERROR: payload string too long; send an ID, not the row
Queue size	8 GB by default (max_notify_queue_pages, PostgreSQL 17+; fixed before)	When full, NOTIFY fails at commit with an error
Channel name	An identifier, up to 63 bytes	Use a few stable channel names, filter by payload content
Scope	One database	A listener in database a never sees notifications sent in database b
Hot standby	LISTEN/NOTIFY not allowed	Listeners must connect to the primary

SELECT pg_notify('transfers', repeat('x', 8000));
-- ERROR:  payload string too long

SELECT pg_notification_queue_usage();             -- fraction of the queue in use: 0 to 1

Delivery semantics

Property	Behavior
When delivered	After the sender commits; the listener receives it between its own transactions (a listener inside a long transaction gets it only after that transaction ends)
Order	Notifications from one transaction arrive in the order sent; notifications from different transactions arrive in commit order
Duplicates	Within one transaction, identical notifications (same channel and payload) are collapsed into one
Durability	None; not written to WAL, lost on crash or restart
Disconnected listeners	Miss everything sent while they were away: at-most-once delivery
Sender	A session listening on a channel also receives its own notifications (check the PID if needed)

The stuck-listener problem. The queue can be trimmed only up to the oldest notification that some listening session has not consumed. A session that issued LISTEN and then sits in a long transaction (or is a pooled connection that never reads) holds the queue back. As usage approaches 100 %, every transaction that sends a notification fails. Watch pg_notification_queue_usage() and terminate offending sessions.

Connection pooling. A listener needs one specific server session for its whole life. With PgBouncer in transaction pooling mode, LISTEN runs on whatever server connection the transaction happened to get, and notifications are delivered to a session the client no longer holds. Listeners must connect directly or through a session-mode pool (Connection Pooling with PgBouncer).

Commit throughput. At commit, transactions that sent notifications take a global lock to append to the queue, which serializes them. For a few hundred or a few thousand notifying transactions per second this is fine; for very high write rates, notify per statement (a statement-level trigger) or per batch rather than per row.

Designing for reliability: the table is the truth. Since notifications can be lost, treat them as a hint to look, never as the only copy of the data:

 writer:    INSERT row into a table   +  NOTIFY channel, '<id>'    (same transaction)
 listener:  on start-up and after any reconnect: read everything newer than its checkpoint
            on notification: read rows newer than its checkpoint, process, advance the checkpoint
            on timeout (e.g. every 30 s) with no notification: read anyway (catches anything missed)

If a listener misses a notification, the next catch-up query finds the row anyway. When you need guaranteed delivery to systems outside the database, use a queue table, a transactional outbox (Queue Tables with SKIP LOCKED), or logical decoding (Change Data Capture Pipelines).

Key takeaways

• Payloads are limited to under 8000 bytes; send identifiers and let listeners query the details.
• Delivery happens after commit, in commit order, with duplicates collapsed within a transaction, and only to connected listeners.
• Notifications are not durable; design listeners to catch up from a table after reconnects and on a timer.
• A listener stuck in a long transaction can fill the queue and make notifying transactions fail; monitor pg_notification_queue_usage().
• Listeners need a dedicated session: not through transaction pooling, and not on hot standbys.

🧪 Practice

1. (Beginner) In one transaction, send the same notification three times and a different one once. Count how many the listener receives.
2. (Intermediate) Start a listener, BEGIN a transaction in it and leave it open, then send 100,000 notifications with 1 KB payloads from another session. Watch pg_notification_queue_usage() and explain the result.
3. (Interview) "Your service relies on NOTIFY to update a search index, and the index sometimes misses records. What are the possible causes and how do you make it reliable?" Hint: List the cases where a notification is never received (disconnects, deploys, pooling, crashes) and describe a checkpoint-based catch-up design.

Queue Tables with SKIP LOCKED

Some work must happen reliably but not immediately: send the confirmation email, generate the PDF statement, call the fraud-check API. Doing it inside the user's request makes the request slow and fragile; dropping it on the floor when a server restarts is unacceptable. A queue table stores pending work durably in the database, in the same transaction as the business change that created it, and a pool of workers claims and processes it. The locking mechanics (FOR UPDATE SKIP LOCKED) are covered in SKIP LOCKED and NOWAIT; this topic builds a complete queue around them.

Why a database queue? A separate message broker is an extra system to run, and it cannot take part in your database transaction: if you commit the order and then the broker publish fails (or the reverse), the two disagree. A queue table is written in the same transaction as the order, so either both exist or neither does.

The schema

CREATE TABLE tasks (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind       text        NOT NULL,                       -- what to do: 'send_receipt', 'fraud_check', ...
  payload    jsonb       NOT NULL DEFAULT '{}',
  run_at     timestamptz NOT NULL DEFAULT now(),         -- not before this time (delays and backoff)
  attempts   int         NOT NULL DEFAULT 0,
  last_error text,
  dead       boolean     NOT NULL DEFAULT false          -- gave up: kept for inspection ("dead letter")
);
CREATE INDEX tasks_ready_idx ON tasks (run_at) WHERE NOT dead;   -- only live tasks are indexed

-- wake idle workers when tasks arrive (a hint; workers also poll on a timer)
CREATE FUNCTION tasks_wake() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM pg_notify('tasks', '');
  RETURN NULL;
END;
$$;

CREATE TRIGGER tasks_wake
AFTER INSERT ON tasks
FOR EACH STATEMENT EXECUTE FUNCTION tasks_wake();         -- one notification per statement, not per row

Enqueueing in the business transaction

BEGIN;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 5) RETURNING id;   -- say id = 42
INSERT INTO tasks (kind, payload) VALUES ('send_receipt', '{"transfer_id": 42}');
COMMIT;                                     -- transfer and task become visible together, or not at all

The worker

import psycopg

CLAIM = """
    SELECT id, kind, payload, attempts
    FROM tasks
    WHERE NOT dead AND run_at <= now()
    ORDER BY run_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED              -- tasks being processed by other workers are skipped
"""
RESCHEDULE = """
    UPDATE tasks
    SET attempts   = attempts + 1,
        last_error = %s,
        run_at     = now() + interval '1 second' * power(2, attempts),   -- exponential backoff
        dead       = attempts + 1 >= 5                                   -- give up after 5 attempts
    WHERE id = %s
"""

def process_one(conn) -> bool:
    with conn.transaction():                           # the row lock lasts until this block ends
        row = conn.execute(CLAIM).fetchone()
        if row is None:
            return False
        task_id, kind, payload, attempts = row
        try:
            with conn.transaction():                   # savepoint: a failing handler undoes only its own writes
                handle(conn, kind, payload)            # application logic
            conn.execute("DELETE FROM tasks WHERE id = %s", (task_id,))
        except Exception as exc:
            conn.execute(RESCHEDULE, (str(exc), task_id))
    return True

def run_worker(dsn: str) -> None:
    with psycopg.connect(dsn, autocommit=True) as conn:
        conn.execute("LISTEN tasks")
        while True:
            while process_one(conn):                   # drain all ready tasks
                pass
            # sleep until notified, or at most 5 s (catches delayed tasks and lost notifications)
            for _ in conn.notifies(timeout=5.0, stop_after=1):
                pass

If the worker process crashes mid-task, its transaction aborts, the row lock disappears, and another worker picks up the task: work is processed at least once. Handlers must therefore be idempotent (safe to run twice), for example by recording "receipt sent for transfer 42" with a unique constraint (Idempotency and Retry Logic).

Long-running tasks. Holding a transaction open for a 10-minute task holds back vacuum across the database. For long tasks, claim in a short transaction (UPDATE ... SET locked_until = now() + interval '15 minutes' ... RETURNING), process outside any transaction, and delete when done; a task whose locked_until has passed is considered abandoned and is claimed again.

The transactional outbox. The same structure solves a common integration problem: publishing events to Kafka or another service exactly when the database change commits. The application writes an outbox row in the business transaction; a relay process claims rows with SKIP LOCKED, publishes them, and deletes them. Consumers must tolerate duplicates, since the relay can crash after publishing but before deleting.

Operating a queue table

Concern	Recommendation
Bloat	Every task is inserted, updated, and deleted; keep the table small and tune autovacuum aggressively for it (Autovacuum Configuration and Tuning)
Very high volume	Partition by time and drop old partitions instead of deleting rows
Monitoring	Queue depth and the age of the oldest ready task: SELECT count(*), now() - min(run_at) FROM tasks WHERE NOT dead AND run_at <= now()
Dead letters	Alert when dead tasks appear; provide a way to requeue them (UPDATE ... SET dead = false, attempts = 0)
Priorities and fairness	Add a priority column to the ORDER BY and the index; per-tenant fairness needs per-tenant limits
Ready-made tools	pgmq (extension), Graphile Worker (Node.js), River (Go), Oban (Elixir), Solid Queue (Rails) implement these patterns

Key takeaways

• A queue table stores work durably and transactionally with the business data that creates it.
• Workers claim tasks with FOR UPDATE SKIP LOCKED, so many workers never block each other; NOTIFY wakes them, a timer covers missed notifications.
• Delivery is at-least-once: crashes cause retries, so handlers must be idempotent.
• Retries with backoff and a dead-letter state handle failing tasks; long tasks should claim with a lease rather than an open transaction.
• Queue tables churn rows; keep them small, vacuum them aggressively, and monitor depth and age.

🧪 Practice

1. (Beginner) Enqueue 10 tasks and claim them from three psql sessions with the CLAIM query inside open transactions. Show each session gets a different task.
2. (Intermediate) Implement the Python worker with a handle function that fails randomly 30 % of the time. Run three workers on 1,000 tasks and verify every task is either completed or dead, and none is processed concurrently by two workers.
3. (Interview) "Compare a PostgreSQL queue table with a dedicated message broker such as RabbitMQ or Kafka for a system processing 500 jobs per second." Hint: Consider transactional enqueueing, operational cost, throughput limits from vacuum and index churn, ordering, fan-out to many consumers, and replay of past messages.

Job Scheduling with pg_cron

Databases need recurring work: refresh a materialized view every 15 minutes, delete expired sessions every night, create next month's partitions, roll up yesterday's statistics. An operating-system cron job on some server can do it, but then the schedule lives outside the database, needs credentials, and is forgotten when the database moves to a new host. pg_cron is an extension that runs SQL on a schedule from inside PostgreSQL, using familiar cron syntax.

Setting it up

# postgresql.conf (requires a restart)
shared_preload_libraries = 'pg_cron'
cron.database_name = 'bank'          # the database that holds the job tables (default: postgres)
cron.timezone = 'UTC'                # schedules are interpreted in this zone (default: GMT)

CREATE EXTENSION pg_cron;            -- in the database named by cron.database_name

pg_cron is available on most managed services (Amazon RDS and Aurora, Azure, Google Cloud SQL, and others), usually through the provider's parameter settings.

Scheduling jobs

 ┌───────── minute (0-59)
 │ ┌─────── hour (0-23)
 │ │ ┌───── day of month (1-31, or $ for the last day)
 │ │ │ ┌─── month (1-12)
 │ │ │ │ ┌─ day of week (0-6, Sunday = 0)
 * * * * *

-- every 15 minutes
SELECT cron.schedule('refresh-daily-payments', '*/15 * * * *',
                     'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_payments');

-- every night at 03:30
SELECT cron.schedule('purge-old-tasks', '30 3 * * *',
                     $$DELETE FROM tasks WHERE dead AND run_at < now() - interval '30 days'$$);

-- on the 25th of each month: create next month's partition (procedure from Dynamic SQL with EXECUTE;
-- assumes a partitioned table named payments_part)
SELECT cron.schedule('next-partition', '0 2 25 * *',
                     $$CALL create_month_partition('payments_part', (now() + interval '1 month')::date)$$);

-- every 30 seconds (interval syntax of pg_cron 1.5+): wake queue workers for delayed tasks that became due
SELECT cron.schedule('wake-task-workers', '30 seconds', $$SELECT pg_notify('tasks', '')$$);

-- a job that runs in another database
SELECT cron.schedule_in_database('analytics-rollup', '0 * * * *',
                                 'CALL rollup_hourly()', 'analytics');

Calling cron.schedule again with an existing job name updates that job. $$ quoting avoids escaping quotes inside the command.

Managing and monitoring

SELECT jobid, jobname, schedule, command, database, active FROM cron.job;

SELECT cron.alter_job(job_id := 3, active := false);      -- pause a job
SELECT cron.unschedule('purge-old-tasks');               -- remove a job

SELECT jobid, status, return_message, start_time::time(0) AS started, end_time - start_time AS duration
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;
--  jobid |  status   |                  return_message                  | started  |    duration
-- -------+-----------+--------------------------------------------------+----------+-----------------
--      4 | succeeded | 1 row                                            | 10:45:30 | 00:00:00.001
--      1 | succeeded | REFRESH MATERIALIZED VIEW                        | 10:45:00 | 00:00:00.761
--      4 | succeeded | 1 row                                            | 10:45:00 | 00:00:00.001
--      3 | failed    | ERROR:  relation "payments_part" does not exist  | 02:00:00 | 00:00:00.002

cron.job_run_details grows forever; schedule its own cleanup:

SELECT cron.schedule('purge-cron-history', '0 4 * * *',
                     $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '14 days'$$);

Behavior to know

Behavior	Detail
No overlapping runs	If a job is still running when its next run is due, that run waits until the current one finishes
Runs on the primary only	On a standby, the scheduler does nothing; after failover, the new primary starts running jobs
Execution	Each run opens a connection (or a background worker if cron.use_background_workers = on) as the job's owner
Concurrency	At most cron.max_running_jobs jobs run at once (default 32), each using a connection slot
Missed runs	If the server is down at the scheduled time, that run is skipped, not caught up
Transactions	A single-statement command runs in its own transaction; use CALL to a procedure for batched work with commits

Alternatives

Tool	Notes
OS cron / systemd timers + psql	Simple; schedule lives outside the database
Kubernetes CronJob, Airflow, other orchestrators	Better for multi-step pipelines across systems
pg_timetable	External scheduler with chains, dependencies, and retries, configured in database tables
pgAgent	Job scheduler used by pgAdmin

Key takeaways

• pg_cron runs SQL on cron schedules inside PostgreSQL; it is loaded via shared_preload_libraries and lives in one database.
• cron.schedule, cron.schedule_in_database, cron.alter_job, and cron.unschedule manage jobs; cron.job_run_details records each run.
• Runs never overlap, happen only on the primary, and missed runs are not caught up.
• Purge cron.job_run_details regularly and alert on failed runs.

🧪 Practice

1. (Beginner) Install pg_cron (for example with the postgresql-18-cron package), schedule a job that inserts now() into a heartbeat table every minute, and confirm it in cron.job_run_details.
2. (Intermediate) Schedule a job that runs for 90 seconds (SELECT pg_sleep(90)) every minute, and examine cron.job_run_details to show runs do not overlap.
3. (Interview) "What are the risks of running all scheduled maintenance through pg_cron on a highly available cluster?" Hint: Think about failover (which node runs jobs), jobs that take strong locks during peak hours, missed runs during outages, monitoring of failures, and connection slots.

Background Worker Processes

Everything covered so far runs when a client asks for it, inside that client's backend process. Some extensions need code that runs on its own: a scheduler that wakes every minute (pg_cron), a process that reloads the buffer cache after a restart (pg_prewarm), a job runner for continuous aggregates (TimescaleDB). PostgreSQL provides background workers: server processes, started and supervised by the postmaster, that run extension code with full access to shared memory and, optionally, a database connection.

Analogy. Client backends are waiters who serve the guests at their tables. Background workers are kitchen staff: nobody orders them around directly, but the restaurant cannot run without them.

Where they appear

SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 1;
--          backend_type         | count
-- ------------------------------+-------
--  autovacuum launcher          |     1
--  background writer            |     1
--  checkpointer                 |     1
--  client backend               |     1
--  io worker                    |     3      -- PostgreSQL 18 asynchronous I/O workers
--  logical replication launcher |     1      -- a background worker
--  pg_cron launcher             |     1      -- a background worker registered by an extension
--  walwriter                    |     1

Built-in auxiliary processes such as the checkpointer are not background workers in this technical sense, but the logical replication launcher, logical replication apply workers, and parallel query workers are, and so are processes registered by extensions.

Kinds of background workers

Kind	Registered	Example
Static	At server start, from _PG_init() of a library in shared_preload_libraries	pg_cron launcher, pg_prewarm autoprewarm, TimescaleDB scheduler
Dynamic	At run time with RegisterDynamicBackgroundWorker()	Parallel query workers, logical replication apply workers, TimescaleDB job workers

Capacity settings

Setting	Default	Meaning
max_worker_processes	8	Total background worker slots (restart required); shared by everything below plus extensions
max_parallel_workers	8	How many of those slots parallel queries may use
max_logical_replication_workers	4	Slots for logical replication apply and sync workers

When slots run out, parallel queries silently run with fewer workers, logical replication stops starting new apply workers, and extension workers fail to start (with a log message). Size max_worker_processes for all users together; on a server with TimescaleDB, logical replication, and parallel queries, 8 is often too few.

Writing a background worker (C)

#include "postgres.h"
#include "fmgr.h"
#include "miscadmin.h"
#include "access/xact.h"
#include "executor/spi.h"
#include "postmaster/bgworker.h"
#include "postmaster/interrupt.h"
#include "storage/ipc.h"
#include "storage/latch.h"
#include "utils/snapmgr.h"
#include "utils/wait_event.h"

PG_MODULE_MAGIC;

PGDLLEXPORT void heartbeat_main(Datum arg);

void
_PG_init(void)
{
    BackgroundWorker worker = {0};

    if (!process_shared_preload_libraries_in_progress)
        return;                                  /* static workers must be registered at start-up */

    worker.bgw_flags = BGWORKER_SHMEM_ACCESS | BGWORKER_BACKEND_DATABASE_CONNECTION;
    worker.bgw_start_time = BgWorkerStart_RecoveryFinished;   /* only on a primary, after recovery */
    worker.bgw_restart_time = 10;                /* restart 10 s after a crash; BGW_NEVER_RESTART to disable */
    snprintf(worker.bgw_library_name, BGW_MAXLEN, "heartbeat");
    snprintf(worker.bgw_function_name, BGW_MAXLEN, "heartbeat_main");
    snprintf(worker.bgw_name, BGW_MAXLEN, "heartbeat worker");
    snprintf(worker.bgw_type, BGW_MAXLEN, "heartbeat");       /* shown as backend_type */
    RegisterBackgroundWorker(&worker);
}

void
heartbeat_main(Datum arg)
{
    pqsignal(SIGTERM, SignalHandlerForShutdownRequest);       /* exit cleanly on shutdown */
    BackgroundWorkerUnblockSignals();
    BackgroundWorkerInitializeConnection("bank", NULL, 0);    /* connect to database "bank" */

    while (!ShutdownRequestPending)
    {
        /* sleep up to 60 s, waking early if signaled; exit if the postmaster dies */
        (void) WaitLatch(MyLatch, WL_LATCH_SET | WL_TIMEOUT | WL_EXIT_ON_PM_DEATH,
                         60 * 1000L, PG_WAIT_EXTENSION);
        ResetLatch(MyLatch);
        CHECK_FOR_INTERRUPTS();

        /* run SQL through SPI inside a transaction */
        SetCurrentStatementStartTimestamp();
        StartTransactionCommand();
        SPI_connect();
        PushActiveSnapshot(GetTransactionSnapshot());
        SPI_execute("INSERT INTO heartbeat (at) VALUES (now())", false, 0);
        SPI_finish();
        PopActiveSnapshot();
        CommitTransactionCommand();
    }
    proc_exit(0);
}

The library is built with PGXS like any extension (C Language Functions), copied into the server's library directory, and activated with shared_preload_libraries = 'heartbeat' plus a restart. The worker_spi module in the PostgreSQL source tree is the reference example.

Risks. A background worker with BGWORKER_SHMEM_ACCESS that crashes makes the postmaster restart all server processes, just like a crashing backend, which disconnects every client. Bugs in worker code therefore affect the whole cluster; only install background workers from trusted, well-maintained extensions.

Key takeaways

• Background workers are postmaster-supervised processes that run extension code independently of clients; they show up in pg_stat_activity with their own backend_type.
• Static workers are registered from shared_preload_libraries; dynamic workers are started at run time (parallel query and logical replication use them).
• All of them share max_worker_processes; running out silently reduces parallelism and blocks replication or extension workers.
• Writing one requires C, careful signal and latch handling, and SPI for SQL; a crash restarts the whole server.

🧪 Practice

1. (Beginner) List the background processes on your server with pg_stat_activity and look up what each backend_type does.
2. (Intermediate) Set max_worker_processes = 4 and max_parallel_workers_per_gather = 4, enable a logical replication subscription, and run a parallel query. Compare Workers Planned and Workers Launched in EXPLAIN ANALYZE and explain the difference.
3. (Interview) "An extension vendor asks you to add their library to shared_preload_libraries on a production cluster. What do you evaluate first?" Hint: Think about the processes and shared memory it adds, worker slots, crash behavior, upgrade compatibility across major versions, and support on managed services.

<a id="96-other-procedural-languages"></a>

9.6 Other Procedural Languages

PostgreSQL's function system is language-agnostic: a language is a handler that knows how to run function bodies. This subchapter covers the most common alternatives to SQL and PL/pgSQL (Python, Perl, Tcl, JavaScript, and C), and the trust model that decides who may write functions in each of them.

PL/Python

PL/pgSQL is good at orchestrating SQL, but weak at general programming: string manipulation beyond regular expressions, statistics, parsing file formats, or anything that a well-tested library already does. PL/Python runs Python inside the server process, with access to the standard library and to any package installed for the server's Python interpreter.

Analogy. PL/Python is like hiring a specialist into your kitchen: they bring their own tools and skills, but they work in your kitchen, use your power supply, and can make a mess if they are careless.

Enabling it

CREATE EXTENSION plpython3u;              -- "u" = untrusted: only superusers may create functions in it

On Debian and Ubuntu, the language is packaged separately (postgresql-plpython3-18). There is no trusted variant of PL/Python, because Python has no reliable sandbox (Trusted vs. Untrusted Languages).

A first function: the standard library

CREATE FUNCTION slugify(title text) RETURNS text
LANGUAGE plpython3u IMMUTABLE STRICT
AS $$
import re
import unicodedata

ascii_only = unicodedata.normalize('NFKD', title).encode('ascii', 'ignore').decode()  # "Crème" -> "Creme"
return re.sub(r'[^a-z0-9]+', '-', ascii_only.lower()).strip('-')
$$;

SELECT slugify('Crème Brûlée: 10 Tips & Tricks!');
--            slugify
-- -----------------------------
--  creme-brulee-10-tips-tricks

Arguments are available as Python variables with the parameter names; the function body is the body of a Python function, so return sends the result back.

Type mapping

PostgreSQL	Python
smallint, int, bigint	int
real, double precision	float
numeric	decimal.Decimal
text, varchar	str
bytea	bytes
boolean	bool
Arrays	list (nested for multidimensional)
Composite types / table rows	dict (on input); return a dict, tuple, or object with matching attributes
jsonb	str, or dict/list with the jsonb_plpython3u transform
SQL NULL	None

Querying the database: the plpy module

CREATE FUNCTION balance_stats(min_balance numeric DEFAULT 0)
RETURNS TABLE (n int, mean numeric, median numeric, stdev numeric)
LANGUAGE plpython3u STABLE
AS $$
import statistics

plan = plpy.prepare("SELECT balance FROM accounts WHERE balance >= $1", ["numeric"])
rows = plpy.execute(plan, [min_balance])                 # list-like result; each row is a dict
balances = [r["balance"] for r in rows]
if len(balances) < 2:
    plpy.notice(f"only {len(balances)} account(s) match")
    return []
return [(len(balances),
         statistics.mean(balances),
         statistics.median(balances),
         statistics.stdev(balances))]
$$;

SELECT * FROM balance_stats();
--  n |             mean              | median |             stdev
-- ---+-------------------------------+--------+-------------------------------
--  3 | 53.96666666666666666666666667 |  68.00 | 36.05971898577876235157420019

plpy function	Purpose
plpy.execute(query_or_plan [, args] [, limit])	Run SQL; returns rows as dicts
plpy.prepare(query, [types])	Prepare a parameterized statement (safe from injection, reusable)
plpy.cursor(query_or_plan [, args])	Iterate over large results without loading them all
plpy.notice, plpy.warning, plpy.error	Emit messages (error raises an exception)
plpy.quote_ident, plpy.quote_literal	Quoting for dynamic SQL
plpy.subtransaction()	Context manager for a savepoint

Caching between calls. The dictionaries SD (per function) and GD (shared by all PL/Python functions in the session) persist for the life of the session, which is useful for expensive set-up such as compiling a regular expression or loading a model:

CREATE FUNCTION is_valid_iban(iban text) RETURNS boolean
LANGUAGE plpython3u IMMUTABLE STRICT
AS $$
if "pattern" not in SD:
    import re
    SD["pattern"] = re.compile(r"^[A-Z]{2}[0-9]{2}[A-Z0-9]{11,30}$")
s = iban.replace(" ", "").upper()
if not SD["pattern"].match(s):
    return False
digits = "".join(str(int(c, 36)) for c in s[4:] + s[:4])     # move country code to the end, letters to numbers
return int(digits) % 97 == 1                                  # ISO 13616 checksum
$$;

SELECT is_valid_iban('GB82 WEST 1234 5698 7654 32');          -- true

Trade-offs

Advantage	Cost
Whole Python ecosystem (parsing, statistics, ML inference)	Runs in the database process: CPU and memory come from the database server
Logic next to the data, no round trips	Packages must be installed on every server (and replica) for the server's Python version
Familiar language	Untrusted: only superusers create functions; often not available on managed services
	Slower calls than PL/pgSQL for simple logic; data conversion per call

Key takeaways

• PL/Python (plpython3u) runs Python in the server, with the standard library and installed packages available.
• Arguments arrive as Python values (numeric as Decimal, rows as dicts); plpy runs queries, prepares plans, and raises messages.
• SD and GD cache expensive objects for the session.
• It is untrusted (superuser-only) and uses database server resources; use it for logic that genuinely needs Python.

🧪 Practice

1. (Beginner) Write a PL/Python function word_count(doc text) RETURNS int and compare its result with a SQL version using regexp_split_to_array.
2. (Intermediate) Write top_words(n int) RETURNS TABLE (word text, freq int) that reads all transfer notes with plpy.cursor and returns the most common words using collections.Counter.
3. (Interview) "A data science team wants to run model inference in PL/Python directly on the production database. What concerns would you raise?" Hint: Think about CPU and memory contention with OLTP queries, package management on replicas and upgrades, superuser requirements, crash isolation, and alternatives such as a separate service or a read replica.

PL/Perl and PL/Tcl

Before Python became popular, Perl was the standard tool for text processing, and Tcl was a common embedded scripting language. Both have long-standing procedural languages in the PostgreSQL distribution. Unlike PL/Python, each comes in a trusted variant that runs in a sandbox, so ordinary users can be allowed to write functions in it.

Variants

Language	Trusted	Untrusted	Sandbox
Perl	plperl	plperlu	Perl's Safe module blocks file, network, and process operations
Tcl	pltcl	pltclu	Tcl "safe interpreter" with dangerous commands removed

PL/Perl: text processing

CREATE EXTENSION plperl;

CREATE FUNCTION extract_tags(body text) RETURNS text[]
LANGUAGE plperl IMMUTABLE STRICT
AS $$
  my ($body) = @_;                              # arguments arrive in @_
  my %seen;
  my @tags = grep { !$seen{$_}++ }              # de-duplicate, keep first occurrence
             map  { lc }
             ($body =~ /#(\w+)/g);              # every #word in the text
  return \@tags;                                # an array reference becomes a PostgreSQL array
$$;

SELECT extract_tags('Paid rent #Housing #monthly, see #housing notes');
--    extract_tags
-- -------------------
--  {housing,monthly}

Database access in PL/Perl

CREATE FUNCTION owners_with_prefix(prefix text) RETURNS SETOF text
LANGUAGE plperl STABLE
AS $$
  my ($prefix) = @_;
  my $plan = spi_prepare('SELECT owner FROM accounts WHERE owner LIKE $1 ORDER BY owner', 'text');
  my $rv   = spi_exec_prepared($plan, $prefix . '%');
  return_next($_->{owner}) for @{ $rv->{rows} };  # stream each row to the result set
  spi_freeplan($plan);
  return undef;
$$;

SELECT * FROM owners_with_prefix('a');            -- alice

Other useful functions are spi_exec_query (run a query), spi_query/spi_fetchrow (cursor-style iteration), elog(NOTICE, ...) (messages), quote_ident and quote_literal, and the session-wide hash %_SHARED for caching.

The sandbox in action

CREATE FUNCTION read_file(path text) RETURNS text
LANGUAGE plperl
AS $$
  open(my $fh, '<', $_[0]) or die "cannot open: $!";
  local $/; return <$fh>;
$$;
-- ERROR:  'open' trapped by operation mask at line 2.         -- rejected when the function is compiled
-- CONTEXT:  compilation of PL/Perl function "read_file"

The same function in plperlu would read any file the postgres operating-system user can read, which is why only superusers can create plperlu functions.

PL/Tcl works the same way: arguments are $1, $2, ...; spi_exec runs queries and can loop over rows; elog emits messages. It is rarely chosen for new code, but it is maintained and appears in older systems:

CREATE EXTENSION pltcl;

CREATE FUNCTION tcl_max(a int, b int) RETURNS int
LANGUAGE pltcl IMMUTABLE STRICT
AS $$
  if {$1 > $2} { return $1 }
  return $2
$$;

Choosing between them

Need	Suggestion
Heavy regex or text munging, and you want non-superusers to write functions	plperl (trusted)
Access to CPAN modules, files, or the network	plperlu, written by a superuser
Maintaining legacy Tcl code	pltcl
New general-purpose code	PL/pgSQL first; PL/Python when libraries are required

Key takeaways

• PL/Perl and PL/Tcl ship with PostgreSQL in trusted (plperl, pltcl) and untrusted (plperlu, pltclu) variants.
• Trusted variants block file, network, and process access, so non-superusers can be allowed to use them.
• PL/Perl excels at regular expressions and text processing; arrays and sets map naturally to Perl references and return_next.
• Both access the database through SPI functions (spi_exec_query, spi_prepare, spi_exec).

🧪 Practice

1. (Beginner) Write a plperl function that masks all but the last four digits of a card number, and test it on several formats with spaces and dashes.
2. (Intermediate) Show that a non-superuser with USAGE on plperl can create a function, but cannot create the same function in plperlu. Then demonstrate the operation mask by trying to open a file.
3. (Interview) "Why does PostgreSQL offer a trusted PL/Perl but no trusted PL/Python?" Hint: Think about what a trusted language must guarantee, how Perl's Safe compartments work, and why Python's dynamic introspection makes a reliable in-process sandbox impractical.

PL/V8 for JavaScript

Many teams write their applications in JavaScript or TypeScript, and much of their data is JSON. PL/V8 is a third-party extension that embeds Google's V8 JavaScript engine, letting you write functions in JavaScript with native handling of JSON values. It is a trusted language: JavaScript in V8 has no built-in access to files, the network, or processes.

Analogy. PL/V8 is a phrasebook that lets your JavaScript developers speak directly to the database in their own language, at the cost of carrying a large phrasebook (the V8 engine) everywhere.

Enabling it. PL/V8 is not part of the PostgreSQL distribution; it must be built (which includes building V8) or installed from a package, and its availability on managed services varies and has been shrinking. Check before designing around it.

CREATE EXTENSION plv8;
SELECT plv8_version();

JSON in, JSON out. json and jsonb arguments arrive as JavaScript objects, and returned objects are converted back:

CREATE FUNCTION mask_card(doc jsonb) RETURNS jsonb
LANGUAGE plv8 IMMUTABLE STRICT
AS $$
  const out = { ...doc };                                  // shallow copy; the argument is named doc
  if (typeof out.card === 'string') {
    out.card = out.card.replace(/\d(?=\d{4})/g, '*');      // keep only the last four digits
  }
  delete out.cvv;                                          // never store the security code
  return out;
$$;

SELECT mask_card('{"card": "4111111111111111", "cvv": "123", "name": "Ann"}');
--                   mask_card
-- ---------------------------------------------
--  {"card": "************1111", "name": "Ann"}

Database access

CREATE FUNCTION transfers_as_json(acct int) RETURNS jsonb
LANGUAGE plv8 STABLE
AS $$
  const rows = plv8.execute(
    'SELECT id, to_id, amount FROM transfers WHERE from_id = $1 ORDER BY id',
    [acct]                                                  // parameters, never string concatenation
  );
  plv8.elog(NOTICE, `found ${rows.length} transfers`);
  return { account: acct, transfers: rows };                // rows is an array of plain objects
$$;

plv8 API	Purpose
plv8.execute(sql, [args])	Run a query, returning an array of row objects (or a row count for DML)
plv8.prepare(sql, [types])	Prepared plan with .execute(args), .cursor(args), .free()
plv8.elog(level, msg)	Messages: DEBUG1 to ERROR
plv8.subtransaction(fn)	Run a function in a savepoint
plv8.find_function(name)	Call another PL/V8 function directly

Shared code. PL/V8 has no import. A common workaround is to store library code in a table and evaluate it at session start through the plv8.start_proc setting, which names a function to run when the V8 context is created.

When to use it

Good fit	Poor fit
Complex JSON reshaping or validation that is awkward in SQL/JSON	Simple JSON access (->, ->>, jsonb_path_query are faster and built in)
Sharing small, pure validation functions with front-end code	Environments where the extension is unavailable
Teams fluent in JavaScript but not PL/pgSQL	Heavy computation; memory per backend grows with the V8 heap

Key takeaways

• PL/V8 embeds the V8 engine for trusted JavaScript functions with native JSON handling.
• plv8.execute and plv8.prepare run parameterized SQL; plv8.elog emits messages.
• It is a third-party extension that must be built or installed separately, and managed-service support varies.
• Prefer built-in JSON operators and SQL/JSON functions for simple cases; use PL/V8 for complex document logic when it is available.

🧪 Practice

1. (Beginner) If PL/V8 is available, write a function that removes all keys starting with _ from a jsonb object. Otherwise, write the same function in SQL with jsonb_object_agg and jsonb_each.
2. (Intermediate) Implement mask_card in PL/V8 (if available), PL/pgSQL, and pure SQL with regexp_replace. Benchmark each over 1 million JSON documents.
3. (Interview) "A team wants to move business logic written in Node.js into PL/V8 functions to share it between the API and the database. What would you ask before agreeing?" Hint: Consider extension availability on your platform and its upgrade path, debugging and testing tools, memory per connection, and whether the logic really needs to run inside the database.

C Language Functions

Every built-in function in PostgreSQL, from lower() to the B-tree comparison routines, is a C function in the server binary. User-defined C functions use the same interface: they are compiled into a shared library that the server loads, and they run at native speed with full access to server internals. This is how extensions such as PostGIS and pgvector add types, operators, and index support.

When C is worth it

Reason	Example
Speed in tight per-row code	Checksums, parsers, numeric kernels called millions of times
New data types and operators	vector, geometry, custom encodings
Index and planner support	Operator classes, SUPPORT functions, custom access methods
Access to internals	Background workers, hooks, custom scans

A complete example: Luhn checksum validation

/* luhn.c */
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
#include "varatt.h"                             /* varlena macros (PostgreSQL 16+) */

PG_MODULE_MAGIC;                                /* version stamp checked when the library is loaded */

PG_FUNCTION_INFO_V1(luhn_valid);                /* declare the version-1 calling convention */

Datum
luhn_valid(PG_FUNCTION_ARGS)
{
    text   *input = PG_GETARG_TEXT_PP(0);       /* argument 0 as text (possibly detoasted) */
    char   *s = VARDATA_ANY(input);             /* pointer to the bytes (not NUL-terminated) */
    int     len = VARSIZE_ANY_EXHDR(input);     /* length in bytes */
    int     sum = 0;
    int     ndigits = 0;

    for (int i = len - 1; i >= 0; i--)          /* walk from the rightmost character */
    {
        int     d;

        if (s[i] == ' ' || s[i] == '-')
            continue;                           /* allow separators */
        if (s[i] < '0' || s[i] > '9')
            PG_RETURN_BOOL(false);              /* any other character: invalid */

        d = s[i] - '0';
        if (ndigits % 2 == 1)                   /* double every second digit */
        {
            d *= 2;
            if (d > 9)
                d -= 9;
        }
        sum += d;
        ndigits++;
    }
    PG_RETURN_BOOL(ndigits > 1 && sum % 10 == 0);
}

# Makefile, built with PGXS (the PostgreSQL extension build system)
MODULES = luhn
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

make && sudo make install          # compiles luhn.so and copies it to $(pg_config --pkglibdir)

CREATE FUNCTION luhn_valid(text) RETURNS boolean
AS '$libdir/luhn', 'luhn_valid'                 -- library file and C symbol
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;      -- STRICT: never called with NULL, so no NULL checks in C

SELECT luhn_valid('4111 1111 1111 1111'), luhn_valid('4111 1111 1111 1112');
--  luhn_valid | luhn_valid
-- ------------+------------
--  t          | f

How fast? Validating 1 million 16-digit numbers on the test machine used for this guide (PostgreSQL 18, parallelism disabled):

Implementation	Time
SELECT count(*) without any function (baseline)	0.06 s
C function	0.10 s
PL/pgSQL loop over characters with substr	11.3 s
SQL function (generate_series over positions, substr, sum)	61 s

The C version adds about 40 nanoseconds per row; the interpreted versions spend microseconds per row on function-call, expression, and string overhead. For logic this small, that difference rarely matters in an OLTP query that touches a few rows, but it dominates a batch job or a filter over millions of rows.

Key parts of the interface

Element	Purpose
PG_MODULE_MAGIC	Stamps the library with the server major version; loading into another major version fails
PG_FUNCTION_INFO_V1(name)	Declares the version-1 calling convention for name
PG_GETARG_INT32(n), PG_GETARG_TEXT_PP(n), ...	Fetch argument n as a C value
PG_RETURN_BOOL(x), PG_RETURN_TEXT_P(t), ...	Return a value
PG_ARGISNULL(n), PG_RETURN_NULL()	NULL handling for non-STRICT functions
palloc / pfree	Memory allocation in the current memory context; freed automatically at the end of the call or query
ereport(ERROR, ...)	Raise an error (never use exit() or abort())

Risks and responsibilities

• A bug (a bad pointer, a buffer overrun) crashes the backend, and the postmaster then restarts every connection to recover shared memory. A subtle bug can corrupt data.
• Only superusers can create C functions, because the code can do anything the postgres OS user can do.
• Libraries must be recompiled for every major PostgreSQL version (PG_MODULE_MAGIC enforces this), and installed on every server, including replicas.
• Managed services do not allow arbitrary C code; only the provider's allowlisted extensions are available.

For anything beyond a single function, package the library as an extension with a control file and SQL script (Building Custom Extensions).

Key takeaways

• C functions use the same version-1 interface as built-in functions and run at native speed.
• PG_MODULE_MAGIC, PG_FUNCTION_INFO_V1, and the PG_GETARG_*/PG_RETURN_* macros form the interface; build with PGXS.
• Declare STRICT, volatility, and PARALLEL SAFE correctly when creating the SQL function.
• Bugs crash the server; only superusers can install C functions, and libraries must be rebuilt for each major version.

🧪 Practice

1. (Beginner) Build the luhn module and reproduce the timing comparison against a PL/pgSQL version on 1 million rows.
2. (Intermediate) Write a C function add_one(int) RETURNS int without STRICT, and handle NULL with PG_ARGISNULL. Then add overflow checking that raises an error with ereport.
3. (Interview) "When would you write a C extension instead of using PL/pgSQL or PL/Python, and how would you reduce the operational risk?" Hint: Weigh performance needs and access to internals against crash risk, review and testing (including fuzzing and amcheck), version upgrades, and platform support.

Trusted vs. Untrusted Languages

A function written in a procedural language runs inside the server process, as the operating-system user that runs PostgreSQL (usually postgres). If a language lets function code open files or start programs, then anyone who can write a function in it can read the data directory, other databases, and server configuration, bypassing every SQL privilege. PostgreSQL therefore classifies languages as trusted or untrusted and decides who may create functions in each.

Analogy. A trusted language is a visitor badge that opens only the meeting rooms; an untrusted language is a master key to the building, so only the building manager may hold one.

The two classes

	Trusted	Untrusted
Promise	Code cannot reach outside the database (no files, network, or processes)	No restrictions
Who can create functions	Any role with USAGE on the language (granted to PUBLIC by default for trusted languages)	Superusers only
Examples	sql, plpgsql, plperl, pltcl, plv8, plrust	c, plpython3u, plperlu, pltclu
Naming convention	Plain name	Usually ends in u

SELECT lanname, lanpltrusted FROM pg_language ORDER BY lanname;
--   lanname   | lanpltrusted
-- ------------+--------------
--  c          | f
--  internal   | f
--  plperl     | t
--  plpgsql    | t
--  plpython3u | f
--  pltcl      | t
--  sql        | t

What "untrusted" means in practice

CREATE FUNCTION server_user() RETURNS text
LANGUAGE plpython3u
AS $$
import getpass, os
return f"{getpass.getuser()} can read: {', '.join(sorted(os.listdir('.'))[:4])} ..."
$$;

SELECT server_user();
--                       server_user
-- --------------------------------------------------------
--  postgres can read: PG_VERSION, base, global, pg_commit_ts ...    -- the data directory

Controlling who may write functions

REVOKE USAGE ON LANGUAGE plperl FROM PUBLIC;     -- trusted languages are open to everyone by default
GRANT  USAGE ON LANGUAGE plperl TO developers;

-- untrusted languages: non-superusers cannot be granted the right to create functions at all
GRANT USAGE ON LANGUAGE plpython3u TO developers;
-- ERROR:  language "plpython3u" is not trusted
-- DETAIL:  GRANT and REVOKE are not allowed on untrusted languages, because only superusers can use untrusted languages.

Using untrusted functions safely. A superuser can write a function in an untrusted language and grant EXECUTE on it to others. That function then acts with the server's operating-system privileges on behalf of the caller, much like a SECURITY DEFINER function at the OS level, so review it as carefully: validate every argument, never pass caller input into file paths, shell commands, or dynamic SQL without strict checks, and REVOKE EXECUTE ... FROM PUBLIC first.

Trust is not resource safety. A trusted language prevents escaping the database, not abusing it. Any function can loop forever, allocate gigabytes of memory, or run expensive queries. Limit those risks with statement_timeout, per-role settings, and code review.

Managed services and newer options

Option	What it offers
Provider allowlists (Amazon RDS, Cloud SQL, Azure)	A fixed set of languages and extensions; untrusted languages usually unavailable
pg_tle (Trusted Language Extensions)	Lets non-superusers install extensions written in trusted languages
PL/Rust (plrust)	A trusted language that compiles Rust functions to native code, using Rust's compile-time safety plus restrictions on what code may use

Key takeaways

• Trusted languages cannot touch the server's files, network, or processes; untrusted ones can do anything the postgres OS user can.
• Any role with USAGE can create functions in a trusted language; only superusers can use untrusted languages, and USAGE cannot be granted on them.
• pg_language.lanpltrusted shows each language's class; revoke USAGE on trusted languages from PUBLIC if you want to restrict who writes server-side code.
• Untrusted functions granted to others act with OS-level privileges; review them like SECURITY DEFINER functions.
• Trust protects the operating system, not resources; use timeouts and review for runaway code.

🧪 Practice

1. (Beginner) List the installed languages and their trust status. Create a non-superuser role and determine in which of them it can create functions.
2. (Intermediate) As a superuser, create a plpython3u function that returns the size of a file in the data directory, grant EXECUTE to a normal role, and then rewrite it so that the role cannot use it to probe arbitrary paths.
3. (Interview) "A developer asks for superuser access because they need to write PL/Python functions. How do you respond?" Hint: Consider deploying functions through a reviewed migration pipeline run by a privileged role, SECURITY DEFINER wrappers with EXECUTE grants, trusted alternatives, and why superuser access cannot be scoped.

<a id="10-security-and-access-control"></a>