Re: Possible bug related to primary keys autogeneration

On 15 July 2016 at 23:13, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>
wrote:

> пт, 15 июл. 2016 г. в 18:07, Andrea Bergia <a(dot)bergia(at)list-group(dot)com>:
>
>> I've noticed that everything works as expected if I use:
>>
>> connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
>>
>
> By the way: current pgjdbc returns all the columns in case of
> Statement.RETURN_GENERATED_KEYS. So be careful when using it with wide
> tables.
> It's better sticking with String[] kind of APIs or filing a PR so
> "RETURN_GENERATED_KEYS" fetches only keys, not all the columns.
>

Use the specified-column form.

To make RETURN_GENERATED_KEYS return just keys, we'd have to do another
round trip to the database and hit the catalogs to fetch metadata. Since
that's so inefficient we'd likely need to cache it, but since it can change
we'd need a way to invalidate that cache and PostgreSQL doesn't send the
driver notifications when there are schema changes. (I just added a
protocol TODO for the mythical v4 protocol for that). We've got no way to
safely invalidate our cache, so we have to cache unsafely and figure we'll
get away with it, or not cache and pay the round trip cost. The latter
would make sense, but it sucks.

Also, what's a "generated key"? The spec doesn't really say.

Any column with a DEFAULT? What if it's set by a trigger instead, so we
can't tell?

Any column with a DEFAULT and a UNIQUE constraint or PRIMARY KEY constraint?

Just the PRIMARY KEY?

Anyone interested in this topic should read the prior JDBC discussions
around RETURN_GENERATED_KEYS and the pgsql-hackers RETURNING GENERATED KEYS
/ RETURNING PRIMARY KEY discussions.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services