Re: [NOVICE] Last ID Problem - Mailing list pgsql-hackers

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> writes:
> > Tom Lane <tgl@sss.pgh.pa.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
assignedby the database server to          a column with an auto_increment or serial type. Returns undef if
thedriver 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).
Ifyou 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),
forothers 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
thename of the database            'sequence' object that holds the value. Any such hints are passed            as
driver-specificattributes in the \%attr parameter.
 
          * If the underlying database offers nothing better, then some            drivers may attempt to implement
thismethod by executing            ""select max($field) from $table"". Drivers using any approach            like this
shouldissue a warning if "AutoCommit" is true because            it is generally unsafe - another process may have
modifiedthe            table between your insert and the select. For situations where            you know it is safe,
suchas 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