Re: mysql's last_insert_id

On Fri, 2003-08-29 at 04:11, Martijn van Oosterhout wrote:

> OIDs have never beebn unique, it's just that most databases never get big
> enough to experience wraparound. They are also now optional per table and
> may soon no longer be available by default.
Ok, that may break some of my code :-( But as long as oid's are enabled
and are unique for the current table, I'm happy.

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.

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).

> 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.

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.

/BL