Thread: connection dropping continued
Hello! Referring to http://www.mail-archive.com/pgadmin-support@postgresql.org/msg02050.html , I'd like to ask if there's going to be some remedy to the problem any time in the near future? We're using PGAdmin III and it's really an extremely useful admin-tool for PostgeSQL, but this issue is really quite unnerving, though it may not be the apllication's fault at all: > I get excellent performance, however, I find > that pgAdmin drops the DB connection rather quickly if there is no > activity. If I leave the interface for any amount of time, when I come > back and click on anything, I get the error message: > "An error has occurred: > > server closed the connection unexpectedly > This probably means the server terminated abnormally before of while > processing the request." > > After this I usually have to click through many dialog boxes telling me > that there is no connection to the server. We're behind a firewall on a connection which is routed securely between two internal networks - our servers are sitting in one and we are on the other one. Our admin tells us that there's really no way he would be willing or able to somehow fix this firewall problem; right now, what we'd really like to see would be some option for keeping our connections alive inside PGAdmin III - maybe some additional "Keep-Alive"-settings unter options where one could switch the feature on or off and define the interval for the requests to the database-clusters which are currently active in the servers-tab. Just some "select '1';" to template1 every 30 seconds would be more than sufficient to resolve the problem for us. Any chance for this to be included some time soon? Kind regards and thanks for this excellent piece of software, Markus
On Tuesday, February 17, 2004 13:03, Markus Wollny wrote: > Referring to > http://www.mail-archive.com/pgadmin-support@postgresql.org/msg02050.html > , I'd like to ask if there's going to be some remedy to the problem any > time in the near future? We're using PGAdmin III and it's really an > extremely useful admin-tool for PostgeSQL, but this issue is really > quite unnerving, though it may not be the apllication's fault at all: We have this problem also between developers (WinXP and Win2K) and our FreeBSD server (on another network, behind firewalls, using SSL, but not NATed), and it ends up filling up all the connections to the DB so nobody can get in until Postgres is restarted. It didn't seem to happen to me on Linux, but I didn't use it as much as the devs did, so it may/may not be linked to Windows as well. It's really a hassle, especially when developing. No other client has the problem, be it psql, mysql, mysqlcc, or any other program like ssh or ftp. We lost many hours testing, changing config settings and kernel options, testing networking stuff, and generally just dealing with this problem. We got the DB done eventually so it "went away". Thankfully Apache/PHP is now the only DB client which connects locally and has no problems. Don't get my wrong, I like pgadmin, it's a great admin tool, and version 3 is pretty slick. I know the problem isn't necessarily a pgadmin issue, and seems more network-related, but a keep-alive option, possibly with user-definable SQL statement or something, would be nice to have (would help troubleshoot problems at least). Josh
Josh Endries wrote: >On Tuesday, February 17, 2004 13:03, Markus Wollny wrote: > > >>Referring to >>http://www.mail-archive.com/pgadmin-support@postgresql.org/msg02050.html >>, I'd like to ask if there's going to be some remedy to the problem any >>time in the near future? We're using PGAdmin III and it's really an >>extremely useful admin-tool for PostgeSQL, but this issue is really >>quite unnerving, though it may not be the apllication's fault at all: >> >> > >We have this problem also between developers (WinXP and Win2K) and our >FreeBSD server (on another network, behind firewalls, using SSL, but not >NATed), and it ends up filling up all the connections to the DB so nobody >can get in until Postgres is restarted. It didn't seem to happen to me on >Linux, but I didn't use it as much as the devs did, so it may/may not be >linked to Windows as well. It's really a hassle, especially when >developing. No other client has the problem, be it psql, mysql, mysqlcc, >or any other program like ssh or ftp. We lost many hours testing, changing >config settings and kernel options, testing networking stuff, and >generally just dealing with this problem. We got the DB done eventually so >it "went away". Thankfully Apache/PHP is now the only DB client which >connects locally and has no problems. > >Don't get my wrong, I like pgadmin, it's a great admin tool, and version 3 >is pretty slick. I know the problem isn't necessarily a pgadmin issue, and >seems more network-related, but a keep-alive option, possibly with >user-definable SQL statement or something, would be nice to have (would >help troubleshoot problems at least). > > > This is *not* an pgAdmin issue, any other app will suffer the same problem if crossing that firewall. Your network is broken, contact your system administrator to fix the firewall. We're using libpq, which doesn't offer such keep-alive option, because it relies on TCP/IP which by definition delivers a solid connection, unless aborted deliberately by a malfunctioning firewall or router. Regards, Andreas
Hi! > -----Ursprüngliche Nachricht----- > Von: Andreas Pflug [mailto:pgadmin@pse-consulting.de] > Gesendet: Dienstag, 17. Februar 2004 17:40 > An: Josh Endries > Cc: pgadmin-support@postgresql.org; Markus Wollny > Betreff: Re: [pgadmin-support] connection dropping continued > This is *not* an pgAdmin issue, any other app will suffer the same > problem if crossing that firewall. You're absolutely right insofar as this is not _caused_ by PGAdmin III; other apps (we're using some oldish version of SQL Navigator to connect to an Oracle 8i DB) show the exactsame behaviour. > Your network is broken, contact your > system administrator to fix the firewall. We're using libpq, which > doesn't offer such keep-alive option, because it relies on > TCP/IP which > by definition delivers a solid connection, unless aborted > deliberately > by a malfunctioning firewall or router. I wouldn't call that malfunctioning, the behaviour of the firewall is intended and does make sense, as it would open some possibilities for DOS-attacks if there would be no timeout. So even though it's not caused by PGAdmin III, it could be resolved in the application level without the need to interfere with firewalls - and I think that there must be a lot of people who don't have sufficient access to their firewalls or routers in order to resolve the issue there. I'm not saying that this is a vital feature for PGAdmin III to have, I'm not saying that the software is crappy because the connection times out. All I'm saying is that some sort of keep-alive-mechanism would be a handy feature to have in PGAdmin III. And there's really no need to establish some complicated mechanism on the network-protocol level to get the desired results, a simple "select 1;" issued every 30 odd seconds to all opened databases would be absolutely sufficient, I should think. If that feature is off by default and can be switched on (and maybe the interval adjusted according to needs), no one would be bothered by it either. Kind regards Markus
Markus Wollny wrote: >Hi! > > > >>-----Ursprüngliche Nachricht----- >>Von: Andreas Pflug [mailto:pgadmin@pse-consulting.de] >>Gesendet: Dienstag, 17. Februar 2004 17:40 >>An: Josh Endries >>Cc: pgadmin-support@postgresql.org; Markus Wollny >>Betreff: Re: [pgadmin-support] connection dropping continued >> >> > > > >>This is *not* an pgAdmin issue, any other app will suffer the same >>problem if crossing that firewall. >> >> > >You're absolutely right insofar as this is not _caused_ by PGAdmin III; >other apps (we're using some oldish version of SQL Navigator to connect >to an Oracle 8i DB) show the exactsame behaviour. > > > >>Your network is broken, contact your >>system administrator to fix the firewall. We're using libpq, which >>doesn't offer such keep-alive option, because it relies on >>TCP/IP which >>by definition delivers a solid connection, unless aborted >>deliberately >>by a malfunctioning firewall or router. >> >> > >I wouldn't call that malfunctioning, > So call it ill-configured. >the behaviour of the firewall is >intended and does make sense, as it would open some possibilities for >DOS-attacks if there would be no timeout. > >So even though it's not caused by PGAdmin III, it could be resolved in >the application level without the need to interfere with firewalls - > Wrong sight; the firewall is the interferer, screwing up tcpip. Simply let it forward according to RFCs, and everything's fine. >and >I think that there must be a lot of people who don't have sufficient >access to their firewalls or routers in order to resolve the issue >there. > >I'm not saying that this is a vital feature for PGAdmin III to have, I'm >not saying that the software is crappy because the connection times out. >All I'm saying is that some sort of keep-alive-mechanism would be a >handy feature to have in PGAdmin III. And there's really no need to >establish some complicated mechanism on the network-protocol level to >get the desired results > Wrong way; it's extra effort to *kill* the connection! >, a simple "select 1;" issued every 30 odd >seconds to all opened databases would be absolutely sufficient, I should >think. If that feature is off by default and can be switched on (and >maybe the interval adjusted according to needs), no one would be >bothered by it either. > > > We can not and thus will not implement app level keep-alives. You can try to head over to pgsql-bugs or pgsql-hackers, to recommend implementing that in libpq, and you certainly will get the same answer: FIX THE FIREWALL! The server is waiting for tcp/ip disconnect, which is never coming because the firewall eats this, resulting in backends waiting to death. Again: you'll have to request your sysadmin to fix the firewall, at least on that pgsql port for internal use. Timeouts simply don't make sense here. You won't have DOS attacks internally, I hope (if you do, locate the aggressor, and eliminate him). Regards, Andreas
Dave Page wrote: > > > > >>-----Original Message----- >>From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] >>Sent: 17 February 2004 17:42 >>To: Markus Wollny >>Cc: Josh Endries; pgadmin-support@postgresql.org >>Subject: Re: [pgadmin-support] connection dropping continued >> >> >> >>>I wouldn't call that malfunctioning, >>> >>> >>So call it ill-configured. >> >> > >I would call it malfunctioning, it's deliberately breaking tcp/ip by the >sounds of it. You say it's to prevent DOS attacks, but isn't that >exactly what it's caused itself? > > > >>>, a simple "select 1;" issued every 30 odd >>>seconds to all opened databases would be absolutely >>> >>> >>sufficient, I should >> >> >>>think. If that feature is off by default and can be switched on (and >>>maybe the interval adjusted according to needs), no one would be >>>bothered by it either. >>> >>> > >I experimented with this following the last round of borked firewalls >and it's virtually impossible to do without screwing up pgAdmin >completely - there are all sorts of threading/concurrency problems that >would take a massive rewrite to sort out - something I'm not prepared to >do to work around a problem caused by a firewall that doesn't implement >TCP/IP properly. > > Actually, it can be considered a BUG in the firewall, if it disconnects a tcpip session without sending the proper disconnect packet to the server, which assumes the connection being still valid and sitting on that open socket. Even web servers, routinely equipped with timeout functions themselves, would be delighted to be noticed about disconnects ASAP, to clean up services and save resources. Regards, Andreas
> -----Original Message----- > From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] > Sent: 17 February 2004 17:42 > To: Markus Wollny > Cc: Josh Endries; pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] connection dropping continued > > > > >I wouldn't call that malfunctioning, > > So call it ill-configured. I would call it malfunctioning, it's deliberately breaking tcp/ip by the sounds of it. You say it's to prevent DOS attacks, but isn't that exactly what it's caused itself? > >, a simple "select 1;" issued every 30 odd > >seconds to all opened databases would be absolutely > sufficient, I should > >think. If that feature is off by default and can be switched on (and > >maybe the interval adjusted according to needs), no one would be > >bothered by it either. > I experimented with this following the last round of borked firewalls and it's virtually impossible to do without screwing up pgAdmin completely - there are all sorts of threading/concurrency problems that would take a massive rewrite to sort out - something I'm not prepared to do to work around a problem caused by a firewall that doesn't implement TCP/IP properly. > > We can not and thus will not implement app level keep-alives. > You can try to head over to pgsql-bugs or pgsql-hackers, to recommend > implementing that in libpq, and you certainly will get the > same answer: > FIX THE FIREWALL! Agreed, I'm certain that's the response you will get (I've seen it before). Regards, Dave.
The server is waiting for tcp/ip disconnect, which is
never coming because the firewall eats this, resulting in backends
waiting to death. Again: you'll have to request your sysadmin to fix the
firewall, at least on that pgsql port for internal use. Timeouts
simply don't make sense here. You won't have DOS attacks internally, I
hope (if you do, locate the aggressor, and eliminate him).
The architecture just doesn't fit here - it's two LANs connected over a
VLAN, so the firewall is between us and the open internet, even though
the PG-server is in it's own LAN. I can not fix the firewall, it's not
in my jurisdiction and I cannot take it there. Changing
firewall-settings is simply not an option for me. I see that there's no
way that you would consider implementing a keep-alive feature. That's
fine, I shall have to live with the issue. Sorry to have asked in the
first place.
Hi! -----Ursprüngliche Nachricht----- Von: Andreas Pflug Gesendet: Di 17.02.2004 21:10 An: Dave Page Cc: Markus Wollny; JoshEndries; pgadmin-support@postgresql.org Betreff: Re: [pgadmin-support] connection dropping continued Actually, it can be considered a BUG in the firewall, if it disconnectsa tcpip session without sending the proper disconnect packet to theserver, which assumes the connection being still valid and sitting onthat open socket. Even web servers, routinely equipped with timeoutfunctions themselves, would be delighted to be noticed about disconnectsASAP, to clean up services and save resources. I don't know if it doesn't notify the server, I haven't noticed anything there. As far as I know the server might be notified all right and the connection closed - though surely the client doesn't receive any such notification, otherwise it wouldn't sit there and wait for a response from the server before finally throwing the timeout-errormessage. It's really just a minor nag, so if the suggested workaround does indeed involve digging arround in thread-concurrency issues, it's not worth it. Sorry to have brought up the subject again, but I didn't find the previous end of the same discussion and thus was not aware of the complexity. Regards Markus
On Feb 17, 2004, at 5:51 PM, Markus Wollny wrote: > > The architecture just doesn't fit here - it's two LANs connected over a > VLAN, so the firewall is between us and the open internet, even though > the PG-server is in it's own LAN. I can not fix the firewall, it's not > in my jurisdiction and I cannot take it there. Changing > firewall-settings is simply not an option for me. I see that there's no > way that you would consider implementing a keep-alive feature. That's > fine, I shall have to live with the issue. Sorry to have asked in the > first place. Have you thought about connecting to the pgAdmin server through a ssh tunnel? You could then use the KeepAlive feature of ssh to circumvent the problems with the firewall. ahp
I am not sure of your exact situation, but I am using a windows client with putty setting up an SSH tunnel to get through the firewall, one of the options on putty allows you to set a keep alive message every XX seconds. I have kept this connection up for days without problems... Jim
I think I found out what's causing the connection dropping - it's not really the firewall, it's the dynamic NAT routing. Our admin doesn't want to set up static NAT routing for the developers though if he can help it - he says that this should be reserved for servers. As an easy solution to the problem, I open up an SSH-terminal to the PostgreSQL-server and keep the top-process running (which I do mostly anyway); as long as there's just any traffic going on, the connection is kept alive - I think even pinging the server should be sufficient. Regards Markus -----Ursprüngliche Nachricht----- Von: James M Doherty Gesendet: Mi 18.02.2004 20:24 An: pgadmin-support@postgresql.org Cc:Betreff: Re: [pgadmin-support] connection dropping continued I am not sure of your exact situation, but I am using a windows client withputty setting up an SSH tunnel to get through thefirewall, one of the options on putty allows you to set a keep alive messageevery XX seconds. I have kept this connectionup for days without problems...Jim---------------------------(endof broadcast)---------------------------TIP 6: Have you searched our list archives? http://archives.postgresql.org
Markus Wollny wrote: >I think I found out what's causing the connection dropping - it's not >really the firewall, it's the dynamic NAT routing. Our admin doesn't >want to set up static NAT routing for the developers though if he can >help it - he says that this should be reserved for servers. > > Huh, that sounds dubious. Dynamic NAT for standard users to access the outer world, that's ok, but why NAT for access of internal resources? In a local network or VPN there's no need for NAT, because the private address space you're probably using is well known inside the organization. Seems to be just another example of weird stuff admins are inventing for some not-so-well understood reasons. Additionally, a NAT gateway may not reshuffle its ports/addresses for an existing connection, which seems to happen here. I'd call that a bug too (the firewall vendor will probably call it a feature, "look, we're scrambling the ports to obfuscate data origins..." - well done!) However, glad you found a workaround. Regards, Andreas