RAISE Statement in PostgreSQL to debug your query messages

RAISE statement in PostgreSQL is used to report messages and raise errors. PostgreSQL provides level option to specify with RAISE that specifies the severity of the statement. Possible levels with RAISE are DEBUG, LOG, NOTICE, WARNING, INFO and EXCEPTION.

EXCEPTION raises an error (which normally aborts the current transaction). The other levels only generate messages of different priority levels.

What is the order of severity for each level?

Following shows the order of severity from low – high, for how PostgreSQL defines the severity order.

WARNING
INFO/NOTICE
LOG
DEBUG

NOTE

Each level includes all the levels that follow it.

How to use RAISE in PostgreSQL?

Now let us understand how to use RAISE statement with different levels that are defined in PostgreSQL.

Example 1:

DO $$ 
BEGIN 
    RAISE INFO    'raise info    - time: %', now() ;
    RAISE WARNING 'raise warning - time: %', now();
    RAISE NOTICE  'raise notice  - time: %', now();
    RAISE LOG     'raise log     - time: %', now();
    RAISE DEBUG   'raise debug   - time: %', now();
END $$;

Output:

INFO: raise info - time: 2020-02-26 18:06:39.04374+00
NOTICE: raise notice - time: 2020-02-26 18:06:39.04374+00
WARNING: raise warning - time:e 2020-02-26 18:06:39.04374+00

Why LOG/DEBUG messages are not displayed to standard output?

To understand which message will be displayed to STDOUT, let us first look into below PostgreSQL configuration variables and what is your default settings.

client_min_messages(string): Controls which message levels are sent to the client STDOUT.

show client_min_messages;

client_min_messages
------------------------------
notice
(1 row)

log_min_messages(string): Controls which message levels are written to the server log.

show log_min_messages;

log_min_messages
------------------------------
warning
(1 row)

From the above it is obvious that client_min_messages server variable in PostgreSQL controls what would be displayed to STDOUT. Since, client_min_messages is set to notice which has higher severity than warning and same severity as info, those messages are displayed to stdout.

Are LOG/DEBUG messages logged to PostgreSQL server log?

Let us try to generate log file for the commands you execute and verify if RAISE LOG and RAISE DEBUG statements are logged in the output file.

Create a sql file for the block explained above in Example 1. Say for example raise-test.sql. You can

Approach 1: Executing .sql file from linux terminal

--Modify your DB connection details and $PWD with .sql file path
psql -h hostname -d dbname -U pguser -W pguserpwd -p 5432 -f $PWD/raise-test.sql -o $PWD/raise-test-log1.log


--View the log file
cat $PWD/raise-test-log1.log

Output:
INFO: raise info - time: 2020-02-27 08:15:45.84349+05:30
WARNING: raise warning - time:e 2020-02-27 08:15:45.84349+05:30
NOTICE: raise notice - time: 2020-02-27 08:15:45.84349+05:30


--View log file
cat $PWD/raise-test-log1.log

Output:
DO

Approach 2: Executing .sql file from psql

 

--Connect to psql
psql -h hostname -d dbname -U pguser -W pguserpwd -p 5432


--Take .sql file as input and create log
\i $PWD/raise-test.sql \o $PWD/raise-test-log2.log

Output:
INFO: raise info - time: 2020-02-27 08:21:00.111512+05:30
WARNING: raise warning - time:e 2020-02-27 08:21:00.111512+05:30
NOTICE: raise notice - time: 2020-02-27 08:21:00.111512+05:30
DO


--Turn off output logging
\o
\q


--View log file
cat $PWD/raise-test-log2.log

Output:
File is Empty

Clearly, RAISE LOG and RAISE DEBUG statements are not logged into output file when your PostgreSQL have default settings for client_min_messages and log_min_messages.

Can you change the default settings for client_min_messages and log_min_messages in PostgreSQL?

PostgreSQL allows users to overwrite the default value for client_min_messages in postgresql.conf file. You can also change the default value for your session using SET command.

show client_min_messages;

client_min_messages
------------------------------
notice
(1 row)



set client_min_messages = debug;

SET



show client_min_messages;

client_min_messages
------------------------------
debug
(1 row)

Run the block and watch the output.

DO $$ 
BEGIN 
    RAISE INFO    'raise info    - time: %', now() ;
    RAISE WARNING 'raise warning - time: %', now();
    RAISE NOTICE  'raise notice  - time: %', now();
    RAISE LOG     'raise log     - time: %', now();
    RAISE DEBUG   'raise debug   - time: %', now();
END $$;

Output:

INFO: raise info - time: 2020-02-26 18:06:39.04374+00
WARNING: raise warning - time:e 2020-02-26 18:06:39.04374+00
NOTICE: raise notice - time: 2020-02-26 18:06:39.04374+00
LOG: raise log - time:e 2020-02-26 18:06:39.04374+00
DEBUG: raise debug - time:e 2020-02-26 18:06:39.04374+00

Change default value for log_min_messages

The default value for log_min_messages is WARNING. You can overwrite the default value for log_min_messages in postgresql.conf file using a superuser role. You need a superuser to change this setting temporarily for your session using SET command.

show log_min_messages;

log_min_messages
------------------------------
warning
(1 row)



set log_min_messages = debug;
SET



show log_min_messages;

log_min_messages
------------------------------
debug
(1 row)

 

Now execute and verify the output file to check whether LOG and DEBUG messages are logged.

--Modify your DB connection details and $PWD with .sql file path
psql -h hostname -d dbname -U pguser -W pguserpwd -p 5432 -f $PWD/raise-test.sql -o $PWD/raise-test-log1.log



--View the log file
cat $PWD/raise-test-log1.log

Output:
INFO: raise info - time: 2020-02-27 08:15:45.84349+05:30
WARNING: raise warning - time:e 2020-02-27 08:15:45.84349+05:30
NOTICE: raise notice - time: 2020-02-27 08:15:45.84349+05:30
LOG: raise log - time:e 2020-02-26 18:06:39.04374+00
DEBUG: raise debug - time:e 2020-02-26 18:06:39.04374+00

 

What happens if you do not specify any level with RAISE?

If you don’t specify the level, by default RAISE statement will use EXCEPTION level that raises an error and stops the current transaction.

NOTE

RAISE without level raises an exception and aborts the current transaction if exception handler is not present

–RAISE without level, no exception handler, program stops abruptly

DO $$ 
BEGIN 
     RAISE INFO   'raise info   - time: %', now() ;
     RAISE;
     RAISE NOTICE 'raise notice - time: %', now();
END $$;

NOTE

RAISE without level raises an exception where exception handler handles the exception and block doesn’t terminate abruptly.

–RAISE without level, exception handler present, program executes successfully

DO $$ 
BEGIN 
    RAISE INFO   'raise info   - time: %', now() ;
    RAISE;
    RAISE NOTICE 'raise notice - time: %', now();
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'Exception! notice message %', now();
 END $$;

What is the right way to use RAISE?

If you are going with the default parameter settings, then using NOTICE/INFO for code debugging and EXCEPTION for error handling makes your job uncomplicated.

PostgreSQL offers code debugging with lot of options where client_min_messages and log_min_messages controls the way message are displayed. You may have many combination of the level with Postgres database parameters settings and if the combination is not right, you may see different behavior.

Leave a Reply

Please Login to comment
  Subscribe  
Notify of
Close Menu
error: Content is protected !!