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

I've written a piece of Python which creates the necessary ALTER SYSTEM commands to back up the system changes made by previous ALTER SYSTEM commands. It needed the query above to get a little more complicated - to get the type of the configuration parameter. 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