inserts/updates problem under stressing !

I answer to my previous post:

Probably ab reports wrong number of requests and
records from access_log and hits from database are consistent,

So, probably there are no problem with database,
but I'd like to know do I need something else to safely log
into database.

Regards,

Oleg
_____________________________________________________________
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

---------- Forwarded message ----------
Date: Sat, 24 Jul 1999 13:48:29 +0400 (MSD)
From: Oleg Bartunov <oleg@sai.msu.su>
To: pgsql-hackers@postgreSQL.org
Cc: pgsql-sql@postgreSQL.org
Subject: inserts/updates problem under stressing !

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

Here is what I had before testing - 181 hits for msg_id=1463

1463| 181|Sat 24 Jul 12:12:24 1999 MSD|Sat 24 Jul 12:12:34 1999 MSD
(11 rows)

12:12[zeus]:/usr/local/apache/bin>ab -c 20 -n 200 http://astronet.sai.msu.su/db/pubs.html\?msg_id=1463; psql discovery -c 'select * from hits where msg_id=1463;'

After running 20 concurent connections, total number requests of 200 I
expected hit count must be increased by 200, but some hits doesn't recorded.
test reports all requests completed successfully and there were nothing
wrong in apache error logs. It's interesting that sometimes I got even
*more* hits than expected ! I didn't noticed any problem if I use smaller
number of concurrent connections.
I didn't use explicit locking - just insert/update into table using
plpgsql function. Do I need something special to take care many concurrent
inserts/updates ?

Regards,

Oleg

Here is my test results:

This is ApacheBench, Version 1.3
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 1998-1999 The Apache Group, http://www.apache.org/

Server Software: Apache/1.3.6
Server Hostname: astronet.sai.msu.su
Server Port: 80

Document Path: /db/pubs.html?msg_id=1463
Document Length: 3564 bytes

Concurrency Level: 20
Time taken for tests: 10.120 seconds
Complete requests: 200
Failed requests: 0
Total transferred: 769800 bytes
HTML transferred: 712800 bytes
Requests per second: 19.76
Transfer rate: 76.07 kb/s received

Connnection Times (ms)
min avg max
Connect: 0 58 380
Processing: 58 734 4919
Total: 58 792 5299
msg_id|count|first_access |last_access
------+-----+----------------------------+----------------------------
1463| 370|Sat 24 Jul 12:12:24 1999 MSD|Sat 24 Jul 12:13:24 1999 MSD
(1 row)
^^^^
must be 381

Here is a entry from apache config file:

--------------------------------
PerlModule Apache::HitsDBI
<Location /db>
PerlLogHandler Apache::HitsDBI
</Location>

---------------------------------
package Apache::HitsDBI;
use Apache::Constants qw(:common);

use strict;
# preloaded in startup.pl
#use DBI ();

sub handler {
my $orig = shift;
my $url = $orig->uri;
my $args = $orig->args();
if ( $url =~ /pubs\.html/ && $args =~ /msg_id=(\d+)/ ) {
my $dbh = DBI->connect("dbi:Pg:dbname=discovery") || die DBI->errstr;
my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;
}
return OK;
}

1;
__END__

-------------------------------
create table hits (
msg_id int4 not null,
count int4 not null,
first_access datetime default now(),
last_access datetime
);
create index idx_hits on hits(msg_id);

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';

---------------------------------

_____________________________________________________________
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

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

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

On Sat, 24 Jul 1999, Oleg Bartunov wrote:

Date: Sat, 24 Jul 1999 21:00:45 +0400 (MSD)
From: Oleg Bartunov <oleg@sai.msu.su>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org
Subject: [HACKERS] Re: [SQL] inserts/updates problem under stressing !

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

Blin, just add stressing at home and also got duplicates !
funny, that at home I have P166, 64Mb system but had to raise
a number of concurrent connections to 20 to get duplicates.
At work I got them already at 10 concurrent connections.
Probably this fact illustrates a big progress in Linux kernel development -
I run at home 2.2.10 version while at work - 2.0.36 SMP.

Regards,

Oleg

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

_____________________________________________________________
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:29 24/07/99 -0400, you wrote:

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?

The usual way around this sort of problem is to update the counter as the
first thing you do in any transaction. This locks the row and prevents any
possible deadlock:

Begin
curtime := ''now'';
update hits set count = count + 1; -- Now have a lock, which causes
other updates to wait.
get diagnostics select processed into numrows;
if numrows == 0 then
cnt := 1;
-- first_access inserted on default, last_access is NULL
Insert Into hits (msg_id,count) values (keyval, cnt);
End If;
return cnt;
End;

The only hassle with this is that the patch to plpgsql for 'get
diagnostics' is not yet applied (I may not have mailed it yet...), and I am
not sure if plpgsql starts a new TX for each statment - if so, you need to
start a TX in the procedure, or prior to valling it.

----------------------------------------------------------------
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 |/

Tom Lane wrote:

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...)

You're right, Tom.

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?

SELECT FOR UPDATE will not help: if there was not record for
particular key then nothing will be locked and

(Sorry for incomplete prev message).

Tom Lane wrote:

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...)

You're right, Tom.

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?

SELECT FOR UPDATE will not help: if there was no record for
particular key then nothing will be locked and two records with
the same key will be inserted.

Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE.

Vadim

Philip Warner wrote:

The usual way around this sort of problem is to update the counter as the
first thing you do in any transaction. This locks the row and prevents any
possible deadlock:

