Database Reference
In-Depth Information
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
You should also note that the value of source is "session" rather than "transaction", as you
might have been expecting.
What are the current configuration settings?
At some point it will occur to you to ask, "What are the
current
configuration settings?"
How to do it...
Your first thought is probably "look in
postgresql.conf
". That works, but only as long as
there is only one parameter file. If there are two, then maybe you're reading the wrong file!
(How do you know?). So the cautious and accurate way is not to trust a text file, but to trust
the server itself.
Also, we learned in the recipe
When
to
set
parameters
that each parameter has a scope that
determines when it can be set. Some parameters can be set through
postgresql.conf
,
but others can be changed afterwards also. So the current value of configuration settings
may have been subsequently changed.
We can use the
SHOW
command, such as the following:
postgres=# SHOW work_mem;
work_mem
----------
1MB
(1 row)
though remember that it reports the current setting at the time when it is run, and that
can be changed in many places.
Another way of finding current settings is to access a PostgreSQL catalog view named
pg_settings.
postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_settings WHERE name = 'work_mem';
[ RECORD 1 ] --------------------------------------------------------
----
name | work_mem
