Docs incorrectly claiming equivalence between show and pg_settings

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.

Thanks,
Stefan Seifert

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.

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> However, in the case of custom variables, you are right that pg_settings
> doesn't show custom variables.

That is entirely intentional: the reason we do not show placeholder
variables in pg_settings is that we have no accurate information about
them, and so everything pg_settings might show would be fabricated,
and probably wrong, information.

Once the placeholder has been replaced by a proper declaration of the
GUC variable, it will be shown (with correct info), unless of course
the "proper declaration" includes GUC_NO_SHOW_ALL.

What this gets back to is that manually created "custom variables" are an
abuse of a loophole that was only meant to allow postgresql.conf to set
a parameter belonging to an extension module that hasn't been loaded yet.

If we want to actually support such variables, there should be a way to
properly declare one, including giving its type and other properties
... and ideally we'd not let you set one without having declared it,
though it's not quite clear how to enforce that without breaking the
parameter-placeholder case.

> 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

or (4) fix the lack of a declaration capability. But both (1) and (2)
are horrid ideas. There are good reasons for having invented
GUC_NO_SHOW_ALL, and just trashing it is not the answer.

As for (3), I might be wrong, but I don't think the documentation mentions
the possibility of abusing SET this way at all. Restrictions in
undocumented quasi-features are likewise undocumented.

regards, tom lane

On Sat, Apr 19, 2014 at 01:38:16PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > However, in the case of custom variables, you are right that pg_settings
> > doesn't show custom variables.
>
> That is entirely intentional: the reason we do not show placeholder
> variables in pg_settings is that we have no accurate information about
> them, and so everything pg_settings might show would be fabricated,
> and probably wrong, information.
>
> Once the placeholder has been replaced by a proper declaration of the
> GUC variable, it will be shown (with correct info), unless of course
> the "proper declaration" includes GUC_NO_SHOW_ALL.

Oh, I had forgotten these were placeholders that get replaced later.

> What this gets back to is that manually created "custom variables" are an
> abuse of a loophole that was only meant to allow postgresql.conf to set
> a parameter belonging to an extension module that hasn't been loaded yet.
>
> If we want to actually support such variables, there should be a way to
> properly declare one, including giving its type and other properties
> ... and ideally we'd not let you set one without having declared it,
> though it's not quite clear how to enforce that without breaking the
> parameter-placeholder case.
>
> > 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
>
> or (4) fix the lack of a declaration capability. But both (1) and (2)
> are horrid ideas. There are good reasons for having invented
> GUC_NO_SHOW_ALL, and just trashing it is not the answer.
>
> As for (3), I might be wrong, but I don't think the documentation mentions
> the possibility of abusing SET this way at all. Restrictions in
> undocumented quasi-features are likewise undocumented.

OK, let's wait to see if anyone else complains --- if so, we can
document the SHOW ALL and pg_settings behavior in the placeholders
section.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Saturday 19 April 2014 13:38:16 Tom Lane wrote:

> > 3 document this restriction
>
> As for (3), I might be wrong, but I don't think the documentation mentions
> the possibility of abusing SET this way at all. Restrictions in
> undocumented quasi-features are likewise undocumented.

http://www.postgresql.org/docs/devel/static/runtime-config-custom.html
states: "Because custom options may need to be set in processes that have not
loaded the relevant extension module, PostgreSQL will accept a setting for any
two-part parameter name."
So I'd say the possibility of using SET this way is somewha documented. That's
at least how I actually got the idea of doing that.

Maybe it can help if I describe our use case: we have some plperlu stored
procedures and triggers that use them that replicate specific data from our
internal management application's database to the system on our webserver.
When running the application's test suite though we obviously don't want that
and instead want it to replicate to another local database, so we can test the
triggers as well. We use a custom option set by our test suite to communicate
the required change in replication target to the stored procedure. As a nice
side effect we can use the same mechanism to prevent a test sandbox version of
the application from trying to replicate to the webserver.

So far this works quite well except for the SHOW command throwing an exception
when we request an unknown configuration parameter complicating our stored
procedure a little. It would be more comfortable if we could query pg_settings
and just get an empty result if the configuration parameter is not set.

Regards,
Stefan