Skip site navigation (1) Skip section navigation (2)

Re: [NOVICE] Last ID Problem

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [NOVICE] Last ID Problem
Date: 2005-02-02 19:35:24
Message-ID: 87vf9an4ar.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Joshua D. DrakeDate: 2005-02-02 20:01:11
Subject: pg_dump bug in 7.3.9 with sequences
Previous:From: Peter EisentrautDate: 2005-02-02 19:32:11
Subject: Re: libpq API incompatibility between 7.4 and 8.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group