Обсуждение: 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
Вложения
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 > >
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
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