Обсуждение: allowed user/db variables

I wonder if there's a way to read all allowed user/database variables 
that can be set/reset.
I'd like to have this self-configured in pgAdmin3 instead of hard-coded.

Can you give a hint?

Regards,
Andreas

Andreas Pflug <Andreas.Pflug@web.de> writes:
> I wonder if there's a way to read all allowed user/database variables 
> that can be set/reset.
> I'd like to have this self-configured in pgAdmin3 instead of hard-coded.

Look at SHOW ALL, maybe?  Or the pg_settings system view?

If you're willing to think about a solution that would only exist
starting in 7.4 --- some of my cohorts at Red Hat are about to submit
patches that create a separate "pg_guc" executable that contains
another copy of the backend's GUC variable table, and can be used to
obtain the set of known variables, their wired-in default values, min
and max values, etc.  So you could imagine executing this program to
get the info.  However, this would only work on the machine where the
stuff is installed, not necessarily on a remote client, so maybe it
wouldn't help pgAdmin.

If anyone's wondering why the RH guys want such an executable instead
of adding functionality to SHOW, it's because they want to be able to
get the info even when the postmaster isn't running; it's for a tool
that will help people set up postgresql.conf, so it seems essential that
the knowledge be available before starting the postmaster.

It occurs to me that we could consider adding columns to pg_settings
to make more info available to remote clients, too.  The current value
of a setting is far from its only interesting property ...
        regards, tom lane

Tom Lane wrote:

>Look at SHOW ALL, maybe?
>
Of course.... Sorry.

>If you're willing to think about a solution that would only exist
>starting in 7.4 --- some of my cohorts at Red Hat are about to submit
>patches that create a separate "pg_guc" executable that contains
>another copy of the backend's GUC variable table, and can be used to
>obtain the set of known variables, their wired-in default values, min
>and max values, etc.  So you could imagine executing this program to
>get the info.  However, this would only work on the machine where the
>stuff is installed, not necessarily on a remote client, so maybe it
>wouldn't help pgAdmin.
>
No, need to retrieve this online.
Thanks,
Andreas

> I wonder if there's a way to read all allowed user/database variables 
> that can be set/reset.
> I'd like to have this self-configured in pgAdmin3 instead of hard-coded.

You know, I was just about to ask this for phpPgAdmin3!!!

SHOW ALL;

Will help, but won't tell the whole story...

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> SHOW ALL;
> Will help, but won't tell the whole story...

See my followup.  Which bits of info would you like to see added that 
SHOW doesn't reveal?
        regards, tom lane

Tom Lane wrote:

>"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>  
>
>>SHOW ALL;
>>Will help, but won't tell the whole story...
>>    
>>
>
>See my followup.  Which bits of info would you like to see added that 
>SHOW doesn't reveal?
>
>  
>
I'd like to have type (bool/numeric/alpha/value list), min, max and 
values in pg_settings.
Currently, you can select the name from a combobox, but type any value 
you like, waiting for the backend to complain (or accept).

Regards,
Andreas

Andreas Pflug wrote:

> I'd like to have type (bool/numeric/alpha/value list), min, max and 
> values in pg_settings.
> Currently, you can select the name from a combobox, but type any value 
> you like, waiting for the backend to complain (or accept).
>
Additionally, it should be marked which vars are legal to set online. 
Currently, "port" and "ssl" are offered to change too...

Regards,
Andreas

> See my followup.  Which bits of info would you like to see added that
> SHOW doesn't reveal?

Unlike andreas, I'm not interested in the types and ranges of values, what I
need to know is the GUC variables that the user is allowed to set, in
particular what they can ALTER USER / SET ...  so that I can display that
feature...

Chris

Christopher Kings-Lynne wrote:
>>See my followup.  Which bits of info would you like to see added that
>>SHOW doesn't reveal?
> 
> Unlike andreas, I'm not interested in the types and ranges of values, what I
> need to know is the GUC variables that the user is allowed to set, in
> particular what they can ALTER USER / SET ...  so that I can display that
> feature...
> 

I was considering adding this and the stuff Andreas requested to 
pg_settings (but not "SHOW ALL" or "SHOW x" unless people feel it's 
important to kept them consistent with pg_settings). Were the Red Hat 
guys going to do this?

Joe

> I was considering adding this and the stuff Andreas requested to 
> pg_settings (but not "SHOW ALL" or "SHOW x" unless people feel it's 
> important to kept them consistent with pg_settings). Were the Red Hat 
> guys going to do this?

pg_settings would be fine for phpPgAdmin.

Chris

Christopher Kings-Lynne wrote:

>>I was considering adding this and the stuff Andreas requested to 
>>pg_settings (but not "SHOW ALL" or "SHOW x" unless people feel it's 
>>important to kept them consistent with pg_settings). Were the Red Hat 
>>guys going to do this?
>>    
>>
>
>pg_settings would be fine for phpPgAdmin.
>  
>
Same for pgAdmin3.

Regards,
Andreas

>>> I was considering adding this and the stuff Andreas requested to 
>>> pg_settings (but not "SHOW ALL" or "SHOW x" unless people feel it's 
>>> important to kept them consistent with pg_settings). Were the Red Hat 
>>> guys going to do this?
>> 
>> pg_settings would be fine for phpPgAdmin.
>> 
> Same for pgAdmin3.

I agree with this plan also.  I'm not sure if the RH guys had intended
to get around to this or not --- it's not on their shortlist of stuff
they need for their tools.

The proposed patch from RH includes addition of descriptions to the
variables' table entries in guc.c.  It might make sense to include these
as a column in pg_settings as well; but if we do then changing the view
would have to wait till that patch is submitted and accepted.  (I was
offline yesterday but it doesn't look like anything's been done; I will
remind 'em that feature freeze is hard upon us.)
        regards, tom lane