Thread: ALTER SYSTEM for tcp_keepalives_idle doesn't show up with the SHOWcommand.

The contents of this e-mail message and
any attachments are intended solely for the
addressee(s) and may contain confidential
and/or legally privileged information. If you
are not the intended recipient of this message
or if this message has been addressed to you
in error, please immediately alert the sender
by reply e-mail and then delete this message
and any attachments. If you are not the
intended recipient, you are notified that
any use, dissemination, distribution, copying,
or storage of this message or any attachment
is strictly prohibited.

"Robertson, Alan L" <Alan.Robertson@charter.com> writes:
> Dear Postgres Bug list,
> I read about the ALTER SYSTEM command at https://www.postgresql.org/docs/9.6/static/sql-altersystem.html and an
articleat 
> http://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-alter-system-set-for-dynamic-configuration/

> and his example worked exactly as it should.  However, when I tried with tcp_keepalives_idle, updates the auto.conf
fileas it should, but does not show up in the SHOW command...  I tried it with the value 60, or '60' - didn't seem to
makeany difference... 

I think the keepalive settings will read as zero unless your connection
is actually over TCP (ie not a unix socket).

            regards, tom lane

Is this described in the documentation somewhere?

Is there an alternative way to read out what the system thinks that these TCP-related configuration values are set to?

Thanks!

  -- Alan


________________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, December 22, 2017 10:20 AM
To: Robertson, Alan L
Cc: Postgres Bug
Subject: Re: ALTER SYSTEM for tcp_keepalives_idle doesn't show up with the SHOW command.

"Robertson, Alan L" <Alan.Robertson@charter.com> writes:
> Dear Postgres Bug list,
> I read about the ALTER SYSTEM command at https://www.postgresql.org/docs/9.6/static/sql-altersystem.html and an
articleat 
> http://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-alter-system-set-for-dynamic-configuration/

> and his example worked exactly as it should.  However, when I tried with tcp_keepalives_idle, updates the auto.conf
fileas it should, but does not show up in the SHOW command...  I tried it with the value 60, or '60' - didn't seem to
makeany difference... 

I think the keepalive settings will read as zero unless your connection
is actually over TCP (ie not a unix socket).

                        regards, tom lane
E-MAIL CONFIDENTIALITY NOTICE:
The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain
confidentialand/or legally privileged information. If you are not the intended recipient of this message or if this
messagehas been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this
messageand any attachments. If you are not the intended recipient, you are notified that any use, dissemination,
distribution,copying, or storage of this message or any attachment is strictly prohibited. 

On Fri, Dec 22, 2017 at 11:16 PM, Robertson, Alan L
<Alan.Robertson@charter.com> wrote:
> Is this described in the documentation somewhere?
>

Yes.  See the documentation of the parameter tcp_keepalives_idle [1].
It clearly documents the behavior, "In sessions connected via a
Unix-domain socket, this parameter is ignored and always reads as
zero".

https://www.postgresql.org/docs/devel/static/runtime-config-connection.html

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Sat, Dec 23, 2017 at 09:34:27AM +0530, Amit Kapila wrote:
> On Fri, Dec 22, 2017 at 11:16 PM, Robertson, Alan L
> <Alan.Robertson@charter.com> wrote:
> > Is this described in the documentation somewhere?
> 
> Yes.  See the documentation of the parameter tcp_keepalives_idle [1].
> It clearly documents the behavior, "In sessions connected via a
> Unix-domain socket, this parameter is ignored and always reads as
> zero".
> 
> https://www.postgresql.org/docs/devel/static/runtime-config-connection.html

So the behavior is documented, which is better than leaving it
undocumented, kinda.

The following hack works for me.  Apart from being ugly, it's
fundamentally unsatisfactory because the question of whether the
setting has been activated is left unsettled:

    SELECT (regexp_match(
        pg_read_file(
            'postgresql.auto.conf'),
            $$tcp_keepalives_idle = '([[:digit:]]+)$$
        )
    )[1] AS "tcp_keepalives_idle";
     tcp_keepalives_idle
    ---------------------
     60
    (1 row)

It might be possible to infer the state of activation by riffling
through the logs with similar hackery, but it's tricky to ensure that
the answer you get is current.

Is it worthwhile to reconsider the decision not to expose the
currently active setting of this parameter via UNIX sockets?

Best,
David.
-- 
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

On Wed, Dec 27, 2017 at 09:13:38AM -0700, David G. Johnston wrote:
> On Wed, Dec 27, 2017 at 9:05 AM, David Fetter <david@fetter.org> wrote:
> 
> >
> > The following hack works for me.  Apart from being ugly, it's
> > fundamentally unsatisfactory because the question of whether the
> > setting has been activated is left unsettled
> >
> 
> Starting with 9.5 we've at least made the parsing of the configuration file
> unnecessary:
> 
> https://www.postgresql.org/docs/10/static/view-pg-file-settings.html

Great!  That awful query turns into:

SELECT setting
FROM pg_file_settings
WHERE
    "name" = 'tcp_keepalives_idle' AND
    applied;

...which is a real improvement.

Best,
David.
-- 
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

So, if I understand correctly, this query would yield one row if it was syntactically correct, and no rows if it had a
syntaxerror. 

And there's nothing which indicates if it is currently activated or not.

Do I understand that correctly?

    Thanks!


________________________________________
From: David Fetter <david@fetter.org>
Sent: Wednesday, December 27, 2017 9:19 AM
To: David G. Johnston
Cc: Amit Kapila; Robertson, Alan L; Tom Lane; Postgres Bug
Subject: Re: ALTER SYSTEM for tcp_keepalives_idle doesn't show up with the SHOW command.

On Wed, Dec 27, 2017 at 09:13:38AM -0700, David G. Johnston wrote:
> On Wed, Dec 27, 2017 at 9:05 AM, David Fetter <david@fetter.org> wrote:
>
> >
> > The following hack works for me.  Apart from being ugly, it's
> > fundamentally unsatisfactory because the question of whether the
> > setting has been activated is left unsettled
> >
>
> Starting with 9.5 we've at least made the parsing of the configuration file
> unnecessary:
>
> https://www.postgresql.org/docs/10/static/view-pg-file-settings.html

Great!  That awful query turns into:

SELECT setting
FROM pg_file_settings
WHERE
    "name" = 'tcp_keepalives_idle' AND
    applied;

...which is a real improvement.

Best,
David.
--
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
E-MAIL CONFIDENTIALITY NOTICE:
The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain
confidentialand/or legally privileged information. If you are not the intended recipient of this message or if this
messagehas been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this
messageand any attachments. If you are not the intended recipient, you are notified that any use, dissemination,
distribution,copying, or storage of this message or any attachment is strictly prohibited. 

And, in a perfect world, the "passing" config items would be included in database backups - in a form that would allow
themto automatically be restored along with the database... :-D 

To me, this makes sense, since your database backups are in the form of SQL that will restore the database to its
original/currentstate. 

Thoughts?

 -- Alan



________________________________________
From: Robertson, Alan L
Sent: Wednesday, December 27, 2017 10:51 AM
To: David Fetter; David G. Johnston
Cc: Amit Kapila; Tom Lane; Postgres Bug
Subject: Re: ALTER SYSTEM for tcp_keepalives_idle doesn't show up with the SHOW command.

So, if I understand correctly, this query would yield one row if it was syntactically correct, and no rows if it had a
syntaxerror. 

And there's nothing which indicates if it is currently activated or not.

Do I understand that correctly?

    Thanks!


________________________________________
From: David Fetter <david@fetter.org>
Sent: Wednesday, December 27, 2017 9:19 AM
To: David G. Johnston
Cc: Amit Kapila; Robertson, Alan L; Tom Lane; Postgres Bug
Subject: Re: ALTER SYSTEM for tcp_keepalives_idle doesn't show up with the SHOW command.

On Wed, Dec 27, 2017 at 09:13:38AM -0700, David G. Johnston wrote:
> On Wed, Dec 27, 2017 at 9:05 AM, David Fetter <david@fetter.org> wrote:
>
> >
> > The following hack works for me.  Apart from being ugly, it's
> > fundamentally unsatisfactory because the question of whether the
> > setting has been activated is left unsettled
> >
>
> Starting with 9.5 we've at least made the parsing of the configuration file
> unnecessary:
>
> https://www.postgresql.org/docs/10/static/view-pg-file-settings.html

Great!  That awful query turns into:

SELECT setting
FROM pg_file_settings
WHERE
    "name" = 'tcp_keepalives_idle' AND
    applied;

...which is a real improvement.

Best,
David.
--
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
E-MAIL CONFIDENTIALITY NOTICE:
The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain
confidentialand/or legally privileged information. If you are not the intended recipient of this message or if this
messagehas been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this
messageand any attachments. If you are not the intended recipient, you are notified that any use, dissemination,
distribution,copying, or storage of this message or any attachment is strictly prohibited. 

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

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)



________________________________________
From: David Fetter <david@fetter.org>
Sent: Wednesday, December 27, 2017 09:19
To: David G. Johnston
Cc: Amit Kapila; Robertson, Alan L; Tom Lane; Postgres Bug
Subject: Re: ALTER SYSTEM for tcp_keepalives_idle doesn't show up with the SHOW command.

On Wed, Dec 27, 2017 at 09:13:38AM -0700, David G. Johnston wrote:
> On Wed, Dec 27, 2017 at 9:05 AM, David Fetter <david@fetter.org> wrote:
>
> >
> > The following hack works for me.  Apart from being ugly, it's
> > fundamentally unsatisfactory because the question of whether the
> > setting has been activated is left unsettled
> >
>
> Starting with 9.5 we've at least made the parsing of the configuration file
> unnecessary:
>
> https://www.postgresql.org/docs/10/static/view-pg-file-settings.html

Great!  That awful query turns into:

SELECT setting
FROM pg_file_settings
WHERE
    "name" = 'tcp_keepalives_idle' AND
    applied;

...which is a real improvement.

Best,
David.
--
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
E-MAIL CONFIDENTIALITY NOTICE:
The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain
confidentialand/or legally privileged information. If you are not the intended recipient of this message or if this
messagehas been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this
messageand any attachments. If you are not the intended recipient, you are notified that any use, dissemination,
distribution,copying, or storage of this message or any attachment is strictly prohibited. 

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

> 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

> 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