PostgreSQL: set_config and current_setting

PostgreSQL comes with a rich set of administrative functions for various use cases. There are functions to control parameters, there are functions for index maintenance, others are for recovery and or backup control and there is much more. In this post we’ll look at how you can use the build-in functions to set and get parameters without touching the configuration file (or setting parameters at the cluster, database and user level). In the posts that follow, we’ll walk through various other functions, PostgreSQL provides by default.

If you want to set parameters at the session level, you’ll usually use the “set” command, e.g.:

postgres=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

postgres=# set work_mem='6MB';
SET
postgres=# show work_mem;
 work_mem 
----------
 6MB
(1 row)

As this is on the session level, you’ll get back to the default setting, once you create a new session:

postgres=# c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

Another possibility you have for this requirement, is to use the build-in set_config function:

postgres=# select set_config('work_mem','6MB',true);
 set_config 
------------
 6MB
(1 row)

postgres=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

The third parameter specifies the scope, and in this case the scope is the transaction. As psql by default auto commits and you’re not in a transaction, you fall back to the default immediately. Doing the same in an explicit transactions makes this more clear:

postgres=# begin;
BEGIN
postgres=*# select set_config('work_mem','6MB',true);
 set_config 
------------
 6MB
(1 row)

postgres=*# show work_mem;
 work_mem 
----------
 6MB
(1 row)

postgres=*# end;
COMMIT
postgres=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

If you want to set a parameter for the scope of the whole session, just switch the third parameter:

postgres=# select set_config('work_mem','6MB',false);
 set_config 
------------
 6MB
(1 row)

postgres=# show work_mem ;
 work_mem 
----------
 6MB
(1 row)

postgres=# c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# show work_mem ;
 work_mem 
----------
 4MB
(1 row)

The opposite of the set_config function is current_setting:

postgres=# select current_setting('work_mem',false);
 current_setting 
-----------------
 4MB
(1 row)

The reason for the second parameter is, to give you choice what happens if you ask for a parameter that does not exist:

postgres=# select current_setting('work_memX',false);
ERROR:  unrecognized configuration parameter "work_memX"
postgres=# select current_setting('work_memX',true);
 current_setting 
-----------------
 
(1 row)

Depending on what behavior you want, you’ll either get NULL or an error message.

You might ask yourself: Why do I need these functions when I can do the same with set and show? The answer is easy: Functions can easily be used in other functions or procedures:

postgres=# ! cat a.sql
create or replace function f1() returns text as
$$
declare
  lv text;
begin
  select current_setting('work_mem')
    into lv;
  return lv;
end;
$$ language plpgsql;
postgres=# i a.sql
CREATE FUNCTION
postgres=# select f1();
 f1  
-----
 4MB
(1 row)

This gives you the flexibility to ask for, and set parameters while you’re doing business logic in the database.