X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id EECB79DCA85 for ; Tue, 20 Dec 2005 12:24:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 11555-01 for ; Tue, 20 Dec 2005 12:24:48 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id BB7949DCA83 for ; Tue, 20 Dec 2005 12:24:47 -0400 (AST) Received: from mail.dsvr.co.uk (mail.dsvr.co.uk [212.69.192.8]) by svr4.postgresql.org (Postfix) with ESMTP id 2B41F5AF84D for ; Tue, 20 Dec 2005 16:24:47 +0000 (GMT) Received: from [62.69.64.93] (helo=[10.100.3.130]) by mail.dsvr.co.uk with esmtp (Exim 4.30) id 1EokI8-0000qi-CU; Tue, 20 Dec 2005 16:24:44 +0000 Subject: PostgreSQL 7.4.10 hanging on delete From: Jonathan Parkin To: PostgreSQL Admin Mailing List Content-Type: text/plain Organization: Legend Communications plc Message-Id: <1135095712.14411.1260.camel@jparkin.local> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 20 Dec 2005 16:21:53 +0000 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/224 X-Sequence-Number: 19944 I have a reasonably large, live, system-critical database. A perl script on another machine connects and issues a sequence of commands in a transaction, the last of which is a delete. The delete never returns a response, and the connection never times out. The postgres process handling the delete is in a scheduled state, but stracing it produces no output at all. The table contains entries for a "parent" and it's "children". Grandchildren and other descendents never exist in the table. A before delete trigger removes the children when the parent is deleted and the delete is always issued for a parent. Children have exactly one parent. By tcpdumping the SQL connection the system consistently hangs on the same delete for one of a set of rows. As the delete never commits the transaction is never finished. Other rows (parents and children) can be deleted without issue. Killing the perl process at the other end, and subsequently the connection timing out on the perl-server end does not stop the postgres process handling the delete, and it continues to produce no output when straced. Restarting the server, doing a VACUUM FULL ANALYZE and a REINDEX of all tables in the database (including system tables) has no effect. This was first noticed using PostgreSQL 7.4.7. Upgrading to 7.4.10 has not helped. Upgrading to 8.x may not be an option due to the systems connecting to the database (this is being investigated). My next logical step is to stop access to the database, take a dump of it, remove it, and rebuild it. Can anyone think of a reason why I should not do this? Can anyone think of anything else I should try? I welcome all suggestions, no matter how obvious they may appear. Thanks, Jonathan -- Best Regards Jonathan Parkin Developer Legend Communications plc T: 0844 390 2049 F: 0844 390 2001 E: jonathan.parkin@legendplc.com W: http://www.legend.co.uk/ The information in this message is confidential and may be legally privileged. Unauthorised disclosure, copying or distribution, either whole or in part; or action taken in reliance on its content is prohibited. If you are not the intended recipient, please notify Legend Communications immediately.