Re: pgAdmin ver 1.10 freezing all too frequently

I did a bit more checking and there appears to be some server configurations
that may affect keeping the connection alive:

tcp_keepalives_idle (integer)

On systems that support the TCP_KEEPIDLE socket option, specifies the number
of seconds between sending keepalives on an otherwise idle connection. A
value of zero uses the system default. If TCP_KEEPIDLE is not supported,
this parameter must be zero. This parameter is ignored for connections made
via a Unix-domain socket.

tcp_keepalives_interval (integer)

On systems that support the TCP_KEEPINTVL socket option, specifies how long,
in seconds, to wait for a response to a keepalive before retransmitting. A
value of zero uses the system default. If TCP_KEEPINTVL is not supported,
this parameter must be zero. This parameter is ignored for connections made
via a Unix-domain socket.

tcp_keepalives_count (integer)

On systems that support the TCP_KEEPCNT socket option, specifies how many
keepalives can be lost before the connection is considered dead. A value of
zero uses the system default. If TCP_KEEPCNT is not supported, this
parameter must be zero. This parameter is ignored for connections made via a
Unix-domain socket.

You can determine the settings for a server by running the following:

select * from pg_settings where name like 'tcp%'

On Wed, Nov 11, 2009 at 9:21 PM, Michael Shapiro <mshapiro51(at)gmail(dot)com>wrote:

