The PL/pgSQL exception section

Syntax

• Grammar
• Diagram

plpgsql_exception_section ::= EXCEPTION { plpgsql_handler [ ... ] }

plpgsql_handler ::= WHEN { plpgsql_handler_condition [ OR ... ] } THEN 
                    { plpgsql_executable_stmt [ ... ] }

plpgsql_handler_condition ::= SQLSTATE errcode_literal
                              | exception_name
                              | OTHERS

Semantics

The plpgsql_exception_section (or "exception section" in prose) contains one or more so-called handlers. Briefly, when an error occurs in PL/pgSQL code, or in a SQL statement that it issues dynamically, this manifests as an exception. Then the point of execution immediately moves to a handler that matches the exception in the exception section of the most-tightly-enclosing plpgsql_block_stmt. (When this happens, the exception is said to have been caught.) See Exception handling in PL/pgSQL, below, for the full account.

The relationship between the terms of art "error" and "exception".

The term of art error is used to characterize the outcome when the attempt to execute a SQL statement fails. Any putative SQL statement can, of course, cause a syntax error. And almost all syntactically correct SQL statements have the capacity to cause a semantic error. (Only a few degenerate special cases, like "select 1" cannot cause an error.)

When you use psql (or ysqlsh) a SQL error is reported, on stderr, in general using several lines of text—the first of which starts with the word ERROR. Various facts about the error are available and you can control the volume of the report with the psql variable VERBOSITY. The legal values are default, terse, and verbose and you specify your choice with the \set meta-command.

You can see the effect of the VERBOSITY choice with a trivial example that provokes a run-time error:

\set VERBOSITY default
select 1.0/0.0;

It causes this output:

ERROR:  division by zero

When you attempt the same statement with the verbose choice, you get this output:

ERROR:  22012: division by zero
LOCATION:  int4div, int.c:820

Various client-side tools (for example, Python with psycopg) can be used to connect to a database in a PostgreSQL cluster or in a YugabyteDB cluster. Each has its own way of reporting when a SQL statement causes an error and, then, giving access to the error code and to other information about the context of the error. PL/pgSQL also has corresponding mechanisms. (But language sql subprograms have no such mechanism.)

When a SQL error occurs during the execution of a PL/pgSQL program, it manifests as a so-called exception. Notice that every PL/pgSQL expression is evaluated by using an under-the-covers select statement. See the section PL/pgSQL's "create" time and execution model. It explains that, for example, the simple assignment a := b + c;" is executed by using "select $1 + $2", binding in the current values of a and b. This means that the 22012 error illustrated above, and countless other errors can occur, and manifest as an exception, while a plpgsql_block_stmt is executing.

Exception handling in PL/pgSQL

This subsection explains how errors can occur and how they can be handled by PL/pgSQL code.

How run-time errors can occur during the execution of PL/pgSQL code

This discussion concerns only run-time errors. PL/pgSQL syntax errors most commonly occur when one of these SQL statements is issued at top level:

• the create procedure statement
• the create function statement
• the do statement (during its syntax analysis phase)

However, PL/pgSQL supports the execute statement (see the plpgsql_dynamic_sql_stmt rule)—which can be used only in the plpgsql_executable_section or the plpgsql_exception_section. This allows the text of any SQL statement to be defined dynamically and executed from PL/pgSQL code. In such a case, a PL/pgSQL syntax error that a dynamically executed create procedure, create function or do statement causes manifests as a run-time error occurring in the plpgsql_executable_section, or plpgsql_exception_section, from which the DDL is submitted.

An error can therefore occur in any one of these sections:

• the declaration section—in the evaluation of an expression or in the attempt to install the value that the expression computes into the declared variable
• the executable section—during the execution of any plpgsql_executable_stmt
• the exception section—during the execution of any plpgsql_executable_stmt

How to handle run-time errors in PL/pgSQL code

It helps, first, to define the term top-level PL/pgSQL block statement. It's this that defines the implementation for a user-defined PL/pgSQL subprogram and for a do statement. Because the plpgsql_block_stmt is a kind of plpgsql_executable_stmt, the top-level PL/pgSQL block statement may contain arbitrarily many child PL/pgSQL block statements, to arbitrary depth, in a hierarchy.