But if there was no record then nothing will be locked...
Without ability to read dirty data LOCK is the only way.

...

diagnostics' is not yet applied (I may not have mailed it yet...), and I am
not sure if plpgsql starts a new TX for each statment - if so, you need to

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
It doesn't.

start a TX in the procedure, or prior to valling it.

Vadim

On Mon, 26 Jul 1999, Vadim Mikheev wrote:

Date: Mon, 26 Jul 1999 10:43:00 +0800
From: Vadim Mikheev <vadim@krs.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Oleg Bartunov <oleg@sai.msu.su>, pgsql-hackers@postgreSQL.org,
pgsql-sql@postgreSQL.org
Subject: Re: [SQL] inserts/updates problem under stressing !

(Sorry for incomplete prev message).

Tom Lane wrote:

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...)

You're right, Tom.

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?

SELECT FOR UPDATE will not help: if there was no record for
particular key then nothing will be locked and two records with
the same key will be inserted.

Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE.

Thanks Vadim. Just tried this, but still I see a difference between
count hits (accumulated) from db and access_log. In my test these numbers are:
95 and 109. So I lost 14 hits ! And no errors !
In my handler I have now:

my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;

am I right ?

I created hits table as:
create table hits (
msg_id int4 not null primary key,
count int4 not null,
first_access datetime default now(),
last_access datetime
);

and in error_log sometimes I see
ERROR: Cannot insert a duplicate key into a unique index
How this could be possible if I use
LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE ?

Oleg

PS.
I remind my functions is:

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';

Vadim

_____________________________________________________________
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

Oleg Bartunov wrote:

SELECT FOR UPDATE will not help: if there was no record for
particular key then nothing will be locked and two records with
the same key will be inserted.

Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE.

Thanks Vadim. Just tried this, but still I see a difference between
count hits (accumulated) from db and access_log. In my test these numbers are:
95 and 109. So I lost 14 hits ! And no errors !
In my handler I have now:

my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;

am I right ?

You should run LOCK and SELECT inside BEGIN/END (i.e. in
the same transaction), do you?

Vadim

On Mon, 26 Jul 1999, Vadim Mikheev wrote:

Date: Mon, 26 Jul 1999 10:50:28 +0800
From: Vadim Mikheev <vadim@krs.ru>
To: Philip Warner <pjw@rhyme.com.au>
Cc: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org,
Oleg Bartunov <oleg@sai.msu.su>
Subject: Re: [HACKERS] Re: [SQL] inserts/updates problem understressing !

Philip Warner wrote:

The usual way around this sort of problem is to update the counter as the
first thing you do in any transaction. This locks the row and prevents any
possible deadlock:

But if there was no record then nothing will be locked...
Without ability to read dirty data LOCK is the only way.

I agree, no data, no locking.

...

diagnostics' is not yet applied (I may not have mailed it yet...), and I am
not sure if plpgsql starts a new TX for each statment - if so, you need to

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
It doesn't.

start a TX in the procedure, or prior to valling it.

How do I start a TX in the procedure ? Is't possible ?
I don't understand this because a procedure must return something, so
there're no point where to end a TX.

Oleg

Vadim

_____________________________________________________________
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

On Mon, 26 Jul 1999, Vadim Mikheev wrote:

Date: Mon, 26 Jul 1999 14:26:06 +0800
From: Vadim Mikheev <vadim@krs.ru>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgreSQL.org,
pgsql-sql@postgreSQL.org
Subject: Re: [SQL] inserts/updates problem under stressing !

Oleg Bartunov wrote:

SELECT FOR UPDATE will not help: if there was no record for
particular key then nothing will be locked and two records with
the same key will be inserted.

Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE.

Thanks Vadim. Just tried this, but still I see a difference between
count hits (accumulated) from db and access_log. In my test these numbers are:
95 and 109. So I lost 14 hits ! And no errors !
In my handler I have now:

my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;

am I right ?

You should run LOCK and SELECT inside BEGIN/END (i.e. in
the same transaction), do you?

Good question.

I use perl DBI interface to work with postgres and I supposed it does
transaction automatically. Will check it.
Aha, got the problem. Now everything works !!!

Tnanks again,

Oleg

So, here is a working handler to *accumulate* hit statistics.

package Apache::HitsDBI;
use Apache::Constants qw(:common);

use strict;
# preloaded in startup.pl
#use DBI ();

sub handler {
my $orig = shift;
my $url = $orig->uri;
if ( $orig->args() =~ /msg_id=(\d+)/ ) {
my $dbh = DBI->connect("dbi:Pg:dbname=discovery") || die DBI->errstr;
$dbh->{AutoCommit} = 0;
my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE") || die $dbh->errstr;
my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;
my $rc = $dbh->commit || die $dbh->errstr;
}
return OK;
}

1;
__END__

Oleg

Vadim

_____________________________________________________________
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

my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;

am I right ?

You should run LOCK and SELECT inside BEGIN/END (i.e. in
the same transaction), do you?

Yes, in DBI that translates to switching AutoCommit off, and doing an
explicit commit, (roughly)

$dbh->{AutoCommit} = 0;
eval {
$dbh->do (...)
...
};
if ($@) {
// There was an error
$dbh->rollback();
} else {
$dbh->commit();
}

I think you need to set RaiseError=>1 as well when connecting to the
database, to get die's inside the eval.

Adriaan

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
??????????
-- 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