> > currval() is the value last used by the backend, not by the database.
> > Meaning that you have to actually use the sequence before the backend
has a
> > value to retrieve. That means either doing a nextval() or doing an
INSERT
> > (which implicitly performs the nextval() ).
>
> <dense mode=on>
> seems like the backend (server?) would know the current value of
> a sequence, since it has to get it, then add one to it, to get
> the next one in line. if george inserts a record, i'd think that
> ringo would be able to see the current counter afterwards,
> independent of session...
Well, the problem is that currval() is the value last inserted by *that*
backend into the database. The reasoning for this is simple:
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.
> 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.
