Re: Troublesome handling of dropped connection

Francisco Reyes <lists@natserv.com> writes:
> I had connected to a Postgresql database, running 7.1.3 on FreeBSD,
> machine through ssh/psql. My machine crashed.

> Upon restart of my machine I recconected to the database machine, again
> ssh to the machine and then run psql.

Did you look to see whether your old session had disconnected or not?

> The table I was loading data to at the time of the disconnection was
> unresponsive.

It sounds like your new session was waiting around for the old session
to complete a transaction and release locks.

Unfortunately, if your old session was not in the midst of
sending/receiving data at the time of the crash, it may take quite a
while (an hour or two) before the TCP code notices that the connection's
been lost.  The backend will just sit there patiently until the
connection times out.  There is not a lot we can do about this; the
long timeout is mandated by the TCP specs, and the code involved is
kernel code not Postgres code anyway.

You could have zapped the backend more cleanly by sending it a SIGINT.

            regards, tom lane