When an error occurs, it manifests as an exception. An exception is identified (under the covers) by the error code of the error that caused it. The current execution flow is aborted and the search begins for a so-called matching handler (see the plpgsql_handler rule). Here "matching" means that one (or more) of the handler's conditions specifies the error code of the to-be-handled exception. The condition might specify the error code explicitly by using the keyword sqlstate. Or it might specify it indirectly by using the identifier for the name of the exception. See the section PostgreSQL Error Codes in the PostgreSQL documentation. It shows pairs of mappings between an error code and its corresponding name. Condition names are interpreted case-insensitively.

The special catch-all condition, denoted by the bare others keyword, matches every possible error except error 'P0004' (assert_failure) and error '57014' (query_canceled). Notice that it is possible, but generally unwise, to catch these two errors explicitly. See the section Catching "assert_failure" explicitly.

• When an error occurs in the executable section of a PL/pgSQL block statement, the search starts in that block's exception section (if present). If no match is found, then the search continues in the exception section (if present) of the containing PL/pgSQL block statement (if this exists).

• When an error occurs in the declaration section or the exception section of a PL/pgSQL block statement, the search starts in the exception section (if present) of the containing PL/pgSQL block statement (if this exists). Again, if no match is found, then the search continues in the exception section (if present) of the containing PL/pgSQL block statement (if this exists).

• If the search fails all the way up through the (possible) block hierarchy and fails in the top-level PL/pgSQL block statement, then the exception remains unhandled and escapes to the calling environment as the SQL error to which it corresponds.

• In the case that the calling environment is a PL/pgSQL block statement in a separate PL/pgSQL subprogram, or in a do statement, the in-flight exception continues as such and the search starts anew in the PL/pgSQL block statement from which the PL/pgSQL subprogram, or do statement, where the original error occurred was invoked.

• Once a matching handler is found, the exception is defined to be handled (or caught) and the entire effect of the present block statement's executable section is rolled back. See the section Handling an exception implies rolling back the effect of the block statement. The scope of the rollback includes all statically nested block statements, together with the effect of all block statements that might have been called dynamically.

• If the search for a handler fails even up through the top-level PL/pgSQL block statement of the subprogram or do statement that the client code invoked, then the error escapes to the client code.

The executable statements that define the handler are executed in the normal way and then normal execution continues after the end of the plpgsql_block_stmt in whose exception section the handler is found—just as it would as if that block's executable section had ended normally.

Here's a trivial example. Use any convenient sandbox database and a role that has no special attributes and that has create and connect on that database. Do this first:

\set db <the sandbox database>
\set u  <the ordinary role>

Ensure a clean start:

\c :db :u
drop schema if exists s cascade;
create schema s;

Create a trivial function:

create function s.f()
  returns text
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  a int;
begin
  a := 1.0/0.0;
  return a::text;
exception
  when sqlstate '22012' then
    return 'Caught "Error 22012".';
end;
$body$;

Execute it:

select s.f();

The create function statement completes without error. And then select s.f() completes without error, returning this result:

 Caught "Error 22012".

Notice that this handler:

when division_by_zero or sqlstate '2200F' or others then...

Because the others condition will handle any exception, the two preceding conditions, division_by_zero and sqlstate '2200F', are redundant—and therefore will confuse the reader about the code author's intention.

Here's a more elaborate example:

\c :db :u
drop schema if exists s cascade;
create schema s;
create type s.rec as(outcome text, a numeric, b text);

Create a function that will be called by a second function:

create function s.callee(mode in text)
  returns s.rec
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  r s.rec      := ('OK', -1, '-')::s.rec;
  x numeric    := 1.0;
  y varchar(4) := 'abcd';
  z varchar(4);
begin
  begin
    case mode
      when 'division_by_zero' then
        x := x/0.0;
      when 'string_data_right_truncation' then
        z := y||'e'::varchar(4);
      else
        x := x/2.0;
        z := 'Nice';
    end case;
  exception
    when division_by_zero then
      r.outcome := 'Caught "division_by_zero" in "s.callee()".';
      return r;
  end;
  r.a := x::text;
  r.b := z::text;
  return r;
end;
$body$;

Create a function to call the callee function:

create function s.caller(mode in text)
  returns s.rec
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  r s.rec := ('OK', -1, '-')::s.rec;
begin
  r := s.callee(mode);
  return r;
exception
  when string_data_right_truncation then
    r.outcome :=  'Caught "string_data_right_truncation" in "s.caller()".';
    return r;
end;
$body$;

Invoke the caller without provoking an error:

with c(rec) as (select s.caller('benign'))
select (c.rec).outcome, (c.rec).a::text, (c.rec).b from c;

