Re: Autogenerated keys and ... RETURNING

On 4-Oct-06, at 2:15 AM, Michael Paesold wrote:

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

Makes sense, additionally I'd like to be able to specify my own
insert returning clause, so If I do know what I want I can get it
without returning all of them.
>
> 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.
From what I read this requires (at a minimum) parsing the input SQL
in order to figure out the column positions
>
> Personally I think we should at least support the String[]
> columnNames case, but I would like to try supporting all cases.

Do the easy ones
>
> Comments? Anyone see a defect in my reasoning?
Dave
>
> Best Regards,
> Michael Paesold
>
> [1] http://developer.postgresql.org/pgdocs/postgres/sql-insert.html
>
>