PostgreSQL message levels

When you start to write business logic in the database by using triggers or functions/procedures you usually want to report messages to the user that runs your code or you want to include some debugging output for your own. In Oracle a lot of people use the dbms_output package to return simple messages to the user’s screen. In PostgreSQL you can do the same but of course the way it is implemented is not the same. There are basically two parameters that control how much output is returned to the client or to the server’s log file: client_min_messages and log_min_messages.

Let’s start with client_min_messages: The default value of this parameter is ‘NOTICE’ and these are the valid values you cant set:

• DEBUG5
• DEBUG4
• DEBUG3
• DEBUG2
• DEBUG1
• LOG
• NOTICE
• WARNING
• ERROR

A given level excludes all the lower levels, e.g. “LOG” would exclude all the “DEBUG*” levels and the default “NOTICE” will exclude “LOG” as well. The DEBUG* levels are usually not required and if you check the PostgreSQL source code you’ll notice that the highest level of information (DEBUG5) is not used that often while lower levels are used more often:

postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG5" * | grep -v sgml | wc -l
20
postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG4" * | grep -v sgml | wc -l
78
postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG3" * | grep -v sgml | wc -l
64
postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG2" * | grep -v sgml | wc -l
236
postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG1" * | grep -v sgml | wc -l
221

Setting the highest level would give a lot of information even for basic tasks:

postgres=# set client_min_messages = 'DEBUG5';
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
postgres=# create table tt1 ( a int );
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 559/1/1
CREATE TABLE
postgres=# create index ii1 on tt1 (a);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  building index "ii1" on table "tt1" serially
DEBUG:  index "ii1" can safely use deduplication
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 560/1/2
CREATE INDEX

Lowering that to “DEBUG1” already reduces the amount of messages:

postgres=# set client_min_messages = 'DEBUG1';
SET
postgres=# create table tt2 ( a int );
CREATE TABLE
postgres=# create index ii2 on tt2 (a);
DEBUG:  building index "ii2" on table "tt2" serially
DEBUG:  index "ii2" can safely use deduplication
CREATE INDEX
postgres=# 

You can use most of the levels to control how much information is given back by your code as well. Let’s assume we have a function like this:

postgres=# create or replace function f_test_msg () returns void 
postgres-# as
postgres-# $$
postgres$# declare
postgres$# begin
postgres$#     raise debug 'This is a DEBUG message';
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION

With the default setting of ‘NOTICE’ for client_min_messages you would not see any output when you execute the function:

postgres=# show client_min_messages;
 client_min_messages 
---------------------
 notice
(1 row)

postgres=# select f_test_msg();
 f_test_msg 
------------
 
(1 row)

Setting client_min_messages to the appropriate level will give you the output:

postgres=# set client_min_messages = 'DEBUG';
SET
postgres=# select f_test_msg();
DEBUG:  This is a DEBUG message
 f_test_msg 
------------
 
(1 row)

postgres=# 

It does not really matter to with “DEBUG” level you set the parameter, you’ll get the “raise debug” for all those levels:

postgres=# set client_min_messages = 'DEBUG1';
SET
postgres=# select f_test_msg();
DEBUG:  This is a DEBUG message
 f_test_msg 
------------
 
(1 row)

postgres=# set client_min_messages = 'DEBUG2';
SET
postgres=# select f_test_msg();
DEBUG:  This is a DEBUG message
 f_test_msg 
------------
 
(1 row)

postgres=# 

In the documentation of RAISE the only DEBUG level is “DEBUG” anyway. Using this method you can easily control the amount of messages your code will return, e.g.:

postgres=# create or replace function f_test () returns void 
postgres-# as
postgres-# $$
postgres$# declare
postgres$# begin
postgres$#     raise debug 'This is a DEBUG message';
postgres$#     raise log 'This is a LOG message';
postgres$#     raise notice 'This is an NOTICE message';
postgres$#     raise warning 'This is a WARNING message';
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION

Depending on you current setting of client_min_messages you’ll get more or less messages:

postgres=# set client_min_messages = 'WARNING';
SET
postgres=# select f_test();
WARNING:  This is a WARNING message
 f_test 
--------
 
(1 row)

postgres=# set client_min_messages = 'NOTICE';
SET
postgres=# select f_test();
NOTICE:  This is an NOTICE message
WARNING:  This is a WARNING message
 f_test 
--------
 
(1 row)

postgres=# set client_min_messages = 'LOG';
SET
postgres=# select f_test();
LOG:  This is a LOG message
NOTICE:  This is an NOTICE message
WARNING:  This is a WARNING message
 f_test 
--------
 
(1 row)

postgres=# set client_min_messages = 'DEBUG';
SET
postgres=# select f_test();
DEBUG:  This is a DEBUG message
LOG:  This is a LOG message
NOTICE:  This is an NOTICE message
WARNING:  This is a WARNING message
 f_test 
--------
 
(1 row)

Use that wisely and you will easily be able to debug your code by just setting the correct level for the information you want to get.

log_min_messages on the other side controls how much information is written to PostgreSQL’s log file. There are two more levels you can set than for client_min_messages but the logic is the same:

• DEBUG5
• DEBUG4
• DEBUG3
• DEBUG2
• DEBUG1
• INFO
• NOTICE
• WARNING
• ERROR
• LOG
• FATAL

The default is “WARNING” so you might want to lower that if you need more information in the log file or you need to collect debugging information.