"RETURNING PRIMARY KEY" syntax extension

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

One possible solution would be to have the driver request the primary key for
a table, but this could cause a race condition where the primary key could change,
and even if it does not, it would entail extra overhead.

A more elegant and universal solution, which would allow the JDBC driver to
request the primary key in a single request, would be to extend the RETURNING
clause syntax with the option PRIMARY KEY. This resolves during parse
analysis into the columns of the primary key, which can be done unambiguously
because the table is already locked by that point and the primary key cannot change.

A patch is attached which implements this, and will be added to the next commitfest.
A separate patch will be submitted to the JDBC project. Example usage shown below.

Regards

Ian Barwick

/* ---------------------------------------------- */
postgres=# CREATE TABLE foo (id SERIAL PRIMARY KEY);
CREATE TABLE

postgres=# INSERT INTO foo VALUES(DEFAULT) RETURNING PRIMARY KEY;
id
----
1
(1 row)

INSERT 0 1

postgres=# CREATE TABLE bar (id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY(id1, id2));
CREATE TABLE
postgres=# INSERT INTO bar VALUES(1,2) RETURNING PRIMARY KEY;
id1 | id2
-----+-----
1 | 2
(1 row)

INSERT 0 1

postgres=# INSERT INTO bar VALUES(2,1),(2,2) RETURNING PRIMARY KEY;
id1 | id2
-----+-----
2 | 1
2 | 2
(2 rows)

INSERT 0 2

postgres=# CREATE TABLE no_pkey (id SERIAL NOT NULL);
CREATE TABLE
postgres=# INSERT INTO no_pkey VALUES(DEFAULT) RETURNING id;
id
----
1
(1 row)

INSERT 0 1
postgres=# INSERT INTO no_pkey VALUES(DEFAULT) RETURNING PRIMARY KEY;
ERROR: Relation does not have any primary key(s)

/* ---------------------------------------------- */

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