Обсуждение: Keep-alive?

Perhaps I've missed something - if so, I apologize for wasting the list's time.

Is there some support for keeping PostgreSQL connections alive?
That is, I often start PGAdmin and leave it idle in the background; when I return to it, and hit an item in the tree, I
getabout 10-15 message boxes telling me that the server is offline. 

Are there any plans to change this functionality?

Thanks.
--
+----------------------------------
+ Mark A. Rappoport
+ Software Engineer
+ NSA Internet & Security Ltd.
+ +972-68-523-103
+----------------------------------

> -----Original Message-----
> From: Mark Rappoport [mailto:mark@nsa.co.il]
> Sent: 08 October 2003 12:01
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] Keep-alive?
>
>
> Perhaps I've missed something - if so, I apologize for
> wasting the list's time.
>
> Is there some support for keeping PostgreSQL connections
> alive? That is, I often start PGAdmin and leave it idle in
> the background; when I return to it, and hit an item in the
> tree, I get about 10-15 message boxes telling me that the
> server is offline.
>
> Are there any plans to change this functionality?

Hi Mark,

Neither pgAdmin nor PostgreSQL implement any kind of connection timeout.
Communication is via a TCP connection which is maintained by the TCP
stack on the client and the server. If you are getting disconnected from
your server, then I would guess that there is something else on your
network causing the problem. Ar you working across any ISDN links or
similar?

Regards, Dave.

Hi Dave, thanks for the swift reply.

No, we're working on a server in a farm; the local office connection is
a regular ADSL one, going through a Cisco PIX. The farm is about 4 hops
away.
I suppose that the PIX is somehow affecting the connection... There's
probably a multitude of factors affecting this, and I doubt that it's
the fault of the database backend or pgAdmin... 

Regardless, I think that PGAdmin should offer some kind of option to
automagically reconnect if it sees that the connection is no longer
alive. 

Perhaps there are other pgAdmin users experiencing such disconnections,
who would benefit from this?

Anyway, thanks. I'll look into the TCP streams heading in and out of
here and see what's causing this.

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org 
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Dave Page
> Sent: Wednesday, October 08, 2003 1:07 PM
> To: mark@nsa.co.il; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Keep-alive?
> 
> 
> 
> 
> > -----Original Message-----
> > From: Mark Rappoport [mailto:mark@nsa.co.il]
> > Sent: 08 October 2003 12:01
> > To: pgadmin-support@postgresql.org
> > Subject: [pgadmin-support] Keep-alive?
> > 
> > 
> > Perhaps I've missed something - if so, I apologize for
> > wasting the list's time.
> > 
> > Is there some support for keeping PostgreSQL connections
> > alive? That is, I often start PGAdmin and leave it idle in 
> > the background; when I return to it, and hit an item in the 
> > tree, I get about 10-15 message boxes telling me that the 
> > server is offline.
> > 
> > Are there any plans to change this functionality?
> 
> Hi Mark,
> 
> Neither pgAdmin nor PostgreSQL implement any kind of 
> connection timeout. Communication is via a TCP connection 
> which is maintained by the TCP stack on the client and the 
> server. If you are getting disconnected from your server, 
> then I would guess that there is something else on your 
> network causing the problem. Ar you working across any ISDN 
> links or similar?
> 
> Regards, Dave.
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index 
> scan if your
>       joining column's datatypes do not match
> 

> -----Original Message-----
> From: Mark Rappoport [mailto:mark@nsa.co.il]
> Sent: 08 October 2003 12:19
> To: Dave Page; pgadmin-support@postgresql.org
> Subject: RE: [pgadmin-support] Keep-alive?
>
>
> Hi Dave, thanks for the swift reply.

Hi Mark, you're welcome.

> No, we're working on a server in a farm; the local office
> connection is a regular ADSL one, going through a Cisco PIX.
> The farm is about 4 hops away. I suppose that the PIX is
> somehow affecting the connection... There's probably a
> multitude of factors affecting this, and I doubt that it's
> the fault of the database backend or pgAdmin...
>
> Regardless, I think that PGAdmin should offer some kind of
> option to automagically reconnect if it sees that the
> connection is no longer alive.
>
> Perhaps there are other pgAdmin users experiencing such
> disconnections, who would benefit from this?

It's never been raised before, but I guess it might be useful, and
probably not too difficult to implement. I'll bung it on the todo list.

> Anyway, thanks. I'll look into the TCP streams heading in and
> out of here and see what's causing this.

Good luck!

Regards, Dave.

Mark Rappoport wrote:

>Hi Dave, thanks for the swift reply.
>
>No, we're working on a server in a farm; the local office connection is
>a regular ADSL one, going through a Cisco PIX. The farm is about 4 hops
>away.
>I suppose that the PIX is somehow affecting the connection... There's
>probably a multitude of factors affecting this, and I doubt that it's
>the fault of the database backend or pgAdmin... 
>
>Regardless, I think that PGAdmin should offer some kind of option to
>automagically reconnect if it sees that the connection is no longer
>alive. 
>
>Perhaps there are other pgAdmin users experiencing such disconnections,
>who would benefit from this?
>
>Anyway, thanks. I'll look into the TCP streams heading in and out of
>here and see what's causing this.
>  
>
Obviously I don't know what is specifically causing your connection 
problems, but I can talk about this in the general sense.  I also have a 
similar problem connecting to my servers from my new work network.  
Existing sessions timeout after a couple of minutes or so.  The timeout 
is transparent to the server end, and on my end I get a FIN/ACK which 
appears to come from the remote machine, but must come from some gateway 
here, because the remote machine has no idea the connection is dropped.  
In my instance KeepAlives inside SSH have no effect, and the connection 
still drops.  Sending data down the connection will keep it alive, though.

A problem to consider with pgAdmin automatically re-opening connections 
with the server is this:  the server has no idea the connection has been 
dropped, so it will keep it open until a TCP timeout occurs (which in my 
experience is a *very* long time).  Since the server keeps these 
connections open, any new connections opened will decrease the number of 
available non-superuser connections to the database.  After a few 
timeouts, you will no longer be able to connect to your database.  Bad 
news!!  This happens quite a bit with my IMAP server which only allows 4 
connections from the same IP address.  On this new work network, if the 
IMAP connections get closed by the firewall (this is an assumption since 
I have no idea what sort of firewall/gateway is in use here -- the 
Internet line is run by a separate company than mine), when my IMAP 
application attemps to open some more, it can no longer make an IMAP 
connection to the server, since the server rejects the new connections 
(> 4).  This means that I have to restart my IMAP server severeal times 
a day, a big pain to say the least.

Network problems like this are usually better solved somewhere other 
than the application, since their impact usually affects more than just 
one application.

ahp

Adam H. Pendleton wrote:

>
> Network problems like this are usually better solved somewhere other 
> than the application, since their impact usually affects more than 
> just one application. 


I totally agree on that. Additionally, when we implement some kind of 
reconnect we must be quite careful about it, so that a connection that's 
really terminated doesn't cause a bunch of unnecessary and unwanted 
retries (resulting in 10 minutes sitting in front of your machine, 
waiting for it to recover).

I'd rather like to leave this to the network level. If the link is 
broken, there must be a reason for this: either it's intentionally, or 
it's faulty. Both ways need review.

Regards,
Andreas

This may very well be a firewall/router thing...
Since the amount of sockets (which are needed for any TCP connection) is
limited, your gateway/firewall may very well delete a socket that has been
idle for a certain amount of time.
There is a ICMP message KeepAlive, which does nothing but send some kind of
ping which keeps the connection alive... You cannot send these messages
outside the application, there would be a new socket made for that, so it
would not help you very much. I think that adding an option to PgAdmin
"send keepalive packages every <##> seconds" would be a valuable
addition...

Michiel



|--------+------------------------------------>
|        |          "Mark Rappoport"          |
|        |          <mark@nsa.co.il>          |
|        |          Sent by:                  |
|        |          pgadmin-support-owner@post|
|        |          gresql.org                |
|        |                                    |
|        |                                    |
|        |          08-10-2003 13:18          |
|        |          Please respond to mark    |
|        |                                    |
|--------+------------------------------------>
>-----------------------------------------------------------------------------------------------------------||
                                                                                                | |       To:
"'DavePage'" <dpage@vale-housing.co.uk>, <pgadmin-support@postgresql.org>                  | |       cc:
                                                                                | |       Subject:     Re:
[pgadmin-support]Keep-alive?                                                      |
>-----------------------------------------------------------------------------------------------------------|




Hi Dave, thanks for the swift reply.

No, we're working on a server in a farm; the local office connection is
a regular ADSL one, going through a Cisco PIX. The farm is about 4 hops
away.
I suppose that the PIX is somehow affecting the connection... There's
probably a multitude of factors affecting this, and I doubt that it's
the fault of the database backend or pgAdmin...

Regardless, I think that PGAdmin should offer some kind of option to
automagically reconnect if it sees that the connection is no longer
alive.

Perhaps there are other pgAdmin users experiencing such disconnections,
who would benefit from this?

Anyway, thanks. I'll look into the TCP streams heading in and out of
here and see what's causing this.

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Dave Page
> Sent: Wednesday, October 08, 2003 1:07 PM
> To: mark@nsa.co.il; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Keep-alive?
>
>
>
>
> > -----Original Message-----
> > From: Mark Rappoport [mailto:mark@nsa.co.il]
> > Sent: 08 October 2003 12:01
> > To: pgadmin-support@postgresql.org
> > Subject: [pgadmin-support] Keep-alive?
> >
> >
> > Perhaps I've missed something - if so, I apologize for
> > wasting the list's time.
> >
> > Is there some support for keeping PostgreSQL connections
> > alive? That is, I often start PGAdmin and leave it idle in
> > the background; when I return to it, and hit an item in the
> > tree, I get about 10-15 message boxes telling me that the
> > server is offline.
> >
> > Are there any plans to change this functionality?
>
> Hi Mark,
>
> Neither pgAdmin nor PostgreSQL implement any kind of
> connection timeout. Communication is via a TCP connection
> which is maintained by the TCP stack on the client and the
> server. If you are getting disconnected from your server,
> then I would guess that there is something else on your
> network causing the problem. Ar you working across any ISDN
> links or similar?
>
> Regards, Dave.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index
> scan if your
>       joining column's datatypes do not match
>


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate     subscribe-nomail command to
majordomo@postgresql.orgso that your     message can get through to the mailing list cleanly
 

On Wednesday, Oct 8, 2003, at 07:27 US/Eastern, 
Michiel_Lange@actuera.nl wrote:

> I think that adding an option to PgAdmin
> "send keepalive packages every <##> seconds" would be a valuable
> addition...
>

I think this would be a much more viable solution than re-connecting to 
the database.

ahp

> -----Original Message-----
> From: Adam H.Pendleton [mailto:fmonkey@fmonkey.net]
> Sent: 09 October 2003 03:25
> To: Michiel_Lange@actuera.nl
> Cc: mark@nsa.co.il; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Keep-alive?
>
>
> On Wednesday, Oct 8, 2003, at 07:27 US/Eastern,
> Michiel_Lange@actuera.nl wrote:
>
> > I think that adding an option to PgAdmin "send keepalive packages
> > every <##> seconds" would be a valuable addition...
> >
>
> I think this would be a much more viable solution than
> re-connecting to
> the database.

No network level solution is viable for us unless we want to maintain
our own replacement for libpq, besides which, even if we did implement
some kind of icmp heartbeat, there's no guarantee it would help because
it would be completely independent from any TCP connections - a firewall
might still decide to close what it thinks are idle connections inspite
of icmp messages.

Istm that there are 2 options:

1) Implement reconnects in pgConn.cpp. The major objection to this is
that the server may not notice that the original connection is dead, and
thus a number of dead connections may build up until the connection
limit on the server is reached. I think this problem is actually a bit
irrelevant - as things stand at the moment, if you connection drops, you
manually reconnect as soon as you need to. How many of us ever go and
look for dead connections on the server, and even if you did, and found
some, what would you do about it short of restarting the server?

2) Implement SQL level keep alives. This could a be simple SELECT
version() query that is executed automatically after n seconds of
inactivity.

Thoughts?

Regards, Dave.

Dave Page wrote:

>2) Implement SQL level keep alives. This could a be simple SELECT
>version() query that is executed automatically after n seconds of
>inactivity.
>
>  
>
This is what I had in mind, an application (i.e., layer 7) level keep-alive.

ahp