> I checked the server logs and found messages like this:
>
> 2009-11-11 15:08:36 PSTLOG: could not receive data from client: Connection
> reset by peer
> 2009-11-11 16:18:11 PSTLOG: could not receive data from client: Connection
> timed out
>
> I do not know if this means that the server did the disconnect or the
> network did it (I suspect the network).
> This seems to imply that we are at the mercy of either the network admins
> or the server admins. In either case, it is problematic since a lost
> connection is not handled well in PgAdmin.
>
>
> On Wed, Nov 11, 2009 at 5:11 PM, Michael Shapiro <mshapiro51(at)gmail(dot)com>wrote:
>
>> I was able to reproduce the problem within PgAdmin as well as with another
>> (java-based) tool connecting to the same database from the same (Windows XP)
>> machine.
>>
>> I opened an query window and ran a query (that did not take long to
>> complete) in both tools. I then waited 1/2 hour and ran the query again.
>> PgAdmin and the other tool both saw that the connection had been dropped.
>> PgAdmin will continue to run and work if I open a new query window, but if I
>> insist on running the query again in the same window it fails. Moreover, if
>> I insist twice, PgAdmin crashes. The java-based tool re-established the
>> connection, and gets the query results fine, but detects an I/O error while
>> sending the query to the postgres backend. It will continue to function and
>> run queries and get results, but each time it sees an I/O error.
>>
>> 17:05:35 [ERROR AWT-EventQueue-1 H.?] isEditable Error: An I/O error
>> occurred while sending to the backend.
>>
>>
>>
>> I realize that unless the development team can reproduce the problem, it
>> is hard to fix, but at least it appears that the connection being dropped is
>> not caused by PgAdmin itself. Seems like the postgres server may be doing
>> it. I have asked the server admins if the server does automatic timeouts for
>> idle connections.
>>
>>
>> On Wed, Nov 11, 2009 at 9:20 AM, Michael Shapiro <mshapiro51(at)gmail(dot)com>wrote:
>>
>>> It just happened again. I ran a query in a window and after it completed
>>> I waited 10-15 minutes and ran it again.
>>> I got a connection error. I ran the query again (by hitting the green >
>>> icon) and it caused pgAdmin to crash.
>>>
>>> Moreover, I was simultaneously running another tool that was connected to
>>> a non-Postgres database. After PgAdmin crashed I ran a query in the other
>>> tool. It ran fine. This implies that the connection isn't being dropped by
>>> my router due to inactivity. It is probably happening within the Postgres
>>> server.
>>>
>>> I will try to run this test again using PgAdmin and a different tool that
>>> can connect to the same Postgres database.
>>>
>>>
>>>
>>> On Wed, Nov 11, 2009 at 8:32 AM, Michael Shapiro <mshapiro51(at)gmail(dot)com>wrote:
>>>
>>>> It happens to me quite a lot, as well. I will lose the contents of the
>>>> SQL window as well when it happens.
>>>>
>>>> It would be good if PgAdmin would automatically try to reconnect (at
>>>> least once) before giving up.
>>>>
>>>>
>>>> On Wed, Nov 11, 2009 at 8:08 AM, Eugene Lisitsky <lisitsky(at)gmail(dot)com>wrote:
>>>>
>>>>>
>>>>> I see such problem when remote servers replies slowly.
>>>>>
>>>>>
>>>>> 2009/11/1 Pedro Doria Meunier <pdoria(at)netmadeira(dot)com>
>>>>>
>>>>>> -----BEGIN PGP SIGNED MESSAGE-----
>>>>>> Hash: SHA1
>>>>>>
>>>>>> Replying to self:
>>>>>> Altering the tcp_keepalive_time, tcp_keepalive_intvl values *didn't*
>>>>>> solve the problem.
>>>>>>
>>>>>>
>>>>>> On 11/01/2009 03:04 PM, Pedro Doria Meunier wrote:
>>>>>> >
>>>>>> > Sorry forgot to tell it happens on a remote connection... :)
>>>>>> >
>>>>>> > This is AFAICS is most probably a timeout problem. It makes me
>>>>>> > wonder if my router isn't dropping idle connections...
>>>>>> >
>>>>>> > Anyway I'm playing with tcp_keepalive_time, tcp_keepalive_intvl
>>>>>> > values to see if it solves the problem. btw: tcp_keepalive_time =
>>>>>> > 75 tcp_keepalive_intvl = 60
>>>>>> >
>>>>>> >
>>>>>> > I'll post my results.
>>>>>> >
>>>>>> > BR, Pedro Doria Meunier
>>>>>> >
>>>>>> >
>>>>>> > On 11/01/2009 12:37 PM, Dave Page wrote:
>>>>>> >> On Sun, Nov 1, 2009 at 11:04 AM, Pedro Doria Meunier
>>>>>> >> <pdoria(at)netmadeira(dot)com> wrote:
>>>>>> >>> -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
>>>>>> >>>
>>>>>> >>> Hi,
>>>>>> >>>
>>>>>> >>> pgAdmin ver 1.10.0 rev. 7945:7946 freezes after a short time
>>>>>> >>> of inactivity... Fedora 11 - kernel 2.6.30.9-90.fc11.i686.PAE
>>>>>> >>>
>>>>>> >>> Reproducible by: - - connect to a db - - click on any of the
>>>>>> >>> db's objects - - wait a while (1-3 minutes) - - refresh the
>>>>>> >>> object
>>>>>> >>>
>>>>>> >>> kaboom. it freezes. have to force termination.
>>>>>> >
>>>>>> >> I only have 64 bit CentOS 5 here at the moment, but I can't
>>>>>> >> reproduce on that. Anyone else?
>>>>>> >
>>>>>> >>> Setting the log to "debug" offers no joy as to discover the
>>>>>> >>> cause...
>>>>>> >>>
>>>>>> >>> I'm unsure as if it's a pgAdmin problem or rather a connection
>>>>>> > timeout...
>>>>>> >>> At any rate... does pgAdmin make any attempt to KEEP_ALIVE
>>>>>> >>> (the connection)?
>>>>>> >
>>>>>> >> No.
>>>>>> >
>>>>>> -----BEGIN PGP SIGNATURE-----
>>>>>> Version: GnuPG v1.4.9 (GNU/Linux)
>>>>>> Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/
>>>>>>
>>>>>> iEYEARECAAYFAkrtq3EACgkQ2FH5GXCfxAuPUQCdExxsfhj3pyrCXuK0MqB0Sm9w
>>>>>> xsIAoI3N5xHOiHlGQC6HfW2BtPXpTtlt
>>>>>> =gNcZ
>>>>>> -----END PGP SIGNATURE-----
>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Sent via pgadmin-support mailing list (pgadmin-support(at)postgresql(dot)org
>>>>>> )
>>>>>> To make changes to your subscription:
>>>>>> http://www.postgresql.org/mailpref/pgadmin-support
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Yours,
>>>>> Eugene Lisitsky
>>>>>
>>>>
>>>>
>>>
>>
>