Re: "RETURNING PRIMARY KEY" syntax extension

On 06/10/2014 03:19 AM, Tom Dunstan wrote:
> A definite +1 on this feature. A while ago I got partway through
> hacking the hibernate postgres dialect to make it issue a RETURNING
> clause to spit out the primary key before I realised that the driver
> was already doing a RETURNING * internally.
>
> On 10 June 2014 05:53, Jim Nasby <jim(at)nasby(dot)net
> <mailto:jim(at)nasby(dot)net>> wrote:
>
> > I was wondering that myself. I think it's certainly reasonable to expect
> > someone would wan RETURNING SEQUENCE VALUES, which would return the
> value of
> > every column that owned a sequence (ie: ALTER SEQUENCE ... OWNED
> BY). ISTM
> > that would certainly handle the performance aspect of this, and it
> sounds
> > more in line with what I'd expect getGeneratedKeys() to do.
>
> Keep in mind that not all generated keys come from sequences. Many
> people have custom key generator functions, including UUIDs and other
> exotic things like Instagram's setup [1].
>
> 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.
> 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.

At least I would :)
>
> The spec is a bit vague [2]:
>
> Retrieves any auto-generated keys created as a result of executing
> this Statement object. If this Statement object did not generate any
> keys, an empty ResultSet object is returned.
>
> Note:If the columns which represent the auto-generated keys were
> not specified, the JDBC driver implementation will determine the
> columns which best represent the auto-generated keys.
>
> 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 ?
>
> Cheers
>
> Tom
>
>
> [1]
> http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
> [2]
> http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()
> <http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys%28%29>
> [3] http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20int[])
> <http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute%28java.lang.String,%20int[]%29>
> [4] http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20java.lang.String[])
> <http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute%28java.lang.String,%20java.lang.String[]%29>

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