The strange case of the EXCEPTION block

When in a pl/pgsql function there is an exception then the function stops the execution and returns an error. When this happens all the changes made are rolled back.

It’s always possible to manage the error at application level, however there are some cases where managing the exception inside the function it may be a sensible choice. And pl/pgsql have a nice way to do that. The EXCEPTION block.

However handling the exception inside a function is not just a cosmetic thing. The way the excepion is handled have implications that may cause issues.

Trapping exceptions

In a function’s body the exception comes always after all the code in the block started with BEGIN and before the corresponding END;. It’s possible to define multiple BEGIN…EXCEPTION…END for managing different kind of exceptions.

When an error occurs in the code between the BEGIN…EXCEPTION then the execution stops and jumps to the error handlers defined into the EXCEPTION block.

The handlers are defined in the form of WHEN…THEN. After the WHEN it should follow the error code name to handle or the catch all keyword OTHERS.

For example the function

CREATE OR REPLACE FUNCTION fn_pl_division(p_dividend numeric, p_divisor numeric)
RETURNS numeric
AS
$BODY$
  DECLARE
    v_out   numeric;
  BEGIN
    BEGIN
      v_out:=(p_dividend/p_divisor)::numeric;
    EXCEPTION
      WHEN DIVISION_BY_ZERO
      THEN 
	    v_out:=NULL;
    END;
    RETURN v_out;
  END;
$BODY$
LANGUAGE plpgsql;

Handles only the DIVISION_BY_ZERO exception. Should a different error occur the function will exit with an error.

The PostgreSQL online manual have a warning for functions using EXCEPTION. They are more expensive than functions without it. However if using in such functions the DML, like INSERT or UPDATE ,the execution cost may be the last of the problems.

What happens in Functions stays in Functions

The PostgreSQL functions are single transaction, this is a good and a bad thing. It’s good because they consume just one XID whatever happens inside the function.

It’s a bad thing because there is no transaction control within the function’s body and if the function hits an error the the entire transaction is rolled back.

Now for the sake of clarity let’s create a table with one field of type integer enforcing a unique key on that field.

CREATE TABLE t_test 
(
  id integer
);

CREATE UNIQUE INDEX idx_u_t_test_id 
    ON t_test(id);

Then we create a function inserting one record at a time over a FOR LOOP of 1000.

CREATE OR REPLACE FUNCTION fn_loop_noexception()
RETURNS VOID AS
$BODY$
  DECLARE
    v_loop integer;
  BEGIN
    FOR v_loop IN 1..1000
    LOOP
      INSERT INTO t_test(id) VALUES (v_loop);
    END LOOP;
  END;
$BODY$
LANGUAGE plpgsql;

We run a VACUUM FREEZE on our datatabase in order to start with age of the datfroxenxid set to 0.

VACUUM FREEZE;
SELECT datname,age(datfrozenxid) FROM pg_database  WHERE datname=current_database();
 datname | age 
---------+-----
 test    |   0
(1 row)

When executing the function we add 1000 records to the table. Checking the XID we find that the age increased by one.

SELECT datname,age(datfrozenxid) FROM pg_database  WHERE datname=current_database();
 datname | age 
---------+-----
 test    |   1
(1 row)

SELECT count(*) FROM t_test ;
 count 
-------
  1000
(1 row)

When running the function a second time we get an error caused by the unique index.

test=# SELECT fn_loop_noexception();
2020-01-30 15:58:09.190 GMT [4456] ERROR:  duplicate key value violates unique constraint "idx_u_t_test_id"
2020-01-30 15:58:09.190 GMT [4456] DETAIL:  Key (id)=(1) already exists.
2020-01-30 15:58:09.190 GMT [4456] CONTEXT:  SQL statement "INSERT INTO t_test(id) VALUES (v_loop)"
	PL/pgSQL function fn_loop_noexception() line 7 at SQL statement
2020-01-30 15:58:09.190 GMT [4456] STATEMENT:  SELECT fn_loop_noexception();
ERROR:  duplicate key value violates unique constraint "idx_u_t_test_id"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  SQL statement "INSERT INTO t_test(id) VALUES (v_loop)"
PL/pgSQL function fn_loop_noexception() line 7 at SQL statement
test=# SELECT datname,age(datfrozenxid) FROM pg_database  WHERE datname=current_database();
 datname | age 
---------+-----
 test    |   2
(1 row)

The function’s failure consumes an XID anyway.

So we want to handle the exception, we write another function which doesn’t fails in case of UNIQUE violation.

CREATE OR REPLACE FUNCTION fn_loop_withexception()
RETURNS VOID AS
$BODY$
  DECLARE
    v_loop integer;
  BEGIN
    FOR v_loop IN 1..1000
    LOOP
      BEGIN
	INSERT INTO t_test(id) VALUES (v_loop);
      EXCEPTION
	WHEN UNIQUE_VIOLATION 
	THEN
	    NULL;
      END;
    END LOOP;
  END;
$BODY$
LANGUAGE plpgsql;

Let’s truncate the table run another vacuum freze and execute the new function.

TRUNCATE TABLE t_test ;
TRUNCATE TABLE

VACUUM FREEZE;
VACUUM
test=# SELECT datname,age(datfrozenxid) FROM pg_database  WHERE datname=current_database();
 datname | age 
---------+-----
 test    |   0
(1 row)

SELECT fn_loop_withexception();
 fn_loop_withexception 
-----------------------
 
(1 row)

SELECT datname,age(datfrozenxid) FROM pg_database  WHERE datname=current_database();
 datname | age  
---------+------
 test    | 1001
(1 row)

Well, this is a kind of shock. Whether the exception is handled or notthe DML consumes an XID every time it’s executed. All the XID consumed internally are then added to the XID consumed by the function as usual.

This kind of behaviour that I’m struggling to find documention is what caused the one and only emergency shutdown to prevent the XID wraparound I had to deal in my career.

When the cluster goes in emergency shutdown stops accepting new queries and the only way to resume the operations is to stop the cluster, start the postmaster in single user mode and run a VACUUM FREEZE on the relations at risk of XID wraparound.

Wrap up

Knowing how things work is always important. Reading the manal is always good but sometimes the discoveries happen through failures.

Therefore it is very important to be prepared for managing emergency situations.

Adding the behaviour of the exception block for DML in the PostgreSQL documentation may be a nice thing.