Re: [HACKERS] Stored procedure issue

Hello

On 03/12/2007, Dragan Zubac <moroncic(at)yahoo(dot)com> wrote:
> Hello
>
> What I have noticed is that when I don't use procedure
> at all,there's only 2-5 locks in pg_locks,after I
> start application which uses stored procedure the
> number in pg_locks increase rapidly to steady 75 even
> to 130 at certain moments.
>
> Any clue why procedure usage might increase locks so
> heavily ?
>
> Sincerely
>

upgrade to 8.2? There is shared lock and there are less problems with
locks. But I am not sure if this solves your problem. General
protection before locks is all things with maximal speed. Are your
queris well optimazed?

Regards
Pavel Stehule

> --- Dragan Zubac <moroncic(at)yahoo(dot)com> wrote:
>
> > Hello
> >
> > I have a stored procedure which does the billing
> > stuff
> > in our system,it works ok,but if I put in
> > production,where there is some 5-10 billing events
> > per
> > second,the whole database slows down. It won't even
> > drop some test table,reindex,vacuum,things which
> > were
> > done before in the blink of an eye. If I stop the
> > application which calls the procedure,all is back to
> > normal.
> >
> > We didn't implement any special locking mechanism in
> > the procedure,all is default. The procedure is
> > updating user's balance in table 'users'. On the
> > other
> > hand a couple of 'heavy load' table has foreign keys
> > pointing to table 'users'.
> >
> > Is it the matter of concurency and some locking
> > issue
> > or maybe the existing of all those foreign keys
> > pointing to table 'users',or maybe something else
> > which we're not aware at the moment ?
> >
> > Sincerely
> >
> > Pera
> >
> >
> >
> >
> ____________________________________________________________________________________
> > Be a better sports nut! Let your teams follow you
> > with Yahoo Mobile. Try it now.
> >
> http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project
> > by donating at
> >
> >
> > http://www.postgresql.org/about/donate
> >
>
>
>
> ____________________________________________________________________________________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>
>
> ---------------------------(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
>