> -----Original Message-----
> From: Adam H. Pendleton [mailto:fmonkey@fmonkey.net]
> Sent: 09 October 2003 13:31
> To: Dave Page
> Cc: Michiel_Lange@actuera.nl; mark@nsa.co.il;
> pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Keep-alive?
>
> Dave Page wrote:
>
> >2) Implement SQL level keep alives. This could a be simple SELECT
> >version() query that is executed automatically after n seconds of
> >inactivity.
> >
> >
> >
> This is what I had in mind, an application (i.e., layer 7)
> level keep-alive.

OK, just fiddling around here - attached is a patch that implements a
heartbeat on the master connection to each server. Currently it beats
every 5 seconds, though this would be made configurable/disable-able.

It doesn't actually do much other than throw an error message if it gets
an unexpected response.

Thoughts, comments?

Regards, Dave.

Dave Page wrote:

> 
>
>  
>
>>-----Original Message-----
>>From: Adam H. Pendleton [mailto:fmonkey@fmonkey.net] 
>>Sent: 09 October 2003 13:31
>>To: Dave Page
>>Cc: Michiel_Lange@actuera.nl; mark@nsa.co.il; 
>>pgadmin-support@postgresql.org
>>Subject: Re: [pgadmin-support] Keep-alive?
>>
>>Dave Page wrote:
>>
>>    
>>
>>>2) Implement SQL level keep alives. This could a be simple SELECT
>>>version() query that is executed automatically after n seconds of 
>>>inactivity.
>>>
>>> 
>>>
>>>      
>>>
>>This is what I had in mind, an application (i.e., layer 7) 
>>level keep-alive.
>>    
>>
>
>OK, just fiddling around here - attached is a patch that implements a
>heartbeat on the master connection to each server. Currently it beats
>every 5 seconds, though this would be made configurable/disable-able.
>
>It doesn't actually do much other than throw an error message if it gets
>an unexpected response.
>
>Thoughts, comments?
>
Yes, and a harsh one :-(

This is just what I meant that should *not* be implemented.
- It will throw an error every 5 seconds, after blocking the connection 
for a failed heartbeat. Implemented this way, it's more a watchdog, that 
won't stop barking.
- If a broken conn is detected, it must be marked dead.
- This feature is exotic, and must not be active by default.
- 5 seconds is ridiculously short.
- it should fire after n seconds of inactivity, not every n seconds
- this works only for the main window, not for those getting their own 
connection. The wxWin timer will conflict with the threaded executed 
queries, because it's not aware of previously or currently executed queries.
- Does it work at all? pgConn is no window, so where should the message 
come from?

Regards,
Andrea

> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: 10 October 2003 11:12
> To: Dave Page
> Cc: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Keep-alive?
>
> Yes, and a harsh one :-(
>
> This is just what I meant that should *not* be implemented.
> - It will throw an error every 5 seconds, after blocking the
> connection for a failed heartbeat. Implemented this way, it's
> more a watchdog, that won't stop barking.

I did say I was only fiddling, hence why I posted a demo patch rather
than just committing code. I was after feedback before spending too much
time on it.

> - If a broken conn is detected, it must be marked dead.

Yes, of course.

> - This feature is exotic, and must not be active by default.

Agreed.

> - 5 seconds is ridiculously short.

Obviously you didn't read my email where I clearly stated that this
would not remain hard coded. For testing purposes 5 seconds is fine.

> - it should fire after n seconds of inactivity, not every n seconds

Yes, it should and it was. I must have lost part of the code.

> - this works only for the main window, not for those getting
> their own connection. The wxWin timer will conflict with the
> threaded executed queries, because it's not aware of
> previously or currently executed queries.

It purposefully only works on the master connection to each server as
there is no point in individually polling individual databases on the
same server. Unless you've changed the code from what I originally
wrote, the master connection will never be used for threaded queries.

> - Does it work at all? pgConn is no window, so where should
> the message come from?

Yes of course it works, I didn't just randomly type code and post it:

class pgConn : wxFrame

Regards, Dave.

Dave Page wrote:

>  
>
>>- this works only for the main window, not for those getting 
>>their own connection. The wxWin timer will conflict with the 
>>threaded executed queries, because it's not aware of 
>>previously or currently executed queries.
>>    
>>
>
>It purposefully only works on the master connection to each server as
>there is no point in individually polling individual databases on the
>same server.
>
Each database has its own connection to the server (just checked with 
netstat), and thus is individually target of a firewall surveillance/ 
forced tcp disconnect.

> Unless you've changed the code from what I originally
>wrote, the master connection will never be used for threaded queries.
>
Wouldn't make sense to thread them too, but what about disconnects on 
Query Tool, Data Grid etc?

>
>  
>
>>- Does it work at all? pgConn is no window, so where should 
>>the message come from?
>>    
>>
>
>Yes of course it works, I didn't just randomly type code and post it:
>
>class pgConn : wxFrame
>

Deriving from wxFrame doesn't mean it gets events, because the default 
wxFrame constructor won't create a window, so it's no window in win32's 
sense. I just checked the msw implementation of wxTimer, it doesn't use 
the message loop, but a callback proc, that's why it's working. Still, 
declaring pgConn to a kind-of window doesn't make me feel too 
comfortable (wxEvtHandler should be sufficient). I'd rather like to have 
the pg classes free of all this sm_xxx stuff.

Regards,
Andreas

> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: 10 October 2003 13:53
> To: Dave Page
> Cc: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Keep-alive?
>
>
> Each database has its own connection to the server (just
> checked with netstat), and thus is individually target of a
> firewall surveillance/ forced tcp disconnect.

Good point, same problem as the icmp keep alives.

> > Unless you've changed the code from what I originally wrote, the
> >master connection will never be used for threaded queries.
> >
> Wouldn't make sense to thread them too, but what about
> disconnects on Query Tool, Data Grid etc?

Dunno what you mean by diconnects in that context, but as you pointed
out, requiring the heartbeat on all connections kinda throws the whole
idea in the bin anyway as we couldn't then use asynchronous queries in
those (or other) tools.

I don't see a way around that...

> Deriving from wxFrame doesn't mean it gets events, because
> the default wxFrame constructor won't create a window, so
> it's no window in win32's sense. I just checked the msw
> implementation of wxTimer, it doesn't use the message loop,
> but a callback proc, that's why it's working. Still,
> declaring pgConn to a kind-of window doesn't make me feel too
> comfortable (wxEvtHandler should be sufficient). I'd rather
> like to have the pg classes free of all this sm_xxx stuff.

Deriving from wxFrame was not something that I was overly happy with
either, but it was looking a heck of a lot cleaner in deriving a new
class from wxTimer and overloading the OnTimer member (or whatever it's
called).

Anyway, the reason I posted the patch was to gather ideas and feedback,
not to propose it as a final solution (as your initial comments indicate
you thought). I don't see any way to implement this cleanly at this
layer now so unless you or anyone else has any bright ideas I'll scrub
it from the todo list as unworkable, and move one...

Cheers, Dave.

Dave Page wrote:

>
>Anyway, the reason I posted the patch was to gather ideas and feedback,
>not to propose it as a final solution (as your initial comments indicate
>you thought). I don't see any way to implement this cleanly at this
>layer now so unless you or anyone else has any bright ideas I'll scrub
>it from the todo list as unworkable, and move one...
>  
>

pgAdmin3 is just another app, concerning the database connection. So if 
pgAdmin3 suffers from broken links, any other libpq app would too. It 
would make sense to implement this in libpq.
If we wanted to implement this in pgAdmin3, we'd need a global keepalive 
thread, scanning all connections if a keep alive query is needed, 
interlocking with a semaphor to prevent interference with wanted 
queries. This thread also could silently handle reconnects.


Regards,
Andreas

Hi,

Dave Page wrote:
>  
> 
> 
>>-----Original Message-----
>>From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] 
>>Sent: 10 October 2003 13:53
>>To: Dave Page
>>Cc: pgadmin-support@postgresql.org
>>Subject: Re: [pgadmin-support] Keep-alive?
>>
>>
>>Each database has its own connection to the server (just 
>>checked with netstat), and thus is individually target of a 
>>firewall surveillance/ forced tcp disconnect.
> 
> 
> Good point, same problem as the icmp keep alives.
> 
To avoid to much concentration of that not very common
matter - a firewall not in control of the DBA, but
configured the way do enforce keep alive -
this should be handled by the DBA itself.
A solution would be to just establish a connection
forwarding via SSH and a small shell script running
in the control channel (shell) outputting
some letters in a loop with delay of some seconds.

The only thing which would be nice to have at
least on windows, would be support for
socks protocol.
tsocks works good on linux, but I have yet to
see any equivalent of this for win32.

Just my 0.0002c ;)

Regards
Tino