The following bug has been logged online: Bug reference: 5465 Logged by: Valentine Gogichashvili Email address: val...@gmail.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. It was exactly on the time, that the procedure was accessing remote database, the machine hosting this remote database had a panic attack and rebooted. But the ESTABLISHED connection is still hanging on the production database machine: $ netstat | grep remote_db_host tcp 0 0 production_db_host:60248 remote_db_host:postgres ESTABLISHED $ lsof | grep remote_db_host postgres 1365 postgres 199u IPv4 23003779784 TCP production_db_host:60248->remote_db_host:postgres (ESTABLISHED) On the database session list one can see the hanging transaction: production_db=# select procpid, now() - query_start as running, waiting, substr(current_query,1,120) as current_query from pg_stat_activity where current_query not like '%----STATQ-----%' and current_query != '<IDLE>' order by query_start desc; procpid | running | waiting | current_query ---------+------------------------+---------+------------------------------- ---------------------------------------------------------------------------- ------------------------------------ 1365 | 2 days 00:17:57.992004 | f | SELECT * FROM get_remote_data() 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? 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? With best regards, -- Valentine Gogichashvili -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs