Thread: Re: AW: [HACKERS] inserts/updates problem under stressing !
Andreas, I rewrote my function but got a problem how to know if update fails: CREATE FUNCTION "acc_hits" (int4) RETURNS datetime AS ' Declare keyval Alias For $1; cnt int4; curtime datetime; Begin curtime := ''now''; Update hits set count = count + 1,last_access = curtime where msg_id = keyval; if Not Foundthen ?????????? -- first_access inserted on default, last_access is NULL Insert Into hits (msg_id,count)values (keyval, 1); End If; return curtime; End; ' LANGUAGE 'plpgsql'; regards, Oleg On Mon, 26 Jul 1999, Zeugswetter Andreas IZ5 wrote: > Date: Mon, 26 Jul 1999 10:31:33 +0200 > From: Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at> > To: 'Oleg Bartunov' <oleg@sai.msu.su> > Subject: AW: [HACKERS] inserts/updates problem under stressing ! > > > > CREATE FUNCTION "acc_hits" (int4) RETURNS int4 AS ' > > Declare > > keyval Alias For $1; > > cnt int4; > > curtime datetime; > > Begin > > curtime := ''now''; > > Select count into cnt from hits where msg_id = keyval; > > if Not Found then > > cnt := 1; > > -- first_access inserted on default, last_access is NULL > > Insert Into hits (msg_id,count) values (keyval, cnt); > > else > > cnt := cnt + 1; > > Update hits set count = cnt,last_access = curtime where msg_id = > > keyval; > > End If; > > return cnt; > > End; > > ' LANGUAGE 'plpgsql'; > > > > > Ok, this proc is not concurrent capable. This is because in the time between > the select and the update some other connection can update count. > > 1. Change the update to: > > Update hits set count = count+1, last_access = curtime where msg_id = > > keyval; > > > 2. the insert is also not concurrent capable, since there could be two > simultaneous > first accesses. > > It looks like there will be more updates than inserts, so I would change the > above to > 1. try update > 2. if num rows affected = 0 do the insert > > I don't know how to get the rows affected, but this should be possible. > > Andreas > > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
At 12:54 26/07/99 +0400, Oleg Bartunov wrote:
>Andreas,
>
>I rewrote my function but got a problem how to know if update fails:
>
>CREATE FUNCTION "acc_hits" (int4) RETURNS datetime AS '
>Declare
> keyval Alias For $1;
> cnt int4;
> curtime datetime;
>Begin
> curtime := ''now'';
> Update hits set count = count + 1,last_access = curtime where msg_id =
keyval;
> if Not Found then
> ??????????
You need a patch to plpgsql with adds:
GET DIAGNOSTICS SELECT PROCESSED INTO num_of_rows_affected;
where num_of_rows_affected is a local variable.
The patch is currently with Jan, who is quite busy.
> -- first_access inserted on default, last_access is NULL
> Insert Into hits (msg_id,count) values (keyval, 1);
> End If;
> return curtime;
>End;
>' LANGUAGE 'plpgsql';
>
>
>
> regards,
>
> Oleg
>
>
>
>
>
>
>On Mon, 26 Jul 1999, Zeugswetter Andreas IZ5 wrote:
>
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
On Mon, 26 Jul 1999, Philip Warner wrote: > Date: Mon, 26 Jul 1999 19:14:02 +1000 > From: Philip Warner <pjw@rhyme.com.au> > To: Oleg Bartunov <oleg@sai.msu.su>, > Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at> > Cc: pgsql-hackers@postgreSQL.org > Subject: Re: AW: [HACKERS] inserts/updates problem under stressing ! > > At 12:54 26/07/99 +0400, Oleg Bartunov wrote: > >Andreas, > > > >I rewrote my function but got a problem how to know if update fails: > > > >CREATE FUNCTION "acc_hits" (int4) RETURNS datetime AS ' > >Declare > > keyval Alias For $1; > > cnt int4; > > curtime datetime; > >Begin > > curtime := ''now''; > > Update hits set count = count + 1,last_access = curtime where msg_id = > keyval; > > if Not Found then > > ?????????? > > You need a patch to plpgsql with adds: > > GET DIAGNOSTICS SELECT PROCESSED INTO num_of_rows_affected; > > where num_of_rows_affected is a local variable. > > The patch is currently with Jan, who is quite busy. > Jan, did you approve the patch. Is it usable with 6.5.1 ? Oleg > > > -- first_access inserted on default, last_access is NULL > > Insert Into hits (msg_id,count) values (keyval, 1); > > End If; > > return curtime; > >End; > >' LANGUAGE 'plpgsql'; > > > > > > > > regards, > > > > Oleg > > > > > > > > > > > > > >On Mon, 26 Jul 1999, Zeugswetter Andreas IZ5 wrote: > > > > ---------------------------------------------------------------- > Philip Warner | __---_____ > Albatross Consulting Pty. Ltd. |----/ - \ > (A.C.N. 008 659 498) | /(@) ______---_ > Tel: +61-03-5367 7422 | _________ \ > Fax: +61-03-5367 7430 | ___________ | > Http://www.rhyme.com.au | / \| > | --________-- > PGP key available upon request, | / > and from pgp5.ai.mit.edu:11371 |/ > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83