Re: "RETURNING PRIMARY KEY" syntax extension

On 11 June 2014 10:09, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I'm not even 100% sold that automatically returning the primary key
> is going to save any application logic. Could somebody point out
> *exactly* where an app is going to save effort with this type of
> syntax, compared to requesting the columns it wants by name?
> Is it going to save enough to justify depending on a syntax that won't
> be universal for a long time to come?
>

Well, in e.g. Hibernate there's piece of code which calls
getGeneratedKeys() to fetch the inserted primary key (it only supports
fetching a single generated key column in this way) if the underlying
database supports that. The postgresql dialect specifies that it does
support that code path, so at the moment any hibernate users who aren't
explicitly specifying the "sequence" type for their id generation will be
calling that, and the JDBC driver will be appending "RETURNING *" under the
hood for all inserts.

Looking at the oracle hibernate dialect is instructive as to the state of
support for the explicit-column-list variation:

// Oracle driver reports to support getGeneratedKeys(), but they only

// support the version taking an array of the names of the columns to

// be returned (via its RETURNING clause). No other driver seems to

// support this overloaded version.

And so hibernate doesn't support the explicit-column-list version at all
since apparently no-one else supports it, and just marks that code path as
unsupported for oracle. I presume that the situation is similar in other
java-based ORMs.

Looking at some other drivers that I would expect to support
getGeneratedKeys() in a sane way given their identity/auto-increment
semantics reveals:

- JTDS driver for MSSQL/Sybase piggybacks a second query to do "SELECT
SCOPE_IDENTITY() AS blah" / "SELECT @@IDENTITY AS blah" to fetch the key if
that was requested. It looks like this driver does support specifying the
column name, but it only allows a single column to be given, and it
promptly ignores the passed in value and calls the non-specified version.

- MySQL driver internally returns a single ID with the query result, and
the driver then appears to add an auto-increment amount to calculate the
rest of the values. I guess MySQL must allocate the ids in
guaranteed-sequential chunks. MySQL only supports a single auto-increment
key. If called with the explicit column version, the passed-in column names
are ignored.

So looks like other JDBC driver/server combos only support this for
single-column primary keys. But for those cases things pretty much work as
expected. It would be nice to be able to support at least primary keys with
this feature.

We could try to teach every ORM out there to call the explicit column-list
version, but given other lack of support for it I doubt they'll be
interested, especially if the reason is because we don't want to add enough
support to make getGeneratedKeys() work efficiently.

FWIW I reckon for most users of ORMs at least it will be enough to support
this for direct inserts to tables - views is a nice-to-have but I'd take
tables-only over not at all.

Cheers

Tom