Re: [NOVICE] Last ID Problem

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> >> How is what you're suggesting more portable?
>
> > Well, the driver would be free to implement $sth->last_insert_id() using
> > whatever proprietary extensions it has available. The non-portableness would
> > at least be hidden in the driver layer.
>
> Are you asserting that last_insert_id() is a portable function? I doubt
> it.

Well I'm not sure what you mean by "portable". It's part of the DBI driver
definition, so in theory it is. Not all drivers will implement it though, or
implement it properly, and for some it may be more efficient than others.

For postgres it looks like currently it requires you to pass in the table and
field might even need a "driver-specific hint" telling it the sequence name.

At least an application using it has a hope of working on a new driver. An
application using RETURNING will only work on Oracle and one day Postgres.

So it would be nice if the Postgres driver could efficiently implement it
without having to do a second SELECT and without having to know out of band
info like a sequence name.

This is from the DBI documentation -- that is, the non-driver-specific
abstract interface documentation.

"last_insert_id"
$rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
$rv = $dbh->last_insert_id($catalog, $schema, $table, $field, \%attr);

Returns a value 'identifying' the row just inserted, if possible.
Typically this would be a value assigned by the database server to
a column with an auto_increment or serial type. Returns undef if
the driver does not support the method or can't determine the
value.

The $catalog, $schema, $table, and $field parameters may be
required for some drivers (see below). If you don't know the
parameter values and your driver does not need them, then use
"undef" for each.

There are several caveats to be aware of with this method if you
want to use it for portable applications:

* For some drivers the value may only available immediately after
the insert statement has executed (e.g., mysql, Informix).

* For some drivers the $catalog, $schema, $table, and $field
parameters are required (e.g., Pg), for others they are ignored
(e.g., mysql).

* Drivers may return an indeterminate value if no insert has been
performed yet.

* For some drivers the value may only be available if placeholders
have not been used (e.g., Sybase, MS SQL). In this case the value
returned would be from the last non-placeholder insert statement.

* Some drivers may need driver-specific hints about how to get the
value. For example, being told the name of the database
'sequence' object that holds the value. Any such hints are passed
as driver-specific attributes in the \%attr parameter.

* If the underlying database offers nothing better, then some
drivers may attempt to implement this method by executing
""select max($field) from $table"". Drivers using any approach
like this should issue a warning if "AutoCommit" is true because
it is generally unsafe - another process may have modified the
table between your insert and the select. For situations where
you know it is safe, such as when you have locked the table, you
can silence the warning by passing "Warn" => 0 in \%attr.

* If no insert has been performed yet, or the last insert failed,
then the value is implementation defined.

Given all the caveats above, it's clear that this method must be
used with care.

The "last_insert_id" method was added in DBI 1.38.

--
greg