Re: SELECT currval within a transaction

On Wed, 6 Mar 2002, Frank Joerdens wrote:

> When inserting stuff I regularly come across the problem of wanting the
> value of the primary key row of the newly inserted row. In order to get
> it, I put the INSERT statement together with a SELECT currval into a
> transaction, like
>
> BEGIN WORK; INSERT INTO foo (bar) VALUES ('bla'); SELECT
> currval('foo_id_seq'); COMMIT WORK;
>
> My question is: Does this always work? My assumption obviously is that
> it does, i.e. that currval always returns the current value of the
> sequence as seen from within the transaction. It is conceivable
> (although I don't think it would make much sense) though that currval
> would return a different value if some other client did an insert right
> after the INSERT in the transaction (and currval would return a higher
> value). I don't believe it could be implemented that way because IMHO
> it'd break the whole notion of the atomicity of a transaction. But I'd
> like to be sure.

currval is defined to always return the value last given to your session,
so the only case you could have that fail that I see would be if a rule or
trigger invoked nextval on the sequence after the value was gotten for the
insert.