Re: "RETURNING PRIMARY KEY" syntax extension - Mailing list pgsql-hackers

On 10 June 2014 17:49, Hannu Krosing <hannu@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.

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.

Cheers

Tom

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