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 513549DC820 for ; Wed, 21 Dec 2005 06:56:30 -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 01540-01 for ; Wed, 21 Dec 2005 06:56:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ADONIS.compesa.com.br (unknown [200.167.199.194]) by postgresql.org (Postfix) with ESMTP id 1AC559DC80C for ; Wed, 21 Dec 2005 06:56:26 -0400 (AST) Received: from [192.168.1.202] (unknown [192.168.1.202]) by ADONIS.compesa.com.br (Postfix) with ESMTP id 7190F172AA8; Wed, 21 Dec 2005 06:57:43 -0500 (EST) Received: from 127.0.0.1 (AVG SMTP 7.0.300 [265.6.5]); Wed, 21 Dec 2005 07:54:44 -0200 Message-ID: <00ce01c60614$91f7b340$ca01a8c0@DBA> From: "Andrea" To: "Jonathan Parkin" , References: <1135095712.14411.1260.camel@jparkin.local> Subject: Re: PostgreSQL 7.4.10 hanging on delete Date: Wed, 21 Dec 2005 07:54:44 -0200 X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Mime-Version: 1.0 Content-Transfer-Encoding: 8bit Content-Type: text/plain; charset=iso-8859-1 X-Compesa-MailScanner-OpenProtect-Information: Please contact the ISP for more information X-Compesa-MailScanner-OpenProtect: Found to be clean X-MailScanner-OpenProtect-MCPCheck: X-MailScanner-OpenProtect-From: andrea@compesa.com.br X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/241 X-Sequence-Number: 19961 Hi, I dont´t know if i understand exactly your problem, but ... When i tried to delete all of the rows of a table , it seems to never end. So i created an index to each foreing key and the command works very fast. Good Luck! Andréa Pereira Brasil-Recife ----- Original Message ----- From: "Jonathan Parkin" To: "PostgreSQL Admin Mailing List" Sent: Tuesday, December 20, 2005 2:21 PM Subject: [ADMIN] PostgreSQL 7.4.10 hanging on delete > 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. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > -- > Esta mensagem foi verificada pelo sistema de antivírus e > acredita-se estar livre de perigo. > > > > -- > Internal Virus Database is out-of-date. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.6.5 - Release Date: 26/12/2004 > -- Internal Virus Database is out-of-date. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.6.5 - Release Date: 26/12/2004 -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo.