Re: Last ID Problem

On Mon, Jan 31, 2005 at 08:55:22PM -0500, Mitch Pirtle wrote:
> On Mon, 31 Jan 2005 18:38:55 -0700, Michael Fuhr <mike@fuhr.org> wrote:
> >
> > Contrary to what another message in this thread says, it is indeed
> > common practice to do the insert first and call currval() afterwards
> > to find out what value you got from the sequence.  And no, this
> > doesn't introduce a race condition -- currval() returns the last
> > value obtained from the sequence in the current session.
>
> Tell that to the maintainers of PEAR's DB, which is installed by
> default with all recent versions of PHP (that would be all of them). I
> felt the exact same way as you did, and spent an afternoon
> rediscovering the joys of sequence values until one of the maintainers
> pointed out that behavior. I even tried to convince them that this was
> a bug ('inappropriate behavior' was the term IIRC)...

I don't use DB so I can't comment on what its maintainers should
or shouldn't be doing.  Abstraction layers sometimes do things in
ways that are easy to implement across multiple systems, so the
maintainers might have portability concerns.

I'm not saying that doing the INSERT first and then calling currval()
is the "right" way, just that it's a common way, one that's often
suggested on the PostgreSQL mailing lists.  One argument in its
favor is that you can use the same INSERT statement regardless of
whether you need the sequence number or not, so that's one less
thing to maintain if your needs change in that respect.

> 'Common', unfortunately, is relative; and in this matter might only
> apply to ADOdb ;-)

The world's bigger than a couple of PHP modules :-)  Calling currval()
after an INSERT is a common way to get the sequence value when using
PostgreSQL, regardless of the programming language or API being
used.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/