Re: BUG #5465: dblink TCP connection hangs blocking translation from being terminated

On Wed, May 19, 2010 at 5:10 AM, Valentine Gogichashvili
<valgog(at)gmail(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5465
> Logged by:          Valentine Gogichashvili
> Email address:      valgog(at)gmail(dot)com
> PostgreSQL version: 8.2.1
> Operating system:   Red Hat 3.4.6-3 (kernel 2.6.9-42.0.3.ELsmp)
> Description:        dblink TCP connection hangs blocking translation from
> being terminated
> Details:
>
> Hi all,
>
> we have an issue on our productive server. A stored procedure, that uses
> dblink to get some data from the remote database hangs not responding to
> kill signal and holds several locks on some tables as well as an advisory
> lock. So I have this transaction to be completed in order to have a
> possibility to operate the database normally.

I believe this is a known issue in dblink, where it's not possible to
cancel it when it's waiting in the TCP layer in the kernel.
Unfortunately, there is no fix ATM - there was some work towards it
for 9.0 at one point, but I think this is actually the first real
bug-report on the issue...

> It seems like the dblink is waiting for the connection to be closed or
> reseted and also makes the hole transaction hang not processing kill
> signals.
>
> Does the dblink TCP connection have any timeout?

It does not. But it would detect a conneciton that goes away, so TCP
keepalives should be able to deal with this problem. Once the kernel
notices the other end is gone, dblink should notice it and roll back.

> How would it be possible to shutdown the DB in case this session process is
> not responding to normal kill signals? Will it hinder the database from
> shutting down normally? My previous experience with issuing immediate stops
> or killing with -9 had been quite catastrophic and I could not start the DB
> afterwards. What would you suggest in this case?

kill -9 on a client will make the postmaster restart the whole
process, so yes, it's a very heavy operation.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/