The PL/pgSQL "assert" statement

Syntax

Grammar
Diagram

plpgsql_assert_stmt ::= ASSERT boolean_expression 
                        [ , text_expression ]

plpgsql_assert_stmt

Semantics

The plpgsql_assert_stmt evaluates its defining boolean_expression. If the result is true, then the point of execution moves silently to the next executable statement. But if the result is false, then the 'P0004' error (assert_failure) is caused. If the optional text_expression is omitted, then the system supplied error message "assertion failed" is used—in whatever national language the lc_messages run-time parameter specifies. (See the PostgreSQL documentation section Locale Support.) If text_expression is defined, then this text is used as the error message.

If an error occurs while evaluating the boolean_expression or the text_expression, then this is reported as a normal error. The text_expression is evaluated only when the assertion fails.

The 'P0004' ('assert_failure') error and the '57014' ('query_canceled') error are not caught by the 'others' handler.

This is a deliberate design. The idea is that these two errors should be unstoppable so that when either of them occurs, the present top-level server call from the client will be aborted. In particular, the assert statement is intended for detecting program bugs (i.e. conditions that you know are impossible in the absence of bugs). Use the raise statement when you detect a regrettable, but nevertheless possible error like a new user-supplied value that is meant to be unique but turns out to collide with an existing value.

Testing of assertions can be enabled or disabled via the run-time parameter plpgsql.check_asserts, with legal values on (the default) and off.

It is possible, but generally unwise, to catch these two errors explicitly. However, if you implement a scheme to log errors in, for example, a dedicated table then you might want to catch each of these errors, log them, and then re-raise the error. See the raise statement section.

Catching "assert_failure" explicitly

First, try this simple test to demonstrate the "unstoppable by default" behavior:

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

create function s.f(mode in text)
  returns text
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  a    int  not null := 0;
  v    text not null := '';
  err  text not null := '';
begin
  case mode
    when 'division_by_zero' then
      a := 1.0/0.0;
    when 'null_value_not_allowed' then
      v := null;
    when 'assert' then
      assert (1 = 2);
    end case;
  return a::text;
exception
  when division_by_zero then
    return 'Caught "division_by_zero".';
  when others then
    get stacked diagnostics err := returned_sqlstate;
    return 'Caught "Error '||err||'"';
end;
$body$;

Test it thus. First provoke an error for which there is an explicit handler:

select s.f('division_by_zero');

The explicit when division_by_zero handler catches this, bringing this result:

 Caught "division_by_zero".

Now provoke an error for which there is no explicit handler:

select s.f('null_value_not_allowed');

The catch-all others handler catches this, bringing this result:

Caught "Error 22004"

Now cause an assertion failure:

select s.f('assert');

This is the result:

ERROR:  P0004: assertion failed
CONTEXT:  PL/pgSQL function s.f(text) line 13 at ASSERT
LOCATION:  exec_stmt_assert, pl_exec.c:3897

Now modify the implementation of s.f()'s exception section by adding an explicit handler for assert_failure. Here, raise info is used to emulate inserting all of the information that get stacked diagnostics provides into a table for subsequent off-line analysis by Support.

create or replace function s.f(mode in text)
  returns text
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  a    int  not null := 0;
  v    text not null := '';
  err  text not null := '';
begin
  case mode
    when 'division_by_zero' then
      a := 1.0/0.0;
    when 'null_value_not_allowed' then
      v := null;
    when 'assert' then
      assert (1 = 2);
    end case;
  return a::text;
exception
  when division_by_zero then
    return 'Caught "division_by_zero".';
  -- Generally unwise practice. But the explicit "raise" makes this acceptable.
  when assert_failure then
    raise info 'Caught "assert_failure".';
    raise;

  when others then
    get stacked diagnostics err := returned_sqlstate;
    return 'Caught "Error '||err||'"';
end;
$body$;

Cause the same assertion failure now:

\set VERBOSITY default
select s.f('assert');

This is the new result:

INFO:  Caught "assert_failure".
ERROR:  assertion failed
CONTEXT:  PL/pgSQL function s.f(text) line 13 at ASSERT