Re: preventing deadlocks - Mailing list pgsql-admin

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.
Thanks.
Evgeny.

On Wed, 28 Dec 2005, Bruno Wolff III wrote:

> 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 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
>