Re: Re: INSERT ... RETURNING as Oracle

"Martin A. Marques" <martin(at)math(dot)unl(dot)edu(dot)ar> writes:
> If I have a client INSERT a value in a table (all happens inside a BEGIN
> WORK ---- COMMIT WORK), which has a SERIAL column, and I want to
> reference that serial value on another INT column which is a foreign key
> of that SERIAL. Using currval() function will give me exact value as the
> FAQs say, and why? Is it the transaction that makes this work this way?

No, it has nothing to do with transaction boundaries; it'd work the same
even if you did the currval() in a later transaction. The reason it
works is that each backend remembers the last nextval() result it got
for each sequence it's nextval'd in the current session. currval() just
pulls that value out of the local table without ever touching the shared
sequence object. Simple, eh?

Of course, you can still get burnt if you have triggers and such that
do nextval()s on the same sequence your main-line code is touching.
Then one of them might do a nextval() "behind your back", so to speak.
But that sort of foulup is a straight programming error that doesn't
have anything to do with parallel behavior of multiple clients.

regards, tom lane