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

> 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
installationdefaults.
 
>> 
>> 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
runlocally.
 
>> 
>> 
>>                  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
duplicatecolumn 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