Re: AW: [HACKERS] inserts/updates problem under stressing !
От | Oleg Bartunov |
---|---|
Тема | Re: AW: [HACKERS] inserts/updates problem under stressing ! |
Дата | |
Msg-id | Pine.GSO.3.96.SK.990726125156.15509A-100000@ra обсуждение исходный текст |
Ответы |
Re: AW: [HACKERS] inserts/updates problem under stressing !
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: