pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedobtaining primary key/rowid following insert, redux...
5+ messages / 5 participants
[nested] [flat]
* obtaining primary key/rowid following insert, redux...
@ 2000-09-07 03:15 Mark Dzmura <mdz@digital-mission.com>
0 siblings, 2 replies; 5+ messages in thread
From: Mark Dzmura @ 2000-09-07 03:15 UTC (permalink / raw)
To: pgsql-interfaces
Peter:
Trying to solve my problem mentioned in the email of half an hour ago, I searched
through the archives of the interface list and found some of your replies suggesting
using "currval()" to get the last value assigned from a sequence... However,
here's what happens in a good database with multiple in-use sequences:
db=# select currval('foo_foo_id_seq');
ERROR: foo_foo_id_seq.currval is not yet defined in this session
As an alternative, I discovered that I can get the value this way:
db=# select last_value from foo_foo_id_seq;
last_value
---------
27
My questions are, (1) why does the currval() approach give the error message, and
(2) is it OK to use my alternative??
Finally, as far as I can tell, there is a real race condition problem here in a multiple-connection
scenario (e.g. another task can cause the sequence to be incremented between the insert
and the select) - but wrapping a transaction around the insert and select should
take care of it... Thoughts??
Thanks,
Mark Dzmura
Attachments:
mdz.vcf (text/x-vcard, 279B) download
begin:vcard
n:Dzmura;Mark
tel;fax:412-429-3361
tel;work:412-429-6001
x-mozilla-html:FALSE
org:Digital Mission, LLC
adr:;;230 E. Main St., Suite 210;Carnegie;PA;15106;USA
version:2.1
email;internet:mdz@digital-mission.com
title:President
x-mozilla-cpt:;0
fn:Mark Dzmura
end:vcard
* Re: obtaining primary key/rowid following insert, redux...
@ 2000-09-11 07:03 Zeljko Trogrlic <zeljko@technologist.com>
parent: Mark Dzmura <mdz@digital-mission.com>
1 sibling, 1 reply; 5+ messages in thread
From: Zeljko Trogrlic @ 2000-09-11 07:03 UTC (permalink / raw)
To: Mark Dzmura <mdz@digital-mission.com>; pgsql-interfaces
nextval() is the solution to your problems. currval() won't help, all
users will get same previous value.
At 05:15 7.9.2000 , Mark Dzmura wrote:
>Peter:
>
>Trying to solve my problem mentioned in the email of half an hour ago, I
>searched
>through the archives of the interface list and found some of your replies
>suggesting
>using "currval()" to get the last value assigned from a sequence... However,
>here's what happens in a good database with multiple in-use sequences:
>
>db=# select currval('foo_foo_id_seq');
>ERROR: foo_foo_id_seq.currval is not yet defined in this session
>
>As an alternative, I discovered that I can get the value this way:
>
>db=# select last_value from foo_foo_id_seq;
>last_value
>---------
> 27
>
>My questions are, (1) why does the currval() approach give the error
>message, and
>(2) is it OK to use my alternative??
>
>Finally, as far as I can tell, there is a real race condition problem here
>in a multiple-connection
>scenario (e.g. another task can cause the sequence to be incremented between
>the insert
>and the select) - but wrapping a transaction around the insert and select
>should
>take care of it... Thoughts??
>
>Thanks,
>Mark Dzmura
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: obtaining primary key/rowid following insert, re dux...
@ 2000-09-11 07:07 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Peter Mount @ 2000-09-11 07:07 UTC (permalink / raw)
To: 'Mark Dzmura' <mdz@digital-mission.com>; pgsql-interfaces
currval is throwing the error because the sequence hasn't been used in that
session/connection. currval returns the most recent allocated value which is
cached, but not necessarily the next value -1.
Sequences don't have to increment by 1, so if you have multiple concurrent
connections (as I'll show in a moment) the select may not give the correct
result (the race condition you noticed).
The cases where you would (at creation time) set the increment to more than
1 is when you know that in a transaction you'll be allocating multiple
sequence values. Instead of updating the sequence table for each insert, you
allocate a block (say for example 10) of numbers. Another connection would
get the next block of 10, and your connection can safely allocate from the
remaining numbers in the cached block. Cuts down a lot of disk acceses.
Anyhow, here's a real life example of how I use currval. Ok, this is PHP but
you'll get the idea.
This little section of code is used on one of our new websites going live at
the end of the month (and in some ways is one of the other reasons why I'm
behind with JDBC ;-) ). This bit is called when someone is creating a new
user account. Each user is actually stored in three tables (performance
reasons).
There is a sequence 'users_uid_seq' which lives in the main 'users' table,
but is also the primary key in the userinfo and usercomments tables. Here we
have a transaction where the sequence is updated when the first insert is
done, but we need the allocated value to ensure that the next two inserts
are correct:
if(!$error) {
$res=pg_Exec("begin work");
if($res) {pg_FreeResult($res);}
$res=pg_Exec("insert into users
(nick,realname,email,fakemail,passwd,sig,enabled) values
('".$nick."','".$realname."','".$email."','".$email."','".$password."','','t
')");
if($res) {pg_FreeResult($res);}
$res=pg_Exec("insert into userinfo
(uid,url,bio,karma,dailynews,totalcomments) values
(currval('users_uid_seq'),'','',0,'f',0)");
if($res) {pg_FreeResult($res);}
$res=pg_Exec("insert into usercomments
(uid,posthtml,highthresh,clbig,clsmall,reparent,nosigs,cspill,noscores,mode,
threshold) values
(currval('users_uid_seq'),'f',3,300,50,'t','f',100,'f',2,0)");
if($res) {pg_FreeResult($res);}
$res=pg_Exec("commit work");
if($res) {pg_FreeResult($res);}
// redir here
$res=pg_Exec("select currval('users_uid_seq') as a");
if($res) {
$uid=pg_Result($res,0,"a");
pg_FreeResult($res);
$time=time()+86400;
$date=date("l, d-M-y H:i:s",($time));
mySetCookie("digitalmsid",$uid,$time,"/",$GLOBALS["cookie domain"]);
}
header("Location: index.php?tm=".$time);
exit;
}
--
Peter Mount
Enterprise Support Officer, Maidstone Borough Council
Email: petermount@maidstone.gov.uk
WWW: http://www.maidstone.gov.uk
All views expressed within this email are not the views of Maidstone Borough
Council
-----Original Message-----
From: Mark Dzmura [mailto:mdz@digital-mission.com]
Sent: Thursday, September 07, 2000 4:15 AM
To: pgsql-interfaces@postgreSQL.org
Subject: [INTERFACES] obtaining primary key/rowid following insert,
redux...
Peter:
Trying to solve my problem mentioned in the email of half an hour ago, I
searched
through the archives of the interface list and found some of your replies
suggesting
using "currval()" to get the last value assigned from a sequence...
However,
here's what happens in a good database with multiple in-use sequences:
db=# select currval('foo_foo_id_seq');
ERROR: foo_foo_id_seq.currval is not yet defined in this session
As an alternative, I discovered that I can get the value this way:
db=# select last_value from foo_foo_id_seq;
last_value
---------
27
My questions are, (1) why does the currval() approach give the error
message, and
(2) is it OK to use my alternative??
Finally, as far as I can tell, there is a real race condition problem here
in a multiple-connection
scenario (e.g. another task can cause the sequence to be incremented between
the insert
and the select) - but wrapping a transaction around the insert and select
should
take care of it... Thoughts??
Thanks,
Mark Dzmura
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: obtaining primary key/rowid following insert, redux...
@ 2000-09-11 14:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Dzmura <mdz@digital-mission.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2000-09-11 14:37 UTC (permalink / raw)
To: Mark Dzmura <mdz@digital-mission.com>; +Cc: pgsql-interfaces
Mark Dzmura <mdz@digital-mission.com> writes:
> Trying to solve my problem mentioned in the email of half an hour ago,
> I searched through the archives of the interface list and found some
> of your replies suggesting using "currval()" to get the last value
> assigned from a sequence... However, here's what happens in a good
> database with multiple in-use sequences:
> db=# select currval('foo_foo_id_seq');
> ERROR: foo_foo_id_seq.currval is not yet defined in this session
That means you haven't actually done any nextval() yet in this backend,
therefore there is no "last value assigned" yet.
> As an alternative, I discovered that I can get the value this way:
> db=# select last_value from foo_foo_id_seq;
> last_value
> ---------
> 27
> My questions are, (1) why does the currval() approach give the error
> message, and (2) is it OK to use my alternative??
No, because you'd have a race condition across multiple backends.
> Finally, as far as I can tell, there is a real race condition problem
> here in a multiple-connection scenario (e.g. another task can cause
> the sequence to be incremented between the insert and the select)
Not with currval(), because that holds the last value assigned by the
current backend. Looking directly at the sequence's last_value would
indeed have a race problem.
Another way to do it, which might be more convenient than currval()
depending on your application logic, is to do select nextval() to get
a new sequence number assigned, and then explicitly insert that value
into the serial column as you insert the row, rather than relying on
the column's DEFAULT clause to compute it for you. I tend to see this
way as being logically cleaner than the insert-and-then-use-currval
way, but that's a matter of taste.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: obtaining primary key/rowid following insert, redux...
@ 2000-09-11 14:46 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Zeljko Trogrlic <zeljko@technologist.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ross J. Reedstrom @ 2000-09-11 14:46 UTC (permalink / raw)
To: pgsql-interfaces
On Mon, Sep 11, 2000 at 09:03:07AM +0200, Zeljko Trogrlic wrote:
> nextval() is the solution to your problems. currval() won't help, all
> users will get same previous value.
I must say I am getting tired of seeing the same not only FAQ, but
misinformation being spread about sequences. The nextval()/currval()
functions are completely multiuser safe. The currval('foo') is defined
to return the last value of sequence 'foo' that was returned to the
current connection. That is in fact why Mark got an error: He tried to
call it on a sequence that had not yet been incremented via nextval()
in the connection.
Currval('foo') (unlike SELECT last_value from foo) will _never_ return
a number that was not generated in the current connection: i.e. was
delivered to another backend. As a consequence, you may get gaps in the
sequence, if a transaction aborts, since sequence values are never rolled
back, but this is a small price to pay for not having lock the sequence,
and thereby serialize all your users.
Ross
>
> At 05:15 7.9.2000 , Mark Dzmura wrote:
> >Peter:
> >
> >Trying to solve my problem mentioned in the email of half an hour ago, I
> >searched
> >through the archives of the interface list and found some of your replies
> >suggesting
> >using "currval()" to get the last value assigned from a sequence... However,
> >here's what happens in a good database with multiple in-use sequences:
> >
> >db=# select currval('foo_foo_id_seq');
> >ERROR: foo_foo_id_seq.currval is not yet defined in this session
> >
> >As an alternative, I discovered that I can get the value this way:
> >
> >db=# select last_value from foo_foo_id_seq;
> >last_value
> >---------
> > 27
> >
> >My questions are, (1) why does the currval() approach give the error
> >message, and
> >(2) is it OK to use my alternative??
> >
> >Finally, as far as I can tell, there is a real race condition problem here
> >in a multiple-connection
> >scenario (e.g. another task can cause the sequence to be incremented between
> >the insert
> >and the select) - but wrapping a transaction around the insert and select
> >should
> >take care of it... Thoughts??
> >
> >Thanks,
> >Mark Dzmura
> >
> >
>
>
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-09-11 14:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-07 03:15 obtaining primary key/rowid following insert, redux... Mark Dzmura <mdz@digital-mission.com>
2000-09-11 07:03 ` Zeljko Trogrlic <zeljko@technologist.com>
2000-09-11 14:46 ` Ross J. Reedstrom <reedstrm@rice.edu>
2000-09-11 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-09-11 07:07 RE: obtaining primary key/rowid following insert, re dux... Peter Mount <petermount@maidstone.gov.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox