Re: pgAdmin hang on connection drop/reconnect

pgAdmin hangs when connection drops while trying to execute a query

Steps to reproduce:

1. Open SQL query pane
2. Type "SELECT pg_sleep(10);"
3. Shut down postgres in less than ten seconds (the query in pgAdmin
will error out)
4. Start postgres
5. Re-run the query

Result: pgAdmin will hang.

Expected behavior: Ideally, automagically reconnect and re-run the
query. Telling the user the connection dropped and having to reconnect
manually would also be acceptable, even if the query pane has to be
closed.

I suppose this is more important to us at Truviso than postgres users in
general (since long-running queries are our bread and butter), but this
should probably have a more user-friendly behavior anyway. Please let me
know if you need more details.

Thanks,
--
Maciek Sakrejda
Truviso, Inc.
http://www.truviso.com

On Mon, Mar 24, 2008 at 5:04 PM, Maciek Sakrejda <msakrejda(at)truviso(dot)com> wrote:
> pgAdmin hangs when connection drops while trying to execute a query
>
> Steps to reproduce:
>
> 1. Open SQL query pane
> 2. Type "SELECT pg_sleep(10);"
> 3. Shut down postgres in less than ten seconds (the query in pgAdmin
> will error out)
> 4. Start postgres
> 5. Re-run the query
>
> Result: pgAdmin will hang.

When I try this, pgAdmin complains that there is no connection and
drops you back at the query window where you can choose a new
connection from the drop down menu.

What platform/version etc. were you using?

--
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk

1.8.1 on Ubuntu 7.10 (x86), compiled from source. I did a fresh compile
(with default configure options) just to make sure I had a clean build,
and this time, instead of hanging, I got a segfault:

(pgadmin3:13290): GLib-GObject-WARNING **: g_type_instance_get_private()
requires a prior call to g_type_class_add_private()
Segmentation fault (core dumped)

I will try 1.8.2 and see if that still happens.
--
Maciek Sakrejda
Truviso, Inc.
http://www.truviso.com

-----Original Message-----
From: Dave Page <dpage(at)pgadmin(dot)org>
To: Maciek Sakrejda <msakrejda(at)truviso(dot)com>
Cc: pgadmin-support(at)postgresql(dot)org
Subject: Re: [pgadmin-support] pgAdmin hang on connection drop/reconnect
Date: Thu, 27 Mar 2008 16:07:58 +0000

On Mon, Mar 24, 2008 at 5:04 PM, Maciek Sakrejda <msakrejda(at)truviso(dot)com> wrote:
> pgAdmin hangs when connection drops while trying to execute a query
>
> Steps to reproduce:
>
> 1. Open SQL query pane
> 2. Type "SELECT pg_sleep(10);"
> 3. Shut down postgres in less than ten seconds (the query in pgAdmin
> will error out)
> 4. Start postgres
> 5. Re-run the query
>
> Result: pgAdmin will hang.

When I try this, pgAdmin complains that there is no connection and
drops you back at the query window where you can choose a new
connection from the drop down menu.

What platform/version etc. were you using?

