currval -- per session -- UNDERSTOOD!

On Fri, Mar 23, 2001 at 05:28:24PM -0500, Gregory Wood wrote:
> Well, the problem is that currval() is the value last inserted by *that*
> backend into the database. The reasoning for this is simple:

okay ... :)

> If Paul just inserted a record and wants to find out what sequence value was
> inserted, he would use currval(). If currval() returned the last sequence
> value regardless of who last inserted, and Yoko inserted between Paul's
> INSERT and currval(), then Paul would get Yoko's currval(), not the one he
> just inserted. This means that if he uses that value to insert values into
> another table that are foreign keyed into the original table, he'll be
> connecting them to the wrong table.

<dawn arrive=now> aha. grok. </dawn>

> > so i can't retrieve and use the existing current value for a
> > sequence unless i bump it one? me no grok.
> > </dense>
>
> The idea is to retrieve the value after it has been used. If you grab the
> currval() and someone else grabs a currval() before you use it... bad things
> happen. Better to do the insert and see what value you ended up with. If you
> really want to grab a sequence value, just do nextval() and then you can use
> that value explicitly for an insert or whatnot.
>
> And don't feel bad, took me a while to figure all this out.

thanks for the steerage! i'll catch up eventually (but by then
everyone will have moved on...)

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'

will(at)serensoft(dot)com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!