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 502B99DCA25 for ; Wed, 28 Dec 2005 16:20:19 -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 32895-07 for ; Wed, 28 Dec 2005 16:20:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id E6E239DCA27 for ; Wed, 28 Dec 2005 16:20:16 -0400 (AST) Received: (qmail 6245 invoked by uid 500); 28 Dec 2005 20:26:07 -0000 Date: Wed, 28 Dec 2005 14:26:07 -0600 From: Bruno Wolff III To: Tsirkin Evgeny Cc: pgsql-admin@postgresql.org Subject: Re: preventing deadlocks Message-ID: <20051228202607.GA5194@wolff.to> Mail-Followup-To: Bruno Wolff III , Tsirkin Evgeny , pgsql-admin@postgresql.org References: <43B10E07.7010503@mail.jct.ac.il> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43B10E07.7010503@mail.jct.ac.il> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.029 required=5 tests=[AWL=0.029] X-Spam-Score: 0.029 X-Spam-Level: X-Archive-Number: 200512/353 X-Sequence-Number: 20073 On Tue, Dec 27, 2005 at 11:48:55 +0200, Tsirkin Evgeny wrote: > > Hi list! > My issue is as follows : > I have to do some calculations 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 (a or b) > ident | numeric(10,0) | > ----the > count | integer > | ----the count > > > The problem of course is the locking issues while changing a and b > tables.What i am doing now is to > select 1 from counter where counter_type='a' and ident in (1,2,3,4) for > update; > select 1 from counter where 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 a where pkey in (5,6,7) for update; > > My problems: > [1] Is the for update lock anouth here? > [2] The "for update" queries HAVE 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