In PL/pgSQL, when an EXCEPTION clause catches an error, what happens to data changes and local variables, and where do RAISE and SQLSTATE fit?
answer
- rollback to the block's start
- variables keep their values
- OTHERS has two exceptions
- P0001 when no code is given
- GET STACKED DIAGNOSTICS
basics
~20 sWhen an EXCEPTION handler catches an error, PostgreSQL rolls back the block's database changes but keeps local variables and earlier statements. RAISE EXCEPTION throws an error, P0001 by default or a chosen SQLSTATE that handlers match.
solid answer
~40 sA PL/pgSQL block with an `EXCEPTION` section acts as a subtransaction. If a statement fails and a `WHEN` condition matches, every database change made inside that block is rolled back, local variables keep the values they had at the failure, statements before the block are untouched, and execution continues after `END`. Conditions are names like `check_violation`, `SQLSTATE '23514'`, a category, or `OTHERS`, which skips `query_canceled` and `assert_failure`. In the handler, `SQLSTATE`, `SQLERRM` and `GET STACKED DIAGNOSTICS` describe the error. `RAISE EXCEPTION` throws one, defaulting to `P0001`; I give business failures their own code with `USING ERRCODE` so callers branch on the code, and use a bare `RAISE;` to re-throw. Such blocks cost more to enter, so I keep them out of hot loops.
code
sql · 26 linesCREATE FUNCTION redeem_points(p_member bigint, p_points bigint)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
v_attempt int := 0;
v_state text;
v_msg text;
BEGIN
INSERT INTO points_audit (member_id, action) VALUES (p_member, 'redeem requested');
BEGIN
v_attempt := v_attempt + 1;
-- CHECK (points_balance >= 0) fails if the balance is too low
UPDATE loyalty_member
SET points_balance = points_balance - p_points
WHERE member_id = p_member;
EXCEPTION
WHEN check_violation THEN
GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE,
v_msg = MESSAGE_TEXT;
RAISE NOTICE 'redeem refused (%): %', v_state, v_msg;
RETURN 'refused after ' || v_attempt || ' attempt';
END;
RETURN 'redeemed';
END;
$$;go deeper
Recall that an EXCEPTION section catches errors by condition name and that RAISE EXCEPTION throws one. Know that RAISE NOTICE only prints a message.
Explain the rollback rule precisely: the block's data changes undone, variables and earlier statements kept. Cover OTHERS's two exclusions, P0001 as the default, and GET STACKED DIAGNOSTICS.
Show judgement on cost and design: no handler per row in hot loops, custom SQLSTATEs callers can branch on, bare RAISE to preserve context, and never swallowing query_canceled.
Frame error codes as a contract between database code and applications: a documented SQLSTATE catalogue, which failures are retryable, and how they surface in logs and alerts.
## Errors abort by default In PL/pgSQL any error aborts the function and the surrounding transaction unless a block traps it. Trapping uses an `EXCEPTION` section: ```sql BEGIN statements EXCEPTION WHEN condition [OR condition ...] THEN handler_statements [WHEN ... THEN ...] END; ``` When a statement fails, the rest of the block is abandoned and the `WHEN` list is searched in order for the first matching condition. If one matches, its handler runs and control continues after `END`. If none matches, the error propagates as though there were no `EXCEPTION` section, to an enclosing handler or out of the function. An error raised **inside** a handler is not caught by that same `EXCEPTION` section. ## What is rolled back, what is kept The rule fits in one sentence: **local variables keep the values they had when the error occurred, but all changes to persistent database state made inside the block are rolled back.** Statements that ran before the block are untouched, and the transaction is usable again once the handler finishes. That works because a block with an `EXCEPTION` section effectively forms a **subtransaction**: entering it sets a rollback point, and catching an error rolls back to it. In the loyalty example below, the audit row inserted before the inner block survives, the `UPDATE` that would push a balance below zero is undone by the `CHECK` failure, and the attempt counter incremented inside the block keeps its new value. The price is real: the manual says a block with an `EXCEPTION` section is **significantly more expensive to enter and exit** than one without. Wrapping each iteration of a large loop in its own handler multiplies that cost. How subtransactions consume transaction IDs and the per-backend subtransaction cache is a visibility topic of its own. ## Naming the condition - **Condition names** from the error-codes appendix, case-insensitive: `unique_violation`, `check_violation`, `no_data_found`, `division_by_zero`. - **SQLSTATE codes**: `WHEN SQLSTATE '23505'` is the same as `WHEN unique_violation`. - **Category names** match every error in their class, for example `integrity_constraint_violation` for class 23. - **`OTHERS`** matches everything except `query_canceled` and `assert_failure`. Those two can be trapped only by name, which is usually unwise: swallowing `query_canceled` defeats `statement_timeout` and an operator's cancel request. Inside a handler, the special variables `SQLSTATE` and `SQLERRM` hold the error code and message. `GET STACKED DIAGNOSTICS` fetches more: `RETURNED_SQLSTATE`, `MESSAGE_TEXT`, `PG_EXCEPTION_DETAIL`, `PG_EXCEPTION_HINT`, `PG_EXCEPTION_CONTEXT`, and the object names `COLUMN_NAME`, `CONSTRAINT_NAME`, `TABLE_NAME`, `SCHEMA_NAME` and `PG_DATATYPE_NAME`. Checking `CONSTRAINT_NAME` is how a handler tells which unique index fired on a table that has several. ## RAISE: messages and errors `RAISE level 'format', args [USING option = value, ...];` with `%` as the placeholder in the format. | Level | Effect | |---|---| | `DEBUG`, `LOG`, `INFO`, `NOTICE`, `WARNING` | a message only; execution continues | | `EXCEPTION` (the default level) | raises an error, aborting the transaction unless a handler traps it | Whether a message reaches the client or the server log is decided by `client_min_messages` (default `notice`) and `log_min_messages` (default `warning`). `USING` attaches `MESSAGE`, `DETAIL`, `HINT`, `ERRCODE`, and the object names `COLUMN`, `CONSTRAINT`, `DATATYPE`, `TABLE` and `SCHEMA`. With no code given, `RAISE EXCEPTION` reports `raise_exception`, SQLSTATE **`P0001`**. A bare `RAISE;` inside a handler re-throws the error being handled, keeping its original code. ## Designing error codes for callers 1. Give business failures their own SQLSTATE, such as `USING ERRCODE = 'LP001'` for "insufficient points". A custom code is five digits or upper-case ASCII letters, other than `00000`; avoid codes ending in `000`, which are category codes and can be caught only by catching the whole category. 2. Let the caller, or the application's driver, branch on the SQLSTATE, never on parsing `MESSAGE_TEXT`. 3. Catch only what you can handle, and re-raise the rest with `RAISE;` so the original code and context survive. 4. Prefer prevention to trapping: `INSERT ... ON CONFLICT` instead of catching `unique_violation`, and a balance check before the `UPDATE` instead of trapping `check_violation` inside a hot loop.
- Why is catching unique_violation in a retry loop the wrong way to upsert a loyalty enrolment?Each pass enters a block with an `EXCEPTION` section, which costs a subtransaction, and the handler cannot tell which unique index fired unless it checks `CONSTRAINT_NAME`. `INSERT ... ON CONFLICT DO UPDATE` performs the same insert-or-update atomically without a handler; the manual itself recommends it over the exception-loop pattern.
- What is the difference between `RAISE;` and `RAISE EXCEPTION '%', SQLERRM;` inside a handler?A bare `RAISE;` re-throws the error being handled with its original SQLSTATE, detail, hint and context. `RAISE EXCEPTION '%', SQLERRM` creates a new error that keeps only the message text and reports `P0001`, so callers branching on the original code, such as `23505`, no longer match.
An EXCEPTION block is like doing your edits on a tracing sheet laid over the ledger: if something goes wrong, you throw the sheet away and the ledger shows only what was inked before the sheet went down, but the notes you scribbled in your own notebook meanwhile stay. The sheet is the block's database changes; the notebook is its local variables.
- A caught error still rolls back everything the function did before the block.
- Local variables are reset to their defaults when the handler runs.
- WHEN OTHERS catches every error, including a statement_timeout cancel.
- Wrapping each loop iteration in its own EXCEPTION block costs nothing extra.
- Callers should identify business errors by matching the message text.
- RAISE NOTICE stops execution the same way RAISE EXCEPTION does.