Re: [SQL] inserts/updates problem under stressing ! - Mailing list pgsql-sql

Tom,

I just posted my latest results and it seems I have no
problem at all at home - numbers from access_log and and database
are consistent. They are diffrent from what Apache Benchmarks reports
but I'm fine ( I think ab reports something different :-)
I see the problem at work - Linux SMP. As I posted running test cause
duplicated records in database ! Could be SMP somehow affects to
postgres under stressing ? I'm developing rather big informational 
Web channel with all content generated from postgres database and
worry about reliability. Performance is ok. but simple logging to db
getting me totally lost ! 

Does somebody has an experience with SMP+postgres under high stressing. 
Probably we need some pages on Postgres Web server with 
recommendations and experience from real life. Especially after
introducing of MVCC ! I've seen in mailing lists several threads
about administrations of postgres in 27*7*365 systems but never got
a final opinion what's the best and safe. Probably this is  my
problem :-) But it might be more usefull if some expert could summarize
discusion and submit summary to www.postgresql.org
Regards,    Oleg

On Sat, 24 Jul 1999, Tom Lane wrote:

> Date: Sat, 24 Jul 1999 12:29:06 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] inserts/updates problem under stressing ! 
> 
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > I did some benchmarks of my Web site and notice I lost some hits
> > which I accumulate in postgres (6.5.1) database on Linux 2.0.36 system
> 
> > 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';
> 
> I wonder whether this doesn't have a problem with concurrent access:
> 
> 1. Transaction A does 'Select count into cnt', gets (say) 200.
> 2. Transaction B does 'Select count into cnt', gets 200.
> 3. Transaction A writes 201 into hits record.
> 4. Transaction B writes 201 into hits record.
> 
> and variants thereof.  (Even if A has already written 201, I don't think
> B will see it until A has committed...)
> 
> I am not too clear on MVCC yet, but I think you need "SELECT FOR UPDATE"
> or possibly an explicit lock on the hits table in order to avoid this
> problem.  Vadim, any comments?
> 
>             regards, tom lane
> 

_____________________________________________________________
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