PL/pgSQL
Error Handling
12
Copyright
© Postgres Professional, 2017–2021
Authors: Egor Rogov, Pavel Luzanov
Translated by Liudmila Mantrova
Usage of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed on an unrestricted basis. Commercial use is only possible with prior
written permission of Postgres Professional company. Modification of course
materials is forbidden.
Contact Us
Please send your feedback to: edu@postgrespro.ru
Disclaimer
In no event shall Postgres Professional company be liable for any damages
or loss, including loss of profits, that arise from direct or indirect, special or
incidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Agenda
Error handling in PL/pgSQL blocks
Error names and codes
Choosing an error handler
Error handling overhead
3
Handling Errors in a Block
Error handling is performed if there is an EXCEPTION section
Changes are rolled back to the savepoint at the beginning of the
block
an implicit savepoint is set if the block contains an EXCEPTION section
If there is a handler that matches the error
error handler commands are executed
the block completes successfully
If there is no suitable handler
the block completes with an error
If a run-time error occurs within a block, the program (block, function) is
usually aborted, and the current transaction enters the failure mode:
it cannot be committed and can only be rolled back.
But an error can be caught and processed. It can be done by extending the
block with an additional EXCEPTION section, which lists error conditions
and provides operators to handle each of them.
In general, EXCEPTION is similar to the try-catch construct available in
some programming languages (except for transaction specifics, or course).
Before an error is processed, all changes are rolled back to the savepoint
that is implicitly set at the beginning of each block containing the
EXCEPTION section. That’s why it is forbidden to use COMMIT and
ROLLBACK commands in such blocks.
But although SAVEPOINT and ROLLBACK TO SAVEPOINT commands are
not supported by PL/pgSQL, you can still implicitly use savepoints and
rollbacks to savepoints both in functions and procedures.
5
Error Names and Codes
Information about an error
error name
five-character error code
additional information: a short message, a detailed message, a hint,
names of objects related to this error
A two-level hierarchy
XX001 – data_corrupted
XX002 – index_corrupted
XX000 – internal_error
P0001 – raise_exception
P0003 – too_many_rows
P0002 – no_data_found
P0004 – assert_failure
P0000 – plpgsql_error
Each possible error has a name and a code (a five-character string). WHEN
clauses accept both error names and error codes.
All errors are classified into some sort of a two-level hierarchy. Each error
class has a code that ends with three zeros; it corresponds to any error with
the same first two characters in its code.
For example, the code 23000 defines the class that includes all errors
dealing with violations of integrity constraints (such as 23502, which stands
for not-null constraint violation, or 23505, which indicates unique constraint
violation).
Thus, apart from regular errors, you can specify the whole error class by its
name or code. Besides, you can use a special name others to trap any error
(except for the fatal ones).
Apart from the name and code, each error can provide additional
information to facilitate comprehension: a short error message, a detailed
message, and a hint.
All errors are described in documentation in Appendix A:
Errors can be not only trapped, but also raised programmatically.
7
Choosing a Handler
An unhandled error is sent one level up
into the outer PL/pgSQL block, if available
into the calling routine, if available
The search path of a handler is determined by the call stack
i.e., it is not defined statically, it depends on the program execution
An unhandled error is passed to the client
the transaction enters the failure mode and has to be rolled back by the client
the error is registered in the server log
If none of the conditions listed in the EXCEPTION section is triggered, the
error goes one level up.
If an error has occurred in the inner block of a nested structure, the server
will search for a handler in the outer block. If there is no suitable handler
either, the whole outer block will be treated as failed, while the error will be
passed to the next nesting level, and so on.
If we have gone through the whole nested structure and have not found an
appropriate error handler, the error goes further up to the level of the routine
that has called this block. So you have to analyze the call stack to
determine the order in which different error handlers will be tried.
If none of the available error handlers is triggered:
- the error message usually gets into the server log (the exact behavior
depends on the server settings; see lecture “PL/pgSQL. Debugging”);
- the error is reported to the client that has initiated this operation in the
database. The client has to face the fact: the transaction enters the failure
mode, and it can only be rolled back.
It is up to the client to choose how to handle the error. For example, psql will
display the error message and all the debugging information available. An
end-user client may display a classic message like “contact your system
administrator”.
9
Overhead
Any block with the EXCEPTION section is executed slower
because of setting an implicit savepoint
Additional costs are incurred in case of an error
because of a rollback to the savepoint
Error handling can and should be used, but not overused
anyway, PL/pgSQL is an interpreted language that uses SQL to compute
expressions
for most tasks, its speed is more than enough
performance issues are usually related to queries, not to PL/pgSQL code
The mere inclusion of an EXCEPTION section already incurs overhead
because it requires setting an implicit savepoint at the beginning of the
block. If an error really occurs, the rollback to a savepoint increases the
overhead even more.
So if there is a simple way to avoid exception handling, it’s better to do
without it. For example, you should not base your application logic on
“exception juggling.”
However, if error handing is really required, you should use it without doubt:
errors can and must be handled regardless of the overhead. First, the
PL/pgSQL language itself is quite slow because of interpreting instructions
and constantly calling SQL to compute expressions. Second, its speed is
usually still quite adequate. Yes, you can create a faster implementation in
C, but what’s the point? And third, the main performance issues are usually
caused by bad query plans that affect query speed, not by the execution
speed of procedural code (for details, see the QPT course that deals with
query performance tuning).
But if there is an alternative that is both simpler and faster, it should
certainly be preferred.
11
Summary
The search for an error handler is performed “inside out,” i.e.,
starting from the most inner block in the nested structure and
going up the call stack
An implicit savepoint is set at the beginning of the block that
contains EXCEPTION; if an error occurs, a rollback to this
savepoint is performed
An unhandled error aborts the transaction; the error message
is passed to the client and registered in the server log
Error handling incurs overhead
12
Practice
1. Specifying one and the same author several times when adding
a book causes an error.
Change the add_book function: trap the unique constraint
violation error and force an error with a meaningful message
instead.
Test these changes in the application.
Task 1. To determine the name of the error that has to be trapped, catch all
errors (WHEN OTHERS) and display the required information (by raising
another error with the corresponding text).
Then remember to replace WITH OTHERS with a specific error: let all other
error types be handled at a higher level if there is no opportunity to do
anything useful in this particular place of the code.
(In real life, unique constraint violations should not be handled either: it is
better to forbid entering the same author twice at the application level.)
13
Practice
1. Some languages use the construct try … catch … finally …,
where try corresponds to BEGIN, catch corresponds to
EXCEPTION, and the operators located in the finally block are
always triggered regardless of whether an exception has occurred
and whether it has been processed by the catch block. Find
a way to achieve a similar effect in PL/pgSQL.
2. Compare the call stacks returned by GET STACKED DIAGNOSTICS
with pg_exception_context
and GET [CURRENT] DIAGNOSTICS with pg_context.
3. Create the getstack function that returns the current call stack
as a string array. The getstack function itself must not be a part
of the stack.
Task 1. The easiest way to do it is to simply repeat finally operators in
several places. But you should try to come up with a solution that avoids
code duplication.
Task 2. Take a look at documentation first:
