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

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@nasby.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?

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.

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.

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()

[3] http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20int[])

[4] http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20java.lang.String[])

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