Re: ALTER SYSTEM for tcp_keepalives_idle doesn't show up with theSHOW 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
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
    );

Best,
David.

P.S.  In future, please don't top post.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate