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