This is the result:

 outcome |           a            |  b   
---------+------------------------+------
 OK      | 0.50000000000000000000 | Nice

Now provoke an error that will be handled in the callee:

with c(rec) as (select s.caller('division_by_zero'))
select (c.rec).outcome, (c.rec).a::text, (c.rec).b from c;

This is the result:

                  outcome                   | a  | b 
--------------------------------------------+----+---
 Caught "division_by_zero" in "s.callee()". | -1 | -

And now provoke an error for which there's no handler in the callee function and which escapes to the caller to be handled:

with c(rec) as (select s.caller('string_data_right_truncation'))
select (c.rec).outcome, (c.rec).a::text, (c.rec).b from c;

This is the result:

                        outcome                         | a  | b 
--------------------------------------------------------+----+---
 Caught "string_data_right_truncation" in "s.caller()". | -1 | -

Handling an exception implies rolling back the effect of the block statement

When the point of execution enters a PL/pgSQL block statement, at any level of nesting, the runtime system detects whether or not it has an exception section—and if it does (and only in that case), then it automatically creates an internally named savepoint at that moment.

• If an error occurs in the block's executable section, or if an unhandled error escapes from a subprogram or a do statement that it invokes, so that the point of execution enters the exception section, and if a matching handler is found, then the runtime system issues a rollback to that internally named savepoint—and the handler code is executed. Then normal execution continues after the present block statement's end.

• But if no error occurs in the block's executable section, or if an error occurs but no matching handler is found, then the runtime system releases that internally named savepoint—and the search for a handler continues as described in the previous section.

• Of course, if the search for a handler fails all the way up through the top-level PL/pgSQL block statement that defines the subprogram or do statement that was the object of the top-level server call, then the entire effect of the server call is rolled back and the error escapes to the calling client program.

How to get information about the error

The dedicated plpgsql_get_stacked_diagnostics_stmt (one of the kinds of plpgsql_basic_stmt), gives information about the error that the current handler caught.

• Grammar
• Diagram

plpgsql_get_stacked_diagnostics_stmt ::= GET STACKED DIAGNOSTICS 
                                         plpgsql_stacked_diagnostics_item 
                                         [ , ... ]

plpgsql_stacked_diagnostics_item ::= { variable | formal_arg } 
                                     { := | = } 
                                     plpgsql_stacked_diagnostics_item_name

plpgsql_stacked_diagnostics_item_name ::= RETURNED_SQLSTATE
                                          | MESSAGE_TEXT
                                          | PG_EXCEPTION_DETAIL
                                          | PG_EXCEPTION_HINT
                                          | SCHEMA_NAME
                                          | TABLE_NAME
                                          | COLUMN_NAME
                                          | PG_DATATYPE_NAME
                                          | CONSTRAINT_NAME
                                          | PG_EXCEPTION_CONTEXT

The attempt to invoke it in a block statement's executable section causes the 0Z002 run-time error:

GET STACKED DIAGNOSTICS cannot be used outside an exception handler

Each diagnostics item is identified by a keyword that specifies a status value that will be assigned to the specified target variable (or subprogram argument). The available items are listed in the following table. Each status value has the data type text.

Different errors define values for different subsets of the available items. When a value for a particular item isn't defined, the get stacked diagnostics statement assigns the empty string (rather than null) to the specified target variable. This means that you can declare the target variables with a not null constraint.

Here is an example. Ensure a clean start and create some supporting objects:

\c :db :u
drop schema if exists s cascade;
create schema s;
create domain s.d as int
constraint d_ok check(value between 1 and 5);

create table s.t(
  k  int primary key,
  c1 varchar(4) constraint t_c1_nn not null,
  c2 s.d,
  constraint t_c2_nn check(c2 is not null));

insert into s.t(k, c1, c2) values(1, 'dog', 3);

create type s.diagnostics as(
  returned_sqlstate    text,
  message_text         text,
  pg_exception_detail  text,
  pg_exception_hint    text,
  schema_name          text,
  table_name           text,
  column_name          text,
  pg_datatype_name     text,
  constraint_name      text,
  pg_exception_context text
  );

create function s.show(t in text)
  returns text
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  return case t
    when '' then '-'
    else         t
  end;
end;
$body$;

