Re: Last value inserted

Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com> writes:
> --- Jerry III <jerryiii(at)hotmail(dot)com> wrote:
>> Which means that sometimes they do not return the
>> correct value - if you
>> have a trigger that inserts another record you will
>> not get the right value.

> If you are new to PostgreSQL, as you say, then why are
> you so sure of this? Perhaps you may profit from
> looking a little more at how currval() works.

Jerry's correct, although the concern is more theoretical than real IMHO.
What he's imagining is a situation where you do, say,

INSERT INTO foo ...;
SELECT currval('foo_id_seq');

and there is an ON INSERT trigger on foo that directly or indirectly
does a nextval('foo_id_seq'). Execution of the trigger will then
advance the sequence beyond what was used to generate the inserted row,
and the subsequent currval() will return the wrong answer (or at least
not the answer you wanted). Note there is no race condition here; it's
just one process involved.

The reason I think this is mostly a theoretical issue is that I don't
see any good reason for such a trigger to be doing a nextval on the
table's ID sequence. The trigger is certainly not going to insert
additional rows in foo --- if it did that would lead to infinite
recursion of the trigger. So ISTM this scenario is really not
interesting. If it did happen it would represent a bug in your
application design, no more and no less. For instance, using the same
sequence to feed IDs for multiple tables would be a bug if you had a
trigger that did an insert on one of them as a consequence of an insert
on another.

regards, tom lane