Re: "RETURNING PRIMARY KEY" syntax extension - Mailing list pgsql-hackers
| From | Tom Dunstan |
|---|---|
| Subject | Re: "RETURNING PRIMARY KEY" syntax extension |
| Date | |
| Msg-id | CAPPfruwQY0Z66TRv4XmDQnyv0PrJkY+38x+P4VKhMRrw5rbPAQ@mail.gmail.com Whole thread Raw |
| In response to | Re: "RETURNING PRIMARY KEY" syntax extension (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
On 11 June 2014 10:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm not even 100% sold that automatically returning the primary keyis 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
pgsql-hackers by date: