Обсуждение: BUG #2883: TCP Keep Alive not enabled even after setting it in postresql.conf

The following bug has been logged online:

Bug reference:      2883
Logged by:          Christopher Brian Jurado
Email address:      chris.jurado@primesoft.ph
PostgreSQL version: 8.1
Operating system:   Windows 2000/XP
Description:        TCP Keep Alive not enabled even after setting it in
postresql.conf
Details:

The TCP keepalives do not work or is not enabled.

For Windows, in order to control the keepalive settings, you have to add
some entries to its registry. But, its TCP keepalive is disabled by default.
It has to be enabled for the socket using the setsockopt() command.

Did you guys add the setsockopt() command to enable the SO_KEEPALIVE option?
could this setting be added in the postgresql.conf?

We are having touble with this because if a client's network connection
fails, and the client still has an active transaction with locks, those
locks are never released!

As a reference, here is the info about TCP Keepalive Messages on the
Microsoft Knowledgebase:

"TCP Keepalive Messages

A TCP keepalive packet is simply an ACK with the sequence number set to one
less than the current sequence number for the connection. A computer
receiving one of these ACKs should respond with an ACK for the current
sequence number. Keepalives can be used to verify that the computer at the
remote end of a connection is still available. TCP keepalives can be sent
once every KeepAliveTime (defaults to 7,200,000 milliseconds or two hours),
if no other data or higher level keepalives have been carried over the TCP
connection. If there is no response to a keepalive, it is repeated once
every KeepAliveInterval seconds. KeepAliveInterval defaults to one second.
NetBT connections, such as those used by many Microsoft networking
components, send NetBIOS keepalives more frequently, and so normally no TCP
keepalives will be sent on a NetBIOS connection. TCP keepalives are disabled
by default, but Windows Sockets programs may enable them using setsockopt().
"

Your report is a little confusing.  The "Description" says you modified
postgreql.conf, but your text asks that it be added to postgresql.conf

I see this in the 8.1 release notes:

     * Add configuration parameters to control TCP/IP keep-alive times
       for idle, interval, and count (Oliver Jowett)

       These values can be changed to allow more rapid detection of lost
       client connections.

Are you using these postgresql.conf settings?  I do see a setsockopt()
call in pq_setkeepalivesidle(), so I am confused why it isn't working
for you.

---------------------------------------------------------------------------

Christopher Brian Jurado wrote:
>
> The following bug has been logged online:
>
> Bug reference:      2883
> Logged by:          Christopher Brian Jurado
> Email address:      chris.jurado@primesoft.ph
> PostgreSQL version: 8.1
> Operating system:   Windows 2000/XP
> Description:        TCP Keep Alive not enabled even after setting it in
> postresql.conf
> Details:
>
> The TCP keepalives do not work or is not enabled.
>
> For Windows, in order to control the keepalive settings, you have to add
> some entries to its registry. But, its TCP keepalive is disabled by default.
> It has to be enabled for the socket using the setsockopt() command.
>
> Did you guys add the setsockopt() command to enable the SO_KEEPALIVE option?
> could this setting be added in the postgresql.conf?
>
> We are having touble with this because if a client's network connection
> fails, and the client still has an active transaction with locks, those
> locks are never released!
>
> As a reference, here is the info about TCP Keepalive Messages on the
> Microsoft Knowledgebase:
>
> "TCP Keepalive Messages
>
> A TCP keepalive packet is simply an ACK with the sequence number set to one
> less than the current sequence number for the connection. A computer
> receiving one of these ACKs should respond with an ACK for the current
> sequence number. Keepalives can be used to verify that the computer at the
> remote end of a connection is still available. TCP keepalives can be sent
> once every KeepAliveTime (defaults to 7,200,000 milliseconds or two hours),
> if no other data or higher level keepalives have been carried over the TCP
> connection. If there is no response to a keepalive, it is repeated once
> every KeepAliveInterval seconds. KeepAliveInterval defaults to one second.
> NetBT connections, such as those used by many Microsoft networking
> components, send NetBIOS keepalives more frequently, and so normally no TCP
> keepalives will be sent on a NetBIOS connection. TCP keepalives are disabled
> by default, but Windows Sockets programs may enable them using setsockopt().
> "
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +