Обсуждение: Enhancement Idea - Expose the active value of a parameter inpg_settings

Hi Hackers,

I would like to propose that we expose the "active" value of parameters in pg_settings, instead of "-1".  In this
examplebelow, when it's set to "-1" I need to know that autovacuum_work_mem is related to the setting of
maintenance_work_mem,so that I can determine that the actual setting is 64MB: 

postgresql.conf
===============
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#maintenance_work_mem = 64MB            # min 1MB


pg_settings
===========
postgres=# SELECT name, setting, unit FROM pg_settings WHERE name IN ('autovacuum_work_mem','maintenance_work_mem');
        name         | setting | unit
----------------------+---------+------
autovacuum_work_mem  | -1      | kB
maintenance_work_mem | 65536   | kB
(2 rows)


I think it would make sense to create a new column called something like "active_setting", which will allow simple
verificationof the setting that's in use without having to know the specifics about the parameter's relationship with
others,and the version of PostgreSQL.  For parameters where "-1" is a real setting that has meaning (e.g.
log_min_duration_statement),then it should return "-1". 

I presume that as a part of running the server that we have already decoded what the active values should be, so I'm
askingif we can expose this data via the pg_settings view? 

Regards
Greg Clough

https://ipreo.com


********* Confidential Disclaimer *********

This e-mail message and any attachments are confidential. Dissemination, distribution or copying of this e-mail or any
attachmentsby anyone other than the intended recipient is prohibited. If you are not the intended recipient, please
notifyIpreo immediately by replying to this e-mail, and destroy all copies of this e-mail and any attachments. If you
havereceived this e-mail as part of a marketing communication and you would like to unsubscribe from future marketing
communications,please review our privacy policy<http://info.ipreo.com/Ipreo-Private-Policy.html> for more information. 

Greg Clough <greg.clough@ipreo.com> writes:
> I would like to propose that we expose the "active" value of parameters in pg_settings, instead of "-1".  In this
examplebelow, when it's set to "-1" I need to know that autovacuum_work_mem is related to the setting of
maintenance_work_mem,so that I can determine that the actual setting is 64MB: 

If we did that, how would you tell the difference between "-1" and a hard
setting of 64MB?

            regards, tom lane

On Fri, May 25, 2018 at 9:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Greg Clough <greg.clough@ipreo.com> writes:
>> I would like to propose that we expose the "active" value of parameters in pg_settings, instead of "-1".  In this
examplebelow, when it's set to "-1" I need to know that autovacuum_work_mem is related to the setting of
maintenance_work_mem,so that I can determine that the actual setting is 64MB: 
>
> If we did that, how would you tell the difference between "-1" and a hard
> setting of 64MB?
>

He's proposing an extra column to show the actual value used, so
distinguishing them should be a problem.

cheers

andrew


--
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Fri, May 25, 2018 at 10:11 AM, Andrew Dunstan
<andrew.dunstan@2ndquadrant.com> wrote:
> He's proposing an extra column to show the actual value used, so
> distinguishing them should be a problem.

For most settings, that column would just be a duplicate.  For a
handful, it would pull in the value of some other GUC.  If somebody
finds that useful, cool, they can write a view that does it and
install it on their own cluster.  I don't think that it makes a lot of
sense to put it in core, though.  My guess would be that more people
would be annoyed or confused by the extra column than would be pleased
or enlightened by it.  I could of course be wrong.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, May 25, 2018 at 10:11 AM, Andrew Dunstan
> <andrew.dunstan@2ndquadrant.com> wrote:
>> He's proposing an extra column to show the actual value used, so
>> distinguishing them should be a problem.

> For most settings, that column would just be a duplicate.  For a
> handful, it would pull in the value of some other GUC.  If somebody
> finds that useful, cool, they can write a view that does it and
> install it on their own cluster.  I don't think that it makes a lot of
> sense to put it in core, though.  My guess would be that more people
> would be annoyed or confused by the extra column than would be pleased
> or enlightened by it.  I could of course be wrong.

