PostgreSQL JDBC getGeneratedKeys returns all columns

Question

I've recently switched from MySQL to PostgreSQL for the back end of a project and discovered some of my database proxy methods needed reviewing. To insert linked objects I use a transaction to make sure everything is stored. I do this using jdbc methods such as setAutoCommit(false) and commit(). I've written a utility method that inserts a record into a table and returns the generated key. Basically I've followed technique 2 as described here:

http://www.selikoff.net/2008/09/03/database-key-generation-in-java-applications/

This has worked since the start of the project, but after migrating from MySQL to PostgreSQL getGeneratedKeys returns all the columns of the newly inserted record (see console output below).

Code:

final ResultSet keys = ps.getGeneratedKeys();
final ResultSetMetaData metaData = keys.getMetaData();
for (int j = 0; j < metaData.getColumnCount(); j++) {
    System.out.println("Col name: "+metaData.getColumnName(j+1));
}

Output:

Col name: pathstart
Col name: fk_id_c
Col name: xpathid
Col name: firstnodeisroot

Database signature for the table (auto generated SQL from pgAdmin III):

CREATE TABLE configuration.configuration_xpath
(
  pathstart integer NOT NULL,
  fk_id_c integer NOT NULL,
  xpathid integer NOT NULL DEFAULT nextval('configuration.configuration_xpath_id_seq'::regclass),
  firstnodeisroot boolean NOT NULL DEFAULT false,
  CONSTRAINT configuration_xpath_pkey PRIMARY KEY (xpathid),
  CONSTRAINT configuration_fk FOREIGN KEY (fk_id_c)
      REFERENCES configuration.configuration (id_c) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

Database signature for the sequence behind the PK:

CREATE SEQUENCE configuration.configuration_xpath_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 242
  CACHE 1
  OWNED BY configuration.configuration_xpath.xpathid;

So the question is, why is getGeneratedKeys returning all the columns instead of just the generated key? I've searched and found someone else with a similar problem here:

http://www.postgresql.org/message-id/004801cb7518$cbc632e0$635298a0$@[email protected]

But their question has not been answered, only a suggested workaround is offered.

Answer 1

Most drivers support getGeneratedKeys() by tacking on a RETURNING-clause at the end of the query with the columns that are auto-generated. PostgreSQL returns all fields because it has RETURNING * which simply returns all columns. That means that to return the generated key it doesn't have to query the system table to determine which column(s) to return, and this saves network roundtrips (and query time).

This is implicitly allowed by the JDBC specification, because it says:

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.

Reading between the lines you can say that this allows for saying 'I don't know, or it is too much work, so all columns best represent the auto-generated keys'.

An additional reason might be that it is very hard to determine which columns are auto-generated and which aren't (I am not sure if that is true for PostgreSQL). For example in Jaybird (the JDBC driver for Firebird that I maintain) we also return all columns because in Firebird it is impossible to determine which columns are auto-generated (but we do need to query the system tables for the column names because Firebird 3 and earlier do not have RETURNING *).

Therefor it is always advisable to explicitly query the generated keys ResultSet by column name and not by position.

Other solutions are explicitly specifying the column names or the column positions you want returned using the alternate methods accepting a String[] or int[] (although I am not 100% sure how the PostgreSQL driver handles that).

BTW: Oracle is (was?) even worse: by default it returns the ROW_ID of the row, and you need to use a separate query to get the (generated) values from that row.