On Wed, Feb 12, 2014 at 03:54:31PM +0100, Stefan Seifert wrote:
> Hi!
>
> http://www.postgresql.org/docs/devel/static/sql-show.html claims:
> "Also, the pg_settings system view produces the same information."
>
> This is not entirely correct. On IRC I was told, that the view only contains
> settings set from C, not user defined settings defined by the SET command:
>
> timemngt=# set my.test = 'test';
> SET
> timemngt=# show my.test;
> ┌─────────┐
> │ my.test │
> ├─────────┤
> │ test │
> └─────────┘
> (1 row)
>
> timemngt=# select * from pg_settings where name = 'my.test';
> timemngt=#
>
> Including this rather obscure bit of information might help another user down
> the road.
[ Thread reposted to hackers as this is perhaps a coding issue.]
This is an interesting report. You are correct that there are several
places in the docs that say that SHOW and pg_settings display the same
information, and even state that pg_settings shows _more_ information
than SHOW.
However, in the case of custom variables, you are right that pg_settings
doesn't show custom variables. I have found the place in the code
where we do that using GUC_NO_SHOW_ALL.
I believe the original reason for this is that custom variables were
designed to modify plugin languages and therefore not useful for people
doing SHOW ALL, but if you specifically asked for it, it would show it
to you. Because pg_settings is built as a view, the API doesn't really
have an ALL mode.
We can do a few things:
1 show custom variables in SHOW ALL and pg_settings
2 show custom and other non-SHOW-ALL variables in pg_settings
3 document this restriction
I am not sure which approach is best. I am attaching a patch that does
#1.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +