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 D206A9DC9C2 for ; Tue, 27 Dec 2005 05:40:37 -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 11235-09 for ; Tue, 27 Dec 2005 05:40:39 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id C64E39DC998 for ; Tue, 27 Dec 2005 05:40:35 -0400 (AST) Received: from mail3.cc.huji.ac.il (real-outmail.cc.huji.ac.il [132.64.1.21]) by svr4.postgresql.org (Postfix) with ESMTP id D512D5AF907 for ; Tue, 27 Dec 2005 09:40:37 +0000 (GMT) Received: from mail3.cc.huji.ac.il (localhost [127.0.0.1]) by mail3.cc.huji.ac.il (Postfix) with SMTP id 9902E14580C for ; Tue, 27 Dec 2005 11:39:02 +0200 (IST) Received: by mail3.cc.huji.ac.il (Postfix, from userid 31998) id 8C4AC145829; Tue, 27 Dec 2005 11:39:02 +0200 (IST) Received: from [132.64.52.127] (evegeni.mm.huji.ac.il [132.64.52.127]) by mail3.cc.huji.ac.il (Postfix) with ESMTP id 4046814580C for ; Tue, 27 Dec 2005 11:39:02 +0200 (IST) Message-ID: <43B10E07.7010503@mail.jct.ac.il> Date: Tue, 27 Dec 2005 11:48:55 +0200 From: Tsirkin Evgeny User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: preventing deadlocks Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus: scanned by VAMS 2005 version 8.1009 from Mon Dec 26 10:53:07 2005 CET X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/318 X-Sequence-Number: 20038 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. 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