RE: obtaining primary key/rowid following insert, re dux...
От | Peter Mount |
---|---|
Тема | RE: obtaining primary key/rowid following insert, re dux... |
Дата | |
Msg-id | 1B3D5E532D18D311861A00600865478CF1B394@exchange1.nt.maidstone.gov.uk обсуждение исходный текст |
Список | 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("commitwork"); 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["cookiedomain"]); } 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
В списке pgsql-interfaces по дате отправления:
Предыдущее
От: Zeljko TrogrlicДата:
Сообщение: Re: obtaining primary key/rowid following insert, redux...