Re: mysql's last_insert_id

On Fri, Aug 29, 2003 at 08:36:50AM +0200, Bo Lorentsen wrote:
> I made a general insert (executeUpdate) function to my general DB layer,
> that returned the newly inserted row (to be able to use the default
> initealized elements). This code will stop working when oid is disabled,
> very sad thing.
>
> I will have to push this row awareness to a higher level in the future.

Not really, the sequences have very predicatble names. For tables X with
primary key Y, the sequence is usually called X_Y_seq.

> But, how does PG refer to rows internally, and why can't we get this
> reference in the application layer. I don't care if its an integer, long
> or even a string, but I like to know the unique id of the currently and
> newly inserted row, without knowing the datamodel (table layout).

Oh, there's a CTID but that doesn't keep over an update and isn't
transaction safe anyway. Mind, you could always just call your sequences
seq_<TABLENAME>, then you wouldn't have to guess. This is how DCL does it.

> > There are various solutions. Some people use functions to do inserts, I just
> > use the string "currval(whatever)" in the application layer which the
> > database replaces with the appropriate value.
> Hmm, but what happends if more than one connection does this at the same
> time ? Then, only one of the concurrent connections will have a insert
> on the returned value, and the rest will get a wrong row reference
> returned.

Wrong. What gave you that impression? nextval() and currval() work fine with
transactions, look through the docs.

> The only safe methode would be to do a "select nextval(whatever)", and
> aply this number by "hand" to the insert, but that remove the
> possibility to make general code even more, but it will be safe.

Like I said, currval() will do what you want. Make predicatable names for
your sequences and you won't need to worry about it anymore.

--
Martijn van Oosterhout   <kleptog@svana.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