Re: last inserted raw (identity)

On Fri, Aug 01, 2003 at 11:18:30PM -0500, nolan(at)celery(dot)tssi(dot)com wrote:
> > > See currval() and nextval().
> >
> > What if his PK isn't a sequence?
>
> Moreover, currval() and nextval() won't guarantee that you always get the
> most recently inserted sequence value, either, because each connection
> can have a cache of sequence values to assign from. While the backend
> guarantees that each sequence value will be UNIQUE there is no guarantee
> that MY currval() or nextval() is actually based on the last sequence value
> that ANYONE used.

Rubbish. currval() is guarenteed to return the last value returned by
nextval() *in this session*. So if you do a nextval() and sleep for three
days with the session open, currval() will return that value even if a
million records have been entered in the mean time.

> In short, I think the answer to the original question is that there is no
> reliable way to find out what the last record inserted was.

It returns the last record *you* entered. If you want the last record
entered by anyone (committed ofcourse), you'd use order by x desc limit 1.

In general, currval() and nextval() do exactly what you need.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato