Re: preventing deadlocks - Mailing list pgsql-admin

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