Thread: Re: [DOCS] Docs incorrectly claiming equivalence between show and pg_settings
Re: [DOCS] Docs incorrectly claiming equivalence between show and pg_settings
From
Bruce Momjian
Date:
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. +
Attachment
Re: Re: [DOCS] Docs incorrectly claiming equivalence between show and pg_settings
From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.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
Re: Re: [DOCS] Docs incorrectly claiming equivalence between show and pg_settings
From
Bruce Momjian
Date:
On Sat, Apr 19, 2014 at 01:38:16PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Re: Re: [DOCS] Docs incorrectly claiming equivalence between show and pg_settings
From
Stefan Seifert
Date:
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