Re: Avoiding a race condition

On Sat, 2002-11-23 at 15:00, MT wrote:
> Earlier I wrote a note asking how I could insert a record and then
> display that record as verification that it had been successfully
> inserted. I got several suggestions, the following which I'd like to use:
>
> SELECT nextval('my_sequence') as id
>
> Then do the insert with the sequence and all other operations with the
> "id".
>
> I've tried to do this without success. Could someone show me the syntax
> since I can't figure it out.
>
> I've tried:
>
> SELECT nextval('prodid_seq') as id;
>
> INSERT INTO product VALUES (nextval('prodid_seq'),'...',...,'');
                              ^^^^^^^

That should be currval() or else you will increment the sequence twice.

> OK up to this point.

Not quite!

> Now how do I SELECT back the record I just inserted.
>
> SELECT * FROM product WHERE prodid = id;
> ERROR:  Attribute 'id' not found

SELECT * FROM product WHERE prodid = currval('prodid_seq');

>
> OK, I'm not very good at SQL yet. How do you put the sequence number in
> a place holder, then do the insert, and finally retrieve the record you
> just inserted by matching the prodid with the number in the place holder.

Just keep using currval() until you want to increment the sequence
again.

(If you're using a procedural language, you can store the output from
the first use of nextval() in a variable; then you can use that instead
of currval().)

--
Oliver Elphick <olly@lfix.co.uk>
LFIX Limited