On 06/10/2014 11:02 AM, Tom Dunstan wrote:
> On 10 June 2014 17:49, Hannu Krosing <ha...@2ndquadrant.com
> <mailto:ha...@2ndquadrant.com>> wrote:
>     RETURNING GENERATED KEYS perhaps, but then how do we determine that?
>     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Ü

Reply via email to