Now create a function s.f() that will provoke one of a few different contrived errors according to what its input actual argument specifies. One of these is a user-defined error that is caused by the raise statement. Notice that the exception section defines just a single others handler. This will populate a value of the composite type s.diagnostics that has an attribute for every single available diagnostics item. The function returns the s.diagnostics value so that a caller table function can display each value that get stacked diagnostics populated.

create function s.f(mode in text)
  returns s.diagnostics
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  diags s.diagnostics;
  err   text not null := '';
begin
  case mode
    when 'unique_violation' then
      insert into s.t(k, c1, c2) values(1, 'cat', 4);
    when 'not_null_violation' then
      update s.t set c1 = null where k = 1;
    when 'string_data_right_truncation' then
      update s.t set c1 = 'mouse' where k = 1;
    when 'check_violation 1' then
      update s.t set c2 = 7 where k = 1;
    when 'check_violation 2' then
      update s.t set c2 = null where k = 1;

    when 'raise_exception' then
      raise exception using
        errcode    := 'YB573',
        message    := 'It went wrong',
        detail     := 'Caused by "raise exception".',
        hint       := 'Do better next time.',
        schema     := 'n/a',
        table      := 'n/a',
        column     := 'n/a',
        datatype   := 'n/a',
        constraint := 'n/a' ;

    else -- No error
      update s.t set c1 = 'cat', c2 = 2 where k = 1;
  end case;

  diags.returned_sqlstate := 'No error';
  return diags;
exception
  when others then
    get stacked diagnostics
      diags.returned_sqlstate    := returned_sqlstate,
      diags.message_text         := message_text,
      diags.pg_exception_detail  := pg_exception_detail,
      diags.pg_exception_hint    := pg_exception_hint,
      diags.schema_name          := schema_name,
      diags.table_name           := table_name,
      diags.column_name          := column_name,
      diags.pg_datatype_name     := pg_datatype_name,
      diags.constraint_name      := constraint_name,
      diags.pg_exception_context := pg_exception_context;

    err := case diags.returned_sqlstate
             when '23505' then 'unique_violation'
             when '23502' then 'not_null_violation'
             when '22001' then 'string_data_right_truncation'
             when '23514' then 'check_violation'
             else              diags.returned_sqlstate
           end;
    diags.returned_sqlstate := err;
    return diags;
end;
$body$;

Now create the table function s.f_outcome() to invoke s.f() and to report the value of each of the attributes of the s.diagnostics value that it returns:

create function s.f_outcome(mode in text)
  returns table(z text)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  diags s.diagnostics := s.f(mode);
begin
  z := rpad('sqlstate:',    13)||s.show(diags.returned_sqlstate);          return next;

  if diags.returned_sqlstate <> 'No error' then
    z := rpad('message:',     13)||s.show(diags.message_text);             return next;
    z := rpad('detail:',      13)||s.show(diags.pg_exception_detail);      return next;
    z := rpad('hint:',        13)||s.show(diags.pg_exception_hint);        return next;
    z := rpad('schema:',      13)||s.show(diags.schema_name);              return next;
    z := rpad('table:',       13)||s.show(diags.table_name);               return next;
    z := rpad('column:',      13)||s.show(diags.column_name);              return next;
    z := rpad('datatype:',    13)||s.show(diags.pg_datatype_name);         return next;
    z := rpad('constraint:',  13)||s.show(diags.constraint_name);          return next;

    z := '';                                                               return next;
    z := 'context:';                                                       return next;
    z := s.show(diags.pg_exception_context);                               return next;
  end if;
end;
$body$;

Now invoke it with, in turn, each of the actual values of the mode formal argument that it was designed to respond to.

1. First, cause no error:

select s.f_outcome('benign');

It executes this statement:

update s.t set c1 = 'cat', c2 = 2 where k = 1;

This is the result:

 sqlstate:    No error

2. Next, cause an error:

select s.f_outcome('unique_violation');

It executes this statement:

insert into s.t(k, c1, c2) values(1, 'cat', 4);

This attempts to insert a row with the same value for the primary key column, k that the existing row has. This is the result:

 sqlstate:    unique_violation
 message:     duplicate key value violates unique constraint "t_pkey"
 detail:      -
 hint:        -
 schema:      -
 table:       -
 column:      -
 datatype:    -
 constraint:  -
 
 context:
 PL/pgSQL function s.f(text) line 5 at statement block                                          +
 PL/pgSQL function s.f_outcome(text) line 4 during statement block local variable initialization

