Thread: sequence newbie question

Hi,

I'm coming over from the MS sql server world and I've been reading up on
sequences. I have a question:

I see that you can create an implicit sequence like this:
CREATE TABLE test (field varchar(5), id serial NOT NULL, PRIMARY KEY (id));

And I've read that you can replace the Microsoft @@Identity by using this:
select last_value from test_id_seq;

But what if somebody else inserts a row after your insert but before your
select? Will you end up with their ID or is the "last_value" somehow
associated with the connection?

Thanks,
Kevin

On Tue, Nov 30, 2004 at 10:57:23AM -0500, Kevin B. wrote:

> I'm coming over from the MS sql server world and I've been reading up on
> sequences. I have a question:
>
> I see that you can create an implicit sequence like this:
> CREATE TABLE test (field varchar(5), id serial NOT NULL, PRIMARY KEY (id));
>
> And I've read that you can replace the Microsoft @@Identity by using this:
> select last_value from test_id_seq;

Querying last_value returns the last value obtained by anybody, so
using it is subject to race conditions.  You probably want currval(),
which returns the last value obtained in the current session:

SELECT currval('test_id_seq');

If you need the value for subsequent inserts then you don't need
to SELECT it -- you can use currval() in the INSERT statements:

INSERT INTO product (prodname) VALUES ('Widget');
INSERT INTO sale (prodid, price) VALUES (currval('product_prodid_seq'), 9.95);

> But what if somebody else inserts a row after your insert but before your
> select? Will you end up with their ID or is the "last_value" somehow
> associated with the connection?

currval() is safe.  See the PostgreSQL documentation and FAQ:

http://www.postgresql.org/docs/7.4/static/functions-sequence.html
http://www.postgresql.org/docs/faqs/FAQ.html#4.15.3

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

"Kevin B." <db@ke5in.com> writes:
> I see that you can create an implicit sequence like this:
> CREATE TABLE test (field varchar(5), id serial NOT NULL, PRIMARY KEY (id));

Right.

> And I've read that you can replace the Microsoft @@Identity by using this:
> select last_value from test_id_seq;

No.  You want
    SELECT currval('test_id_seq');

> But what if somebody else inserts a row after your insert but before your
> select?

currval is session-local so it's not affected by what other people do.
This is why you want to look at it and not at last_value.  (I think
there are some cache effects that also make last_value less than useful,
but the concurrency reason is sufficient...)

            regards, tom lane