X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 95C3053A91 for ; Thu, 7 Apr 2005 00:02:12 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36856-01 for ; Wed, 6 Apr 2005 23:02:07 +0000 (GMT) Received: from mta08-winn.mailhost.ntl.com (smtpout16.mailhost.ntl.com [212.250.162.16]) by svr1.postgresql.org (Postfix) with ESMTP id 966F153A39 for ; Thu, 7 Apr 2005 00:02:05 +0100 (BST) Received: from aamta01-winn.mailhost.ntl.com ([212.250.162.8]) by mta08-winn.mailhost.ntl.com with ESMTP id <20050406230207.TSMF928.mta08-winn.mailhost.ntl.com@aamta01-winn.mailhost.ntl.com> for ; Thu, 7 Apr 2005 00:02:07 +0100 Received: from localhost.localdomain ([81.100.134.89]) by aamta01-winn.mailhost.ntl.com with ESMTP id <20050406230207.EETL1187.aamta01-winn.mailhost.ntl.com@localhost.localdomain> for ; Thu, 7 Apr 2005 00:02:07 +0100 Received: from [192.168.0.50] ([192.168.0.50]) (authenticated bits=0) by localhost.localdomain (8.13.1/8.13.1) with ESMTP id j36N1GvZ029746 (version=TLSv1/SSLv3 cipher=RC4-SHA bits=128 verify=NO) for ; Thu, 7 Apr 2005 00:01:17 +0100 Mime-Version: 1.0 (Apple Message framework v619.2) Content-Transfer-Encoding: 7bit Message-Id: <9daeb170ec2df50ae120e6403845e285@dsvr.net> Content-Type: text/plain; charset=US-ASCII; format=flowed To: pgsql-admin@postgresql.org From: Robert Fielding Subject: Hang in semop() in 'waiting' backends Date: Thu, 7 Apr 2005 00:02:02 +0100 X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/93 X-Sequence-Number: 17106 Hi, I'm afraid information is a little sketchy with this one, but I'll present to you quickly what's going on as best as I can. Your assistance is appreciated: platform: Linux 2.4.20 #19 SMP RedHat 9 base, PG 7.4.5 (also 7.4.2) problem: postgres backends waiting forever strace shows: semop(19529737, 0xbfffe020, 1 SELECT domain_add ('robf', 'testdomain123456.uk', '212.69.194.46' ) ;\ domain_add is a very complex function which calls lots of the SELECT functions and performs string functions on the input. I've tried reindexing both domains_pkey and domains_vs_idx indexes attached to the domains table with no effect. I've also restarted the service several times. This only results as a temporary fix in clearing the waiting processes, but not adding domains. Instead I have suspended service, dumped and recreated the DNS database. I have also upgraded to PG 7.4.5 (I know not the latest, but this machine doesn't have external access). The dump and restore resolved the issue temporarily. ipcs shows all shared memory use clears when the service is stopped, and resumes when restarted. Individual calls to domain_add() work; multiple calls within a transaction block on the first call. The individual calls from a psql shell also fail after this. The only fix is to kill the backends which are waiting. This is a production system which has suddenly gone wrong. Last schema update was posted to CVS 2005/03/21 which included UPDATE and DELETE constraints and foreign key updates; It seems strange updates from 23rd of last month would start causing problems now. I will need to talk to the developer responsible if any updates haven't been posted to CVS - however this is not our practice. Given the hang, could it still be described as a bug anyway? Best regards, Rob Fielding Designer Servers Business Serve plc