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

> On Tue, Jan 02, 2018 at 03:55:14PM +0000, Robertson, Alan L wrote:
>>
>> 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 ;-)

> It does indeed.

>> 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)
>
> You don't actually need to create a temporary table to get that. You
> could use something like:
>
> SELECT
> s.setting, f.*
> FROM
> pg_settings s
> JOIN
> pg_file_settings f
> ON (
> s.name = 'config_file' AND
> s.setting <> f.sourcefile
> );

For my purposes, the query below may be slightly better - it doesn't provide unneeded information, and it avoids duplicate column names in the result:

SELECT
f.seqno, f.name, f.setting, f.applied, f.error
FROM
pg_settings s
JOIN
pg_file_settings f
ON (
s.name = 'config_file' AND
s.setting <> f.sourcefile
) ORDER by f.seqno;

seqno | name | setting | applied | error
-------+---------------------+---------+---------+-------
23 | tcp_keepalives_idle | 60 | t |
(1 row)

Thanks to everyone for the great product and the kind and gentle education!

>
> Best,
> David.

> P.S. In future, please don't top post.

Had to switch which email address I was sending from to do that. Sorry for the confusion :-(.

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

Many thanks for everyone's help!

-- Alan