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

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