Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Actually, it looks like currval is defined to give the
> value last used in your session. So, the second case
> (not in transaction) should still always give the
> value of the last nextval, assuming they're part of
> the same session.
Stephan is correct: you can safely do
insert into foo(B) values (Bvalue); -- column A is defaulted
select currval('foo_A_seq');
even without a transaction block, and be assured of getting the same
value back that was assigned to A.
At least, you don't have to worry about other backends messing you up.
But it's still possible to shoot yourself in the foot. For example, if
you have rules or triggers on insert to foo, and those rules/triggers
themselves cause additional calls to nextval('foo_A_seq'), then the
eventual currval() will return the latest such result, which might not
have been what got inserted into foo. Note that using a transaction
block will not protect you from this gaffe.
For this reason, and because it just seems cleaner to me, I prefer to
solve this problem like so:
select nextval('foo_A_seq');
insert into foo(A,B) values (just-returned-value, Bvalue);
That gets the same result with about the same amount of work, but
seems more understandable and safer to me. It's mostly a matter of
taste, though.
regards, tom lane