I was able to reproduce this on 1.8.2, building from source (and I get
the hang this time, not the segfault). A small dialog box comes up to
tell me something (possibly that it's about to segfault), but it's not
drawn correctly (presumably because the refresh drawing routines don't
get called correctly by that point), so I'm not sure what it says. Then
pgAdmin stays like this until I try to close that dialog, at which point
I get the "Not responding/Wait/Force Quit" dialog box from Gnome.

I noticed that I get the following on the command line where I start
pgadmin:

Xlib: unexpected async reply (sequence 0xf810)!
--
Maciek Sakrejda
Truviso, Inc.
http://www.truviso.com

-----Original Message-----
From: Maciek Sakrejda <msakrejda(at)truviso(dot)com>
To: Dave Page <dpage(at)pgadmin(dot)org>
Cc: pgadmin-support(at)postgresql(dot)org
Subject: Re: [pgadmin-support] pgAdmin hang on connection drop/reconnect
Date: Thu, 27 Mar 2008 09:53:29 -0700

1.8.1 on Ubuntu 7.10 (x86), compiled from source. I did a fresh compile
(with default configure options) just to make sure I had a clean build,
and this time, instead of hanging, I got a segfault:

(pgadmin3:13290): GLib-GObject-WARNING **: g_type_instance_get_private()
requires a prior call to g_type_class_add_private()
Segmentation fault (core dumped)

I will try 1.8.2 and see if that still happens.
--
Maciek Sakrejda
Truviso, Inc.
http://www.truviso.com

-----Original Message-----
From: Dave Page <dpage(at)pgadmin(dot)org>
To: Maciek Sakrejda <msakrejda(at)truviso(dot)com>
Cc: pgadmin-support(at)postgresql(dot)org
Subject: Re: [pgadmin-support] pgAdmin hang on connection drop/reconnect
Date: Thu, 27 Mar 2008 16:07:58 +0000

On Mon, Mar 24, 2008 at 5:04 PM, Maciek Sakrejda <msakrejda(at)truviso(dot)com> wrote:
> pgAdmin hangs when connection drops while trying to execute a query
>
> Steps to reproduce:
>
> 1. Open SQL query pane
> 2. Type "SELECT pg_sleep(10);"
> 3. Shut down postgres in less than ten seconds (the query in pgAdmin
> will error out)
> 4. Start postgres
> 5. Re-run the query
>
> Result: pgAdmin will hang.

When I try this, pgAdmin complains that there is no connection and
drops you back at the query window where you can choose a new
connection from the drop down menu.

What platform/version etc. were you using?

On Mon, Mar 31, 2008 at 9:59 PM, Maciek Sakrejda <msakrejda(at)truviso(dot)com> wrote:
> I was able to reproduce this on 1.8.2, building from source (and I get
> the hang this time, not the segfault). A small dialog box comes up to
> tell me something (possibly that it's about to segfault), but it's not
> drawn correctly (presumably because the refresh drawing routines don't
> get called correctly by that point), so I'm not sure what it says. Then
> pgAdmin stays like this until I try to close that dialog, at which point
> I get the "Not responding/Wait/Force Quit" dialog box from Gnome.
>
> I noticed that I get the following on the command line where I start
> pgadmin:
>
> Xlib: unexpected async reply (sequence 0xf810)!

Can you get a backtrace? This might require you to build a debug enabled binary.

--
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk

Hi,

We also have issues with connections ending abrubtly. Not only with pgAdmin
by the way.
We're at the end of a DSL line, and the connection usually has a lot of
hiccups.
After one of those hiccups, you have to wait for at least 30 seconds to find
out what happened, after which you get a correct error message, and a dialog
suggesting to close the server browser.
Sometimes, not quite reproducable, pgAdmin will crash instead of showing the
error message.
During these 30 seconds, during which, presumably, pgAdmin waits for a
server response, interaction with any of the pgAdmin windows is not
possible, so that queries cannot be saved, nor can the request be cancelled.

It seems that the main window in pgAdmin is more sensitive to these hiccups
than the SQL window. Sometimes (it's hard to say exactly when, because you
can't notice the hiccups otherwise) the main window will not work anymore,
whereas the SQL window will.
You can trick pgAdmin to reconnect by pressing the "new" button in the SQL
window, the new window will have a connection (copy paste the query and
close the stale window).
But it would be MUCH nicer (these hiccups are driving us crazy) if pgAdmin
would be less sensitive. For example, reconnect when there is user activity
after connection is lost. Also, it would be nice if there would be the
option to cancel the query once you notice that the connection has been
lost, and the possibility to save your work in those 30-50 secs that
sometimes feel like 30 minutes.

Cheers, btw, i am a great pgAdmin fan.

WBL

On Tue, Apr 1, 2008 at 5:56 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> On Mon, Mar 31, 2008 at 9:59 PM, Maciek Sakrejda <msakrejda(at)truviso(dot)com>
> wrote:
> > I was able to reproduce this on 1.8.2, building from source (and I get
> > the hang this time, not the segfault). A small dialog box comes up to
> > tell me something (possibly that it's about to segfault), but it's not
> > drawn correctly (presumably because the refresh drawing routines don't
> > get called correctly by that point), so I'm not sure what it says. Then
> > pgAdmin stays like this until I try to close that dialog, at which
> point
> > I get the "Not responding/Wait/Force Quit" dialog box from Gnome.
> >
> > I noticed that I get the following on the command line where I start
> > pgadmin:
> >
> > Xlib: unexpected async reply (sequence 0xf810)!
>
> Can you get a backtrace? This might require you to build a debug enabled
> binary.
>
>
> --
> Dave Page
> EnterpriseDB UK Ltd: http://www.enterprisedb.com
> PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk
>
> --
> 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
>

the point i was trying to make is that pgAdmin seems to have the possibility
to restore the connection, since opening a new sql window does the trick.
"All you'd need to do" would be to invoke this behavior when it is
necessary, without opening a new window.

cheers,

WBL

On Mon, Apr 7, 2008 at 6:18 PM, Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:

>
> Hi,
>
> We also have issues with connections ending abrubtly. Not only with
> pgAdmin by the way.
> We're at the end of a DSL line, and the connection usually has a lot of
> hiccups.
> After one of those hiccups, you have to wait for at least 30 seconds to
> find out what happened, after which you get a correct error message, and a
> dialog suggesting to close the server browser.
> Sometimes, not quite reproducable, pgAdmin will crash instead of showing
> the error message.
> During these 30 seconds, during which, presumably, pgAdmin waits for a
> server response, interaction with any of the pgAdmin windows is not
> possible, so that queries cannot be saved, nor can the request be cancelled.
>
> It seems that the main window in pgAdmin is more sensitive to these
> hiccups than the SQL window. Sometimes (it's hard to say exactly when,
> because you can't notice the hiccups otherwise) the main window will not
> work anymore, whereas the SQL window will.
> You can trick pgAdmin to reconnect by pressing the "new" button in the SQL
> window, the new window will have a connection (copy paste the query and
> close the stale window).
> But it would be MUCH nicer (these hiccups are driving us crazy) if pgAdmin
> would be less sensitive. For example, reconnect when there is user activity
> after connection is lost. Also, it would be nice if there would be the
> option to cancel the query once you notice that the connection has been
> lost, and the possibility to save your work in those 30-50 secs that
> sometimes feel like 30 minutes.
>
> Cheers, btw, i am a great pgAdmin fan.
>
> WBL
>
>
>
>
>
>
>
> On Tue, Apr 1, 2008 at 5:56 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
> > On Mon, Mar 31, 2008 at 9:59 PM, Maciek Sakrejda <msakrejda(at)truviso(dot)com>
> > wrote:
> > > I was able to reproduce this on 1.8.2, building from source (and I get
> > > the hang this time, not the segfault). A small dialog box comes up to
> > > tell me something (possibly that it's about to segfault), but it's
> > not
> > > drawn correctly (presumably because the refresh drawing routines
> > don't
> > > get called correctly by that point), so I'm not sure what it says.
> > Then
> > > pgAdmin stays like this until I try to close that dialog, at which
> > point
> > > I get the "Not responding/Wait/Force Quit" dialog box from Gnome.
> > >
> > > I noticed that I get the following on the command line where I start
> > > pgadmin:
> > >
> > > Xlib: unexpected async reply (sequence 0xf810)!
> >
> > Can you get a backtrace? This might require you to build a debug enabled
> > binary.
> >
> >
> > --
> > Dave Page
> > EnterpriseDB UK Ltd: http://www.enterprisedb.com
> > PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk
> >
> > --
> > 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
> >
>
>

I looked at this problem once again with a colleage and came to the
conclusion that the firewall is timing out the connection.

Some other client software packages, like SSH for example, have a mechanism
to manage this problem:

"NAT firewalls like to time out idle sessions to keep their state tables
clean and their memory footprint low. Some firewalls are nice, and let you
idle for up to a day or so; some are gestapo and terminate your session
after 5 minutes. I finally got tired of my ssh sessions getting disconnected
at places where I don't control the firewalls, and figured out how to stop
it. Turn out ssh has a nice inband keepalive mechanism, and even lets you
set it on a per-destination basis. Just create a ~/.ssh/config file with
something like the following (*kehlet.cx is shown as an example only, you
might just use *):

Host *kehlet.cx
ServerAliveInterval 240

That's how often, in seconds, ssh will send a keepalive request (at the
application layer) to the other end if the connection's been otherwise idle.
4 minutes should be good :-). The Host line lets you pattern match your
destinations. Minimal effort, no impact to your system (say, as you would
have if you mucked with your system's default TCP keepalive settings), and
it works like a charm."

Maybe this would be a useful addition to pgAdmin?

Cheers,

WBL

On Thu, Apr 10, 2008 at 11:49 AM, Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:

> the point i was trying to make is that pgAdmin seems to have the
> possibility to restore the connection, since opening a new sql window does
> the trick. "All you'd need to do" would be to invoke this behavior when it
> is necessary, without opening a new window.
>
> cheers,
>
> WBL
>
>
> On Mon, Apr 7, 2008 at 6:18 PM, Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:
>
>>
>> Hi,
>>
>> We also have issues with connections ending abrubtly. Not only with
>> pgAdmin by the way.
>> We're at the end of a DSL line, and the connection usually has a lot of
>> hiccups.
>> After one of those hiccups, you have to wait for at least 30 seconds to
>> find out what happened, after which you get a correct error message, and a
>> dialog suggesting to close the server browser.
>> Sometimes, not quite reproducable, pgAdmin will crash instead of showing
>> the error message.
>> During these 30 seconds, during which, presumably, pgAdmin waits for a
>> server response, interaction with any of the pgAdmin windows is not
>> possible, so that queries cannot be saved, nor can the request be cancelled.
>>
>> It seems that the main window in pgAdmin is more sensitive to these
>> hiccups than the SQL window. Sometimes (it's hard to say exactly when,
>> because you can't notice the hiccups otherwise) the main window will not
>> work anymore, whereas the SQL window will.
>> You can trick pgAdmin to reconnect by pressing the "new" button in the SQL
>> window, the new window will have a connection (copy paste the query and
>> close the stale window).
>> But it would be MUCH nicer (these hiccups are driving us crazy) if pgAdmin
>> would be less sensitive. For example, reconnect when there is user activity
>> after connection is lost. Also, it would be nice if there would be the
>> option to cancel the query once you notice that the connection has been
>> lost, and the possibility to save your work in those 30-50 secs that
>> sometimes feel like 30 minutes.
>>
>> Cheers, btw, i am a great pgAdmin fan.
>>
>> WBL
>>
>>
>>
>>
>>
>>
>>
>> On Tue, Apr 1, 2008 at 5:56 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>>> On Mon, Mar 31, 2008 at 9:59 PM, Maciek Sakrejda <msakrejda(at)truviso(dot)com>
>>> wrote:
>>> > I was able to reproduce this on 1.8.2, building from source (and I get
>>> > the hang this time, not the segfault). A small dialog box comes up to
>>> > tell me something (possibly that it's about to segfault), but it's not
>>> > drawn correctly (presumably because the refresh drawing routines don't
>>> > get called correctly by that point), so I'm not sure what it says.
>>> Then
>>> > pgAdmin stays like this until I try to close that dialog, at which
>>> point
>>> > I get the "Not responding/Wait/Force Quit" dialog box from Gnome.
>>> >
>>> > I noticed that I get the following on the command line where I start
>>> > pgadmin:
>>> >
>>> > Xlib: unexpected async reply (sequence 0xf810)!
>>>
>>> Can you get a backtrace? This might require you to build a debug enabled
>>> binary.
>>>
>>>
>>> --
>>> Dave Page
>>> EnterpriseDB UK Ltd: http://www.enterprisedb.com
>>> PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk
>>>
>>> --
>>> 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
>>>
>>
>>
>

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

There are tcp_keepalives on the server side of postgresql
The problem was that debian waits 2 hours before starting the keepalives
(7200 s).
Also, the connection would only be kept alive for 9*75 s, which is 11 min.

So i changed the tcp_keepalives_idle to 120 (keepalives start after 2 min)
and the tcp_keepalives_count to 200 (max 200 keepalives are sent, every 75
secs, so the connection is kept alive for 120+(200*75) secs, which is 4.2
hours.

It seems to have worked.
I'll check if it works on Windows.

==in postgresql.conf==
# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default

==in man 7 tcp (debian lenny)==
tcp_keepalive_intvl (integer; default: 75)
The number of seconds between TCP keep-alive probes.

tcp_keepalive_probes (integer; default: 9)
The maximum number of TCP keep-alive probes to send before
giving up and killing the connection if no response is obtained from the
other end.

tcp_keepalive_time (integer; default: 7200)
The number of seconds a connection needs to be idle
before TCP begins sending out keep-alive probes. Keep-alives are only sent
when the SO_KEEPALIVE socket
option is enabled. The default value is 7200 seconds (2
hours). An idle connection is terminated after approximately an
additional 11 minutes (9 probes an
interval of 75 seconds apart) when keep-alive is enabled.

Note that underlying connection tracking mechanisms and
application timeouts may be much shorter.

Cheers,

WBL