Hi Tom and others,

> I think the correct solution is not to mess with what's admittedly a
legacy aspect of
> our client API. Instead we should invent the "INSERT RETURNING" and
"UPDATE RETURNING"
> commands that have been discussed repeatedly (see the pghackers archives).
That would
> allow people to get what they want, and do so in only one network round
trip, without
> any artificial dependencies on OIDs or TIDs or anything else. It'd be
unportable, but
> surely no more so than relying on OIDs or TIDs ...

Just off the top of my head, would it not be feasible to add a column to
pg_class called lastinsert that points to the OID of the pg_attribute column
to return after an insert? It could be changed using something similar to
"ALTER TABLE x SET LASTINSERT TO y", but by default it would be set to the
OID of the primary key of the table if the table specified WITHOUT OIDS at
creation time, or the first column of the table otherwise. After the INSERT
command, the value of the resulting is column is passed back to the client.

I see that INSERT...RETURNING is a solution to the problem, but it seems
somewhat strange to have to use an unportable command just to be able to
return an identifier for the last inserted record...


Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Tom Lane wrote:
> 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.

I'm not familiar with the Perl interface, but JDBC has a standardized
interface for this:

http://java.sun.com/j2se/1.5.0/docs/...tring,%20i nt)
http://java.sun.com/j2se/1.5.0/docs/...GeneratedKeys()

I tend to agree that a protocol-level change is easier to support in a
driver. If it's done by extending INSERT/UPDATE, the driver will need to
parse and modify queries which is hairy at the best of times.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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. Switch out the driver and the right
thing would happen.

"INSERT/UPDATE ... RETURNING" isn't something a driver can take advantage of.
It would require it to modify your statements which it can't do safely. So
your application would have such non-portable SQL code written into it. Switch
databases and your application code needs to be ported.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Greg Stark <gsstark@mit.edu> writes:
> This is from the DBI documentation -- that is, the non-driver-specific
> abstract interface documentation.

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

Aside from the numerous serious problems pointed out in the
documentation, this has an even more fatal objection, which is that it's
unspecified what the result value is and thus there is no portable way
of *using* the result after you have it. (If the PG driver returns an
OID you certainly couldn't use that the same way as some other driver
that returns a primary key ... especially a multicolumn primary key ...)

This "portable" function is so unportable that I see no reason to
accept it as precedent.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly