Re: "RETURNING PRIMARY KEY" syntax extension

On Monday, June 9, 2014, Ian Barwick <ian(at)2ndquadrant(dot)com> wrote:

>
>
> On 09/06/14 14:47, David G Johnston wrote:
>
>> Ian Barwick wrote
>>
>>> Hi,
>>>
>>> The JDBC API provides the getGeneratedKeys() method as a way of
>>> retrieving
>>> primary key values without the need to explicitly specify the primary key
>>> column(s). This is a widely-used feature, however the implementation has
>>> significant
>>> performance drawbacks.
>>>
>>> Currently this feature is implemented in the JDBC driver by appending
>>> "RETURNING *" to the supplied statement. However this means all columns
>>> of
>>> affected rows will be returned to the client, which causes significant
>>> performance problems, particularly on wide tables. To mitigate this, it
>>> would
>>> be desirable to enable the JDBC driver to request only the primary key
>>> value(s).
>>>
>>
>>
ISTM that having a non-null returning clause variable when no returning is
present in the command makes things more complicated and introduces
unnecessary checks in the not uncommon case of multiple
non-returning commands being issued in series.

returningList was able to be null and so should returningClause. Then if
non-null first check for the easy column listing and then check for the
more expensive PK lookup request.

Then again the extra returning checks may just amount noise.

David J.