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 B0EF19DCA61 for ; Wed, 21 Dec 2005 04:02:43 -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 19433-06 for ; Wed, 21 Dec 2005 04:02:43 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.197]) by postgresql.org (Postfix) with ESMTP id A1B049DC9D3 for ; Wed, 21 Dec 2005 04:02:39 -0400 (AST) Received: by wproxy.gmail.com with SMTP id 69so77807wra for ; Wed, 21 Dec 2005 00:02:41 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=hMCVzkHRWudSJWELhQ4u92nc6tHbdPqaJgDFRYDWUKL7q3K41+0UK8PZUW0lXjTPfSsdhGjPc4LPDMlsz7XpjXimxfkM244zinSg64rLpSs8yECWkxLURLh32c59iQOOEOdjLWC0gu6N70kghIPzxtBNuU/30DnVdG7d3TlTyHo= Received: by 10.65.153.4 with SMTP id f4mr291807qbo; Wed, 21 Dec 2005 00:02:41 -0800 (PST) Received: by 10.64.210.15 with HTTP; Wed, 21 Dec 2005 00:02:41 -0800 (PST) Message-ID: <5e744e3d0512210002o7bb0f782oaacdb9a64160e0fe@mail.gmail.com> Date: Wed, 21 Dec 2005 13:32:41 +0530 From: Pandurangan R S To: Jonathan Parkin Subject: Re: PostgreSQL 7.4.10 hanging on delete Cc: PostgreSQL Admin Mailing List In-Reply-To: <1135095712.14411.1260.camel@jparkin.local> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1135095712.14411.1260.camel@jparkin.local> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.023 required=5 tests=[AWL=0.023] X-Spam-Score: 0.023 X-Spam-Level: X-Archive-Number: 200512/235 X-Sequence-Number: 19955 When the delete statements hangs, look into the pg_locks table, to check if the delete statement is waiting because it has conflict with rows locked by other transactions. On 20 Dec 2005 16:21:53 +0000, Jonathan Parkin wrote: > 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 > -- Regards Pandu