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 5DB6E9DC9AE for ; Thu, 5 Jan 2006 04:25:52 -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 23538-06 for ; Thu, 5 Jan 2006 04:25:52 -0400 (AST) X-Greylist: delayed 16:44:07.234799 by SQLgrey- Received: from mail4.cc.huji.ac.il (mail4.cc.huji.ac.il [132.64.1.23]) by postgresql.org (Postfix) with ESMTP id AA6C59DC99D for ; Thu, 5 Jan 2006 04:25:49 -0400 (AST) Received: from mail4.cc.huji.ac.il (localhost [127.0.0.1]) by mail4.cc.huji.ac.il (Postfix) with SMTP id 48A1B3945; Thu, 5 Jan 2006 10:25:49 +0200 (IST) Received: by mail4.cc.huji.ac.il (Postfix, from userid 31998) id 329803945; Thu, 5 Jan 2006 10:25:49 +0200 (IST) Received: from [132.64.52.127] (evegeni.mm.huji.ac.il [132.64.52.127]) by mail4.cc.huji.ac.il (Postfix) with ESMTP id 823B6391D; Thu, 5 Jan 2006 10:25:48 +0200 (IST) Message-ID: <43BCDA17.6070806@mail.jct.ac.il> Date: Thu, 05 Jan 2006 10:34:31 +0200 From: Tsirkin Evgeny User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bruno Wolff III CC: pgsql-admin@postgresql.org Subject: Re: preventing deadlocks References: <43B10E07.7010503@mail.jct.ac.il> <20051228202607.GA5194@wolff.to> <20060104003001.GY82560@pervasive.com> <20060105065747.GA17322@wolff.to> In-Reply-To: <20060105065747.GA17322@wolff.to> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus: scanned by VAMS 2005 version 8.1020 from Wed Jan 4 15:56:09 2006 CET X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.786 required=5 tests=[AWL=-0.546, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 0.786 X-Spam-Level: X-Archive-Number: 200601/64 X-Sequence-Number: 20152 Bruno Wolff III wrote: >On Wed, Jan 04, 2006 at 10:51:55 +0200, > Tsirkin Evgeny wrote: > > >>>What you want to do in the trigger isinsert a new row into a table that >>>contains the change in count, instead of trying to update a single row >>>for each value/ident (btw, you'll probably get better performance if you >>>make ident an int instead of a numeric). >>> >>> >>Why? >> >> > >Because this doesn't block other processes and still gives you correct >results. > > > I understand THAT ,I meant why int will give me more performance. >>>So now you'll have a list of >>>changes to the value, which you will periodically want to roll up into a >>>table that just stores the count. >>> >>> >>Interesting idea.Thanks.However it pretty complicates things ,maybe there >>will be simpler solution.Something i did not thought about at all - i think that >> >> > >Not that avoids blocking. If you don't have a lot of concurrent queries then >this may not be an issue for you. > > > Well i DO have a lot of concurrent queries,that is the whole point. >>counting is something that everybody does. >> >> > >No it isn't. Smetimes they aren't needed at all, sometimes approximate values >are good enough, and even when they are needed, it is often the case that >it is better for count queries to run slower so that other queries run faster. > > > It was just a hope of mine. >>[1] I have also a hope that i can create a trigger that locks counter >>table once a 'select for update' was done on one of the tables i count. >>However how can i say if a select that fires a trigger is a 'for update' >>one? >> >> > >If you have a counter table and do an UPDATE that will lock that row. >If you have multiple tables that you keep counts for you will want to lock >the whole counter table using a LOCK command or else you can get deadlocks. >Note this means that inserts and deletes from any of the tracked tables >will block inserts and deletes of those tables in other concurrent queries. > > > That does not help me .What i want is : select 1 from table a where whatever for update; issuing this should fire a trigger that does: select 1 from table counter where a.pkey=counter.ident or something however this should only be done for select FOR UPDATE! And i can't know that inside the trigger! >>[2] Maybe there is a MVCC or something solution like Bruno suggested (that >>i did not realy understood thought). >> >> > >The explanation given at the top is the gist of the solution that uses MVCC >advantagesously. > > OK.