pg_set_level

pg_set_level is a PostgreSQL extension which allows to customize the SET statement.

Installation

This module can be built using the standard PGXS infrastructure. For this to work, the pg_config program must be available in your $PATH:

git clone https://github.com/pierreforstmann/pg_set_level.git cd pg_set_level make make install

Extension must be loaded at server level with shared_preload_libraries parameter: shared_preload_libraries = 'pg_set_level'

It can also be created with following SQL statement at server level: create extension pg_set_level;

This extension has been validated with PostgreSQL 9.5, 9.6, 10, 11, 12, 13, 14, 15 and 16.

pg_set_level has 2 specific GUC parameters:

pg_set_level.names which is the list of GUC comma separated parameters for which SET statement must be modified
ps_set_level.actions which is the list of comma separated actions to run when SET statement is run. Action can be one the message severity level: INFO, NOTICE, WARNING, ERROR, LOG, FATAL. The first action is run for first parameter and so on.

Note that pg_set_level only works for the SET statement: it does not take into account set_config function call for any GUC parameter.

To generate an error when SET work_mem is run, add in postgresql.conf: pg_set_level.names='work_mem' pg_set_level.actions='error'

Run from psql: # show work_mem; work_mem ---------- 4MB (1 row)

# set work_mem='1G'; ERROR: pg_set_level: set work_mem='1G';