Re: "RETURNING PRIMARY KEY" syntax extension

On 06/10/2014 11:02 AM, Tom Dunstan wrote:
> On 10 June 2014 17:49, Hannu Krosing <hannu(at)2ndquadrant(dot)com
> <mailto:hannu(at)2ndquadrant(dot)com>> wrote:
>
> RETURNING GENERATED KEYS perhaps, but then how do we determine that?
> What about RETURNING CHANGED FIELDS ?
>
> Might be quite complicated technically, but this is what is
> probably wanted.
>
>
> Seems to be getting further away from something that describes the
> main use
> case - changed fields sounds like something that would apply to an
> update statement.
Not really - it applies to both INSERT and UPDATE if there are any
triggers and/or default values

The use-case is an extended version of getting the key, with the main
aim of making sure
that your ORM model is the same as what is saved in database.
>
>> Any column that was filled with a default value? But that's
>> potentially returning far more values than the user will want - I
>> bet 99% of users just want their generated primary key.
>
> Probably not true - you would want your ORM model to be in sync
> with what is database after you save it if you plan to do any
> further processing using it.
>
>
> Well, yes, but since RETURNING is non-standard most ORMs are unlikely
> to support fetching other generated values that way anyway. The ones
> that I've dealt with will do an insert, then a select to get the extra
> fields. I don't know if other JDBC drivers allow applications to just
> specify any old list of non-key columns to the execute method, but I
> suspect not, given that the way they fetch those columns is rather
> less general-purpose than our RETURNING syntax.
>
>
>
>> The second paragraph refers to [3] and [4] where the application
>> can specify which columns it's after. Given that there's a
>> mechanism to specify which keys the application wants returned in
>> the driver, and the driver in that case can just issue a
>> RETURNING clause with a column list, my gut feel would be to just
>> support returning primary keys as that will handle most cases of
>> e.g. middleware like ORMs fetching that without needing to know
>> the specific column names.
> Why not then just leave the whole thing as it is on server side,
> and let the ORM specify which "generated keys" it wants ?
>
>
> Because java-based ORMs (at least) mostly don't have to - other
> server/driver combos manage to implement getGeneratedKeys() without
> being explicitly given a column list, they just do the sane thing and
> return the appropriate identity column or whatever for the inserted row.
>
> I agree that in hand-crafted JDBC there's no particular problem in
> making a user specify a column list, (although I don't think I've EVER
> seen anyone actually do that in the wild), but most middleware will
> expect getGeneratedKeys() to just work and we should try to do
> something about making that case work a bit more efficiently than it
> does now.
But does the ORM already not "know" the names of auto-generated keys and
thus could easily replace them for * in RETURNING ?
>
> Cheers
>
> Tom

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