Re: preventing deadlocks - Mailing list pgsql-admin

On Tue, Dec 27, 2005 at 11:48:55 +0200,
  Tsirkin Evgeny <tsurkin@mail.jct.ac.il> 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