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
>    );

I've written a piece of Python which creates the necessary ALTER SYSTEM commands to back up the system changes made by
previousALTER SYSTEM commands. It needed the query above to get a little more complicated - to get the type of the
configurationparameter.  It now looks like this:
 

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

Thanks again for everyone's help and consideration!  Like I said, I've learned a lot.

    -- Alan