[Zope] Problem with Z Psycopg

[Barry Pederson]

Jeff Gentry wrote:
> On Tue, 5 Apr 2005, Dieter Maurer wrote:
>>Usually, this means that the postgres server process died...
> 
> I did run into something recently where the local firewall was killing the
> connection to the DB server due to a timeout, so one of the local admin
> types wrote a small zsql method that gets hit by a cronjob about once an
> hour or so.
> 
> Granted the end effect is the same (zope thinks that the DB server has
> died) but in this case it was the connection itself.

Yeah, I think Jeff nailed it.  I had FreeBSD's IPFilter set to drop idle states
after an hour.  Turns out FBSD by default does TCP keepalive, but only after 2
idle hours.  I changed the net.inet.tcp.keepidle sysctl to lower it to a
half-hour, so it now gooses the firewall often enough that I haven't seen a
dropped connection since then.

Looking back at the pgsql logs, I think I've always had this problem, but just
didn't realize it until I tried a new ZPsycopgDA that lacked the auto-reconnect
feature.

Even though tweaking TCP fixed the problem, I think it'd still be good if
ZPsycopgDA was able to auto-reconnect, because you could lose connections for
other reasons - such as restarting postgres - and it'd be nice to not have to go
and restart Zope too.

(I think it's kind of funny that I didn't find out about my TCP problem until
ZPsycopgDA failed to auto-reconnect, and I wouldn't have found out so soon about
ZPsycopgDA not auto-reconnecting if I didn't have the TCP problem :)

	Barry