Yeah, that's pretty much my evaluation --- given the tiny number of
GUCs that have behaviors like this, an extra column seems like it
would mostly be confusing.  Plus, pg_settings is too darn wide already.

Personally, what I'd rather do is try to get rid of GUC behaviors like
"the effective value depends on something else".  But convenience and
backwards compatibility may be arguments against that.

            regards, tom lane

On Fri, May 25, 2018 at 10:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Personally, what I'd rather do is try to get rid of GUC behaviors like
> "the effective value depends on something else".  But convenience and
> backwards compatibility may be arguments against that.

Yeah.  The dependency between various GUCs is something that I don't
like very much either.  However, AFAICT, the limited number of GUCs
that have behaviors like this mostly all do for good reasons,
generally that there are two GUCs which people usually want set the
same way but occasionally not.  Decoupling the GUCs could lead to
people accidentally shooting themselves in the foot, and as you
mention it would also break configurations that work today when users
try to upgrade.  Maybe it would be worth going through that pain if we
could point to some really compelling benefit (if you do this, the
whole system can run 10% faster!) but I know of no such benefit.  It
seems more like a wart than a bullet wound.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

>> On Fri, May 25, 2018 at 10:11 AM, Andrew Dunstan
>> <andrew.dunstan@2ndquadrant.com> wrote:
>>> He's proposing an extra column to show the actual value used, so
>>> distinguishing them should be a problem.
>
>> For most settings, that column would just be a duplicate.  For a
>> handful, it would pull in the value of some other GUC.  If somebody
>> finds that useful, cool, they can write a view that does it and
>> install it on their own cluster.  I don't think that it makes a lot of
>> sense to put it in core, though.  My guess would be that more people
>> would be annoyed or confused by the extra column than would be pleased
>> or enlightened by it.  I could of course be wrong.
>
> Yeah, that's pretty much my evaluation --- given the tiny number of
> GUCs that have behaviors like this, an extra column seems like it
> would mostly be confusing.  Plus, pg_settings is too darn wide already.
>
> Personally, what I'd rather do is try to get rid of GUC behaviors like
> "the effective value depends on something else".  But convenience and
> backwards compatibility may be arguments against that.
>
> regards, tom lane

Many thanks for the quick consideration, even if it's ultimately a rejection.  Figuring out some SQL that will work
acrossall platforms, versions, and compile-time options will be "fun", but I'm up for a challenge. 

It came about because I was scraping the entire cluster with "pgmetrics", and I was trying to reduce all "size" numeric
settingsdown to bytes for them. I figured it would be nice if PostgreSQL could expose the data it's already got rather
thanforcing all external applications that want that data to do the same thing. 

I'll deal with it externally, but I hope it was a reasonable proposal and not completely off-the-wall.

Regards,
Greg Clough.


********* Confidential Disclaimer *********

This e-mail message and any attachments are confidential. Dissemination, distribution or copying of this e-mail or any
attachmentsby anyone other than the intended recipient is prohibited. If you are not the intended recipient, please
notifyIpreo immediately by replying to this e-mail, and destroy all copies of this e-mail and any attachments. If you
havereceived this e-mail as part of a marketing communication and you would like to unsubscribe from future marketing
communications,please review our privacy policy<http://info.ipreo.com/Ipreo-Private-Policy.html> for more information. 

On Fri, May 25, 2018 at 12:14 PM, Greg Clough <greg.clough@ipreo.com> wrote:
> Many thanks for the quick consideration, even if it's ultimately a rejection.  Figuring out some SQL that will work
acrossall platforms, versions, and compile-time options will be "fun", but I'm up for a challenge.
 

Why would you need different SQL for this on different platforms or
with different compile-time options?

I agree that there could be some variation across major versions, but
I don't think there's a lot.

BTW, posting to a public mailing list with a giant blob of legalese
about confidential information in your signature is kind of silly.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company