PL/pgSQL
Error Handling
17
Copyright
© Postgres Professional, 2017–2025
Authors Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by Liudmila Mantrova and Alexander Meleshko
Photo by Oleg Bartunov (Tukuche peak, Nepal)
Use of course materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.com
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
Topics
Error handling in PL/pgSQL blocks
Error names and codes
Choosing an error handler
Error handling overhead
3
Error handling in a block
Error handling is performed if there is an EXCEPTION section
Changes roll 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 specifics related to transactions,
or course).
A savepoint is implicitly set at the start of every block containing an
EXCEPTION section. Before an error is processed, all changes are rolled
back to the savepoint and all locks are removed.
Because of the savepoint, COMMIT and ROLLBACK commands cannot be
used in procedures with EXCEPTION. But although SAVEPOINT and
ROLLBACK TO SAVEPOINT commands are not supported by PL/pgSQL,
you can still use savepoints and rollbacks to savepoints both in functions
and procedures implicitly.
https://postgrespro.com/docs/postgresql/17/plpgsql-control-structures#PLP
GSQL-ERROR-TRAPPING
5
Error names and codes
Error info
error name
five-character error code
additional info: a short message, a detailed message, a hint, names of objects
related to this error
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 a two-level hierarchy of sorts. 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 a 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 catch any
errors (except for the fatal ones).
Apart from the name and code, each error can provide additional debug
information: a short error message, a detailed message, and a hint.
All errors are described in documentation in Appendix A:
https://postgrespro.com/docs/postgresql/17/errcodes-appendix
Errors can be not only trapped, but also raised programmatically.
https://postgrespro.com/docs/postgresql/17/plpgsql-errors-and-messages
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
it is not defined statically, but depends on how the program executes
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 the error goes through the whole nested structure and does not find an
appropriate handler, it goes further up to the level of the routine that has
called the outermost block. Therefore, you have to analyze the call stack to
determine the order in which different error handlers will be applied.
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),
The error is reported to the client that has initiated this operation in the
database. The client cannot do anything about the cause of the error at
this point: 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 from there. For
example, psql will display the error message and all the debugging
information available. An end-user client may display a generic message
like “contact your system administrator”.
9
Overhead
Any block with an EXCEPTION section is executed slower
because of setting an implicit savepoint
Additional costs are incurred in case of an error
because of the rollback to the savepoint
Error handling can and should be used, but not overused
PL/pgSQL is an interpreted language that uses SQL to compute expressions
anyway
the speed is more than enough for most tasks
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 occurs, the rollback to the savepoint increases the
overhead even more.
So if there is a simple way to avoid exception handling, it’s better to do
without it; 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 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
Takeaways
The search for an error handler is performed “inside out”:
starting from the innermost 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. Attempting to put in the same author several times when adding
a book causes an error.
Modify the add_book function: catch the unique constraint
violation error and produce an error with a meaningful message
instead.
Try it out in the application.
1. To determine the name of the error that has to be caught, 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 position in the code.
(In a real environment, uniq ue constraint violations should not be handled
either: it is better to forbid entering the same author twice at the application
level.)