Re: preventing deadlocks

I'm not really sure What Bruno answered that you didn't ask, but I'll
mention that anything you try here other than doing inserts and deletes
from your trigger is flat out going to perform poorly due to locking.
Plus, as you discovered, deadlocking will be a major issue, unless you
establish a table-level lock on your count table at the begining of the
transaction.

Personally, I'd just put the effort into setting up the insert/delete
stuff and a means to periodically roll that info up into a summary
table. I know that this has come up in the past, so you can probably
find someone else who's already done this and might be able to provide
you with code. Even if you have to write it from scratch, I doubt it's
more work than what you've already put into trying to get the other
scheme to work.

BTW, there is a desire to be able to store some kind of visibility info
in a manner which could be used by indexes; that might allow your select
count(*)'s to perform adequately without maintaining a seperate summary
table.

On Thu, Jan 05, 2006 at 10:53:40AM +0200, Tsirkin Evgeny wrote:
>
> Bruno,thanks for the answer but this was not at ALL that i asked.
> Neither mind .
> Note however, how polite i am .
> Thanks anyway.
> Evgeny
>
> Bruno Wolff III wrote:
>
> >On Thu, Jan 05, 2006 at 10:34:31 +0200,
> > Tsirkin Evgeny <tsurkin(at)mail(dot)jct(dot)ac(dot)il> wrote:
> >
> >
> >>I understand THAT ,I meant why int will give me more performance.
> >>
> >>
> >
> >Because if you have a bunch of processes sitting around waiting for table
> >locks, the average time to process queries will be higher. If you are
> >already limited by IO throughput or CPU usage this may not be a big deal,
> >but in many cases it will be.
> >
> >
> >
> >>Well i DO have a lot of concurrent queries,that is the whole point.
> >>
> >>
> >
> >Then you probably want to do what people have been recommending you do.
> >
> >
> >
> >>That does not help me .What i want is :
> >>select 1 from table a where whatever for update;
> >>issuing this should fire a trigger that does:
> >>
> >>
> >
> >You can't trigger on select statements. So this approach won't workin any
> >case.
> >
> >
> >
> >>select 1 from table counter where a.pkey=counter.ident or something
> >>however this should only be done for select FOR UPDATE!
> >>And i can't know that inside the trigger!
> >>
> >>
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461