Notice the empty values. The error definitely occurs in the context of a specific table in a specific schema. And it's due to the violation of a specific constraint. So the implementation (inherited directly from PostgreSQL) could have filled in these values. However, because a primary key constraint is, in general, defined for a column list rather than for a single column, there can be no general way to fill in the column name and the data type.

You'll notice that, often, fields, that might have been filled in, are not. Such cases could, presumably, be the subject of enhancement requests for vanilla PostgreSQL.

3. Next, cause another error:

select s.f_outcome('not_null_violation');

It executes this statement:

update s.t set c1 = null where k = 1;

This attempts to update the existing row to set c1, which is declared with a not null column constraint to null. This is the result:

 sqlstate:    not_null_violation
 message:     null value in column "c1" violates not-null constraint
 detail:      Failing row contains (1, null, 2).
 hint:        -
 schema:      s
 table:       t
 column:      c1
 datatype:    -
 constraint:  -
 
 context:
 SQL statement "update s.t set c1 = null where k = 1"                                           +
 PL/pgSQL function s.f(text) line 10 at SQL statement                                           +
 PL/pgSQL function s.f_outcome(text) line 4 during statement block local variable initialization

Notice that here the names of the schema, the table, and the column are filled in here; but the data type is not (even though it is known). Strangely, the not null column constraint t_c1_nn is not filled in either. This is probably because it isn't listed in the pg_constraint catalog table. Try this:

select conname
from pg_constraint
where connamespace = (select oid from pg_namespace where nspname = 's');

This is the result:

 conname 
---------
 d_ok
 t_c2_nn
 t_pkey

Notice that t_c2_nn, which is listed, is defined as a table constraint rather than as a column constraint.

4. Next, cause another error:

select s.f_outcome('string_data_right_truncation');

It executes this statement:

update s.t set c1 = 'mouse' where k = 1;

This attempts to update the existing row to set c1, which is declared as varchar(4) with a five-character value. This is the result:

sqlstate:    string_data_right_truncation
 message:     value too long for type character varying(4)
 detail:      -
 hint:        -
 schema:      -
 table:       -
 column:      -
 datatype:    -
 constraint:  -
 
 context:
 SQL statement "update s.t set c1 = 'mouse' where k = 1"                                        +
 PL/pgSQL function s.f(text) line 12 at SQL statement                                           +
 PL/pgSQL function s.f_outcome(text) line 4 during statement block local variable initialization

Here, too, the names of the schema, the table, the column, and its data type are left blank. (The implied constraint that the varchar(4) declaration imposes is anonymous.)

5. Next, cause another error:

select s.f_outcome('check_violation 1');

It executes this statement:

update s.t set c2 = 7 where k = 1;

This attempts to update the existing row to set c2, which is declared using the s.d domain—and this has an explicitly defined constraint s.d_ok that checks that the value lies between 1 and 5. This is the result:

 sqlstate:    check_violation
 message:     value for domain s.d violates check constraint "d_ok"
 detail:      -
 hint:        -
 schema:      s
 table:       -
 column:      -
 datatype:    d
 constraint:  d_ok
 
 context:
 SQL statement "update s.t set c2 = 7 where k = 1"                                              +
 PL/pgSQL function s.f(text) line 14 at SQL statement                                           +
 PL/pgSQL function s.f_outcome(text) line 4 during statement block local variable initialization

Here, the names of the data type (i.e. the domain name and the schema in which it lives) and the name of the domain's constraint are filled out. But the names of table and the column are left blank.

5. Finally, cause another error:

select s.f_outcome('check_violation 2');

It executes this statement:

update s.t set c2 = null where k = 1;

This attempts to update the existing row to set c2 to null. But the table s.t has a table-level constraint t_c2_nn check(c2 is not null). This is the result:

 sqlstate:    check_violation
 message:     new row for relation "t" violates check constraint "t_c2_nn"
 detail:      Failing row contains (1, cat, null).
 hint:        -
 schema:      s
 table:       t
 column:      -
 datatype:    -
 constraint:  t_c2_nn
 
 context:
 SQL statement "update s.t set c2 = null where k = 1"                                           +
 PL/pgSQL function s.f(text) line 16 at SQL statement                                           +
 PL/pgSQL function s.f_outcome(text) line 4 during statement block local variable initialization

Here, the names of the schema, the table, and the table-level constraint are filled out. But the names of the column and the data type are left blank—presumably because, in general, a table level constraint is not limited to constrain the values for just a single column.