Configuration - PostgreSQL 9 Administration - page 55

SET and RESET are SQL commands that can be issued from any interface. They apply only to
PostgreSQL server parameters, by which we mean parameters that affect the server, but not
necessarily the whole server. There may be other parameters, such as JDBC driver parameters,
that cannot be set in this way. Refer to the Connections chapter for help with those.
How it works...
When you change the value of a setting during your session, such as:
SET work_mem = '16MB';
then this will show up in the catalog view pg_settings as follows:
postgres=# SELECT name, setting, reset_val, source
FROM pg_settings WHERE source = 'session';
name | setting | reset_val | source
----------+---------+-----------+---------
work_mem | 16384 | 1024 | session
until you issue:
RESET work_mem;
after which the setting returns to the reset_val , and the source returns to default.
name | setting | reset_val | source
---------+---------+-----------+---------
work_mem | 1024 | 1024 | default
There's more...
You can change the value of a setting during your transaction as well, as follows:
SET LOCAL work_mem = '16MB';
then this will show up in the catalog view pg_settings as follows:
postgres=# SELECT name, setting, reset_val
FROM pg_settings WHERE source = 'session';
name | setting | reset_val | source
----------+---------+-----------+---------
work_mem | 1024 | 1024 | session
Huh? What happened to my parameter setting? SET LOCAL takes effect only for the transaction
in which it was executed, which, in our case, was just the SET LOCAL command. We need to
execute it inside a transaction block to be able to see the setting take hold as follows:
BEGIN;
SET LOCAL work_mem = '16MB';