Re: ALTER SYSTEM for tcp_keepalives_idle doesn't show up with the SHOW command.

This query seems to be pretty cool for looking at configuration changes:

SELECT pg_settings.setting INTO TEMPORARY config_file FROM pg_settings WHERE name = 'config_file';
SELECT * FROM config_file INNER JOIN pg_file_settings ON config_file.setting!=pg_file_settings.sourcefile;

The intent of this query is to return the values of all settings which have been modified by SQL from the installation defaults.

I'm an SQL newbie, so I'm sure this august body can improve it. But it does appear to work ;-)

The output of the join looks something like this - and it has the _correct_ version of the TCP variables, even when run locally.

setting | sourcefile | sourceline | seqno | name | setting | applied | error
------------------------------------------+---------------------------------------------------+------------+-------+---------------------+---------+---------+-------
/etc/postgresql/9.5/main/postgresql.conf | /var/lib/postgresql/9.5/main/postgresql.auto.conf | 3 | 23 | tcp_keepalives_idle | 60 | t |
(1 row)

________________________________________
From: David Fetter <david(at)fetter(dot)org>
Sent: Wednesday, December 27, 2017 09:19
To: David G. Johnston
Cc: Amit Kapila; Robertson, Alan L; Tom Lane; Postgres Bug
Subject: Re: ALTER SYSTEM for tcp_keepalives_idle doesn't show up with the SHOW command.

On Wed, Dec 27, 2017 at 09:13:38AM -0700, David G. Johnston wrote:
> On Wed, Dec 27, 2017 at 9:05 AM, David Fetter <david(at)fetter(dot)org> wrote:
>
> >
> > The following hack works for me. Apart from being ugly, it's
> > fundamentally unsatisfactory because the question of whether the
> > setting has been activated is left unsettled
> >
>
> Starting with 9.5 we've at least made the parsing of the configuration file
> unnecessary:
>
> https://www.postgresql.org/docs/10/static/view-pg-file-settings.html

Great! That awful query turns into:

SELECT setting
FROM pg_file_settings
WHERE
"name" = 'tcp_keepalives_idle' AND
applied;

...which is a real improvement.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
E-MAIL CONFIDENTIALITY NOTICE:
The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain confidential and/or legally privileged information. If you are not the intended recipient of this message or if this message has been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this message and any attachments. If you are not the intended recipient, you are notified that any use, dissemination, distribution, copying, or storage of this message or any attachment is strictly prohibited.