Re: Autogenerated keys and ... RETURNING

Dave Cramer wrote:
> No, nobody is working on it, AFAIK. The question I have is how do you
> know which column to return ?

Well, that is something I wanted to discuss here. I understand that with
the current backend support, we can't get a perfect implementation. But I
think we can support most cases, perhaps not with the best possible
performance.

For auto-detecting which columns to return, we would really need to parse
the query first, which is not something that could be done easily. On the
other hand, at least the javadocs for executeUpdate in JDK 1.4.2 say "The
driver will ignore the array if the SQL statement is not an INSERT
statement.", so we only have to parse INSERT statements, which is a much
easier task than parsing the full range of possible queries. (And we must
only parse the table and columns list).

So if we could parse such INSERT queries, we would still have to decide
which columns to fetch if the caller doesn't specify them, which would
require the driver to look at the database meta data to find out which
columns to return. That is so much overhead, I guess, that I think it would
be easier to just use "RETURNING *" and let the application decide which
columns it would like to have. Do you see any issues with this?

There are four executeUpdate methods in Statement (and corresponding
prepareStatement methods). I would suggest doing the following with them:

int executeUpdate(String sql):
Don't change, this one is not required to return auto-generated key, AFAIK.
So no overhead in the common case.

int executeUpdate(String sql, int autoGeneratedKeys):
If the caller wants auto-generated columns to be returned, use RETURNING *
to return all rows of the row. Since they do not specify which columns to
fetch, they cannot expect any certain column order anyways. Alternatively
we could parse the query, find out the relation we are going to insert
into, and fetch the primary key and any column having a DEFAULT serial. But
as I said above, that sounds like to much overhead anyways.

int executeUpdate(String sql, String[] columnNames):
This one is the easiest, and the one we should certainly support. Just add
an appropriate RETURNING clause.

int executeUpdate(String sql, int[] columnIndexes):
This one is tricky. It would be easier if RETURNING supported positional
column definitions (like in ORDER BY), but AFAIK it does not, at least not
according to the docs[1]. And since we are in beta, there is no chance for
adding it in the 8.2 release. Therefore we can again either parse the
columns list in the query, or get the columns from the meta data, or use
RETURNING * and just filter the output columns, although I would certainly
prefer parsing the query.

Personally I think we should at least support the String[] columnNames
case, but I would like to try supporting all cases.

Comments? Anyone see a defect in my reasoning?

Best Regards,
Michael Paesold

[1] http://developer.postgresql.org/pgdocs/postgres/sql-insert.html