close inactive client connection

Luiz Bernardi wrote:

I am developing a system, using the ZeosLib, which maintains a
permanent connection to the database.

When the client loses the connection, the server does not recognize
this and keeps the connection as if it were active. When the client
attempts to reconnect, the server creates a new connection and the
previous remains active.

how does this happen ? TCP connections don't just wander off and get lost.

This has caused an excessive increase in the number of active
connections and hampered the use of the system.

Have any way or setting to make postgres close idle connections?

first, you'd have to identify that they were in fact 'lost' and not just
idle. we have apps that open a socket to postgres, issue some
commands, then sometimes sit for hours before more commands are issued.

On Fri, Sep 25, 2009 at 6:26 AM, Luiz Bernardi <lb@cplinformatica.com> wrote:

Hello,

I am developing a system, using the ZeosLib, which maintains a permanent
connection to the database.

When the client loses the connection, the server does not recognize this and
keeps the connection as if it were active. When the client attempts to
reconnect, the server creates a new connection and the previous remains
active.

Eventually the server's network stack will check to see if the
connection is alive or not by sending a tcp keepalive ping. If it
receives no answers after x number of tries, it will close the socket
and the pg backend will be terminated.

To adjust this time, look at the tcp keepalive parameters in either
the postgresql.conf file or system wide on your server. The default
timeout is 2 hours. It's not unusual to drop it to 5 or 10 minutes on
busy systems.

Thanks, Scott.

I change the setting and then find out why they were losing the
client connection.
--
Luiz Agnaldo Bernardi
Fone 41 36755756
���� 41 99979849

On Fri, 25 Sep 2009 11:11:52 -0600
�Scott Marlowe <scott.marlowe@gmail.com> wrote:

hi John

I have no idea of what may be happening. The system opens a connection
and communicate normally. But after an idle time, it loses the
connection and new transactions return with error.

16/09/2009 13:39:14 - SQL Error: no connection to the server
16/09/2009 13:39:14 - SQL Error: connection not open

At this point he reconnects, the server creates a new connection that
behaves exactly like the previous one.

--
Luiz Agnaldo Bernardi
Fone 41 36755756
���� 41 99979849

On Fri, 25 Sep 2009 10:03:20 -0700
�John R Pierce <pierce@hogranch.com> wrote:

Are these machines communicating through a firewall? Often firewalls
timeout idle tcp/ip connections.

On Fri, Sep 25, 2009 at 12:56 PM, Luiz Bernardi <lb@cplinformatica.com> wrote:

hi John

I have no idea of what may be happening. The system opens a connection and
communicate normally. But after an idle time, it loses the connection and
new transactions return with error.
 16/09/2009 13:39:14 - SQL Error: no connection to the server
16/09/2009 13:39:14 - SQL Error: connection not open

At this point he reconnects, the server creates a new connection that
behaves exactly like the previous one.

--
Luiz Agnaldo Bernardi
Fone 41 36755756
     41 99979849

On Fri, 25 Sep 2009 10:03:20 -0700
 John R Pierce <pierce@hogranch.com> wrote:

Luiz Bernardi wrote:

I am developing a system, using the ZeosLib, which maintains a permanent
connection to the database.

When the client loses the connection, the server does not recognize this
and keeps the connection as if it were active. When the client attempts to
reconnect, the server creates a new connection and the previous remains
active.

how does this happen ?   TCP connections don't just wander off and get
lost.

This has caused an excessive increase in the number of active connections
and hampered the use of the system.

Have any way or setting to make postgres close idle connections?

first, you'd have to identify that they were in fact 'lost' and not just
idle.   we have apps that open a socket to postgres, issue some commands,
then sometimes sit for hours before more commands are issued.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
When fascism comes to America, it will be intolerance sold as diversity.