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 D4FDB9DC80A for ; Tue, 3 Jan 2006 20:30:03 -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 57932-05 for ; Tue, 3 Jan 2006 20:30:06 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from flake.decibel.org (unknown [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id E5A1C9DC824 for ; Tue, 3 Jan 2006 20:30:00 -0400 (AST) Received: by flake.decibel.org (Postfix, from userid 1001) id 05B983983D; Tue, 3 Jan 2006 18:30:02 -0600 (CST) Date: Tue, 3 Jan 2006 18:30:01 -0600 From: "Jim C. Nasby" To: Tsirkin Evgeny Cc: Bruno Wolff III , Tsirkin Evgeny , pgsql-admin@postgresql.org Subject: Re: preventing deadlocks Message-ID: <20060104003001.GY82560@pervasive.com> References: <43B10E07.7010503@mail.jct.ac.il> <20051228202607.GA5194@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.069 required=5 tests=[AWL=0.069] X-Spam-Score: 0.069 X-Spam-Level: X-Archive-Number: 200601/30 X-Sequence-Number: 20118 On Mon, Jan 02, 2006 at 11:36:11AM +0200, Tsirkin Evgeny wrote: > Thanks for answer.However i have already searched for a way to make count > faster and didn't find anything. > Any pointers will be appreciated. What you want to do in the trigger is insert 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). 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. > On Wed, 28 Dec 2005, Bruno Wolff III wrote: > > > On Tue, Dec 27, 2005 at 11:48:55 +0200, > > Tsirkin Evgeny wrote: > > > > > > Hi list! > > > My issue is as follows : > > > I have to do somecalculations based on *exact* number of rows in 2 > > > tables (with a filter) meaning: > > > SELECT count(*) FROM a WHERE a.row1 in (1,2,3,4); > > > SELECT count(*) FROM b WHERE b.row1 in (1,2,3,4); > > > However i couldn't use the count(*) since it is too slow beacause of the > > > table size. > > > > You should look through the past archives on this subject. There is a way to > > do this that uses MVCC for incremental changes. You do want to make a sweep > > through the delta table periodically which will need stronger locking, but > > this won't block reads on a and b. > > > > > So,i created a trigger that on insert increments and on delete > > > decriments special "counter" table > > > that contains > > > \d counter > > > Column | Type | Modifiers > > > --------------+-----------------------+----------- > > > counter_type | character varying(30) | ---- the table name > > > ident | numeric(10,0) | > > > count | integer > > > > > > > > > The problem of course is thelocking issues while changing a and b > > > tables.What i am doing now is to > > > select 1 from counterwhere counter_type='a' and ident in (1,2,3,4) > > > for update; > > > select 1 from counterwhere counter_type='b' and ident in (5,6,7) for > > > update; > > > Befor changing anything in tables "a" and "b"in transaction .I am also > > > doing for update select on the > > > "a" and "b" tables itself ,that is: > > > select b from awhere pkey in (5,6,7) for update; > > > > > > My problems: > > > [1] Is the for update lock anouthhere? > > > [2] The "for update" queriesHAVE to be done in the same order in all > > > application which is pretty error prone - > > > it is very easy to forget in one place and get a deadlock. > > > [3] Can i make a trigger that automatically locks the counter_type='b' > > > if a for update select was done on table b? > > > something like (pseudo): > > > trigger on select for update table b{ > > > select for update where ident = OLD.pkey ; > > > } > > > [4] Can i combine queries for multiple tables to make locking atomic: > > > select 1 from b,counter where b.pkey in (1,2,3,4) and counter.ident in > > > (1,2,3,4); > > > Hope for help and sorry for long message. > > > evgeny > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 4: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: don't forget to increase your free space map settings > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461