> 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