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