From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Dzmura <mdz@digital-mission.com>
Cc: pgsql-interfaces@postgreSQL.org <pgsql-interfaces@postgreSQL.org>
Subject: Re: obtaining primary key/rowid following insert, redux...
Date: Mon, 11 Sep 2000 10:37:46 -0400
Message-ID: <13739.968683066@sss.pgh.pa.us> (raw)
In-Reply-To: <39B70836.94523C33@digital-mission.com>
References: <39B70836.94523C33@digital-mission.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, mdz@digital-mission.com
Subject: Re: obtaining primary key/rowid following insert, redux...
In-Reply-To: <13739.968683066@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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