Thread: Autogenerated keys and ... RETURNING
Hi, Has anyone look at implementing the missing JDBC3 API parts that should return auto-generated keys using the new RETURNING clause? I had already asked about this one in 2002, but there was no supporting backend infrastructure at that time. I thought I could perhaps try to implement this now, given I find enough time in the next few weeks. Is anyone working on this item? Best Regards, Michael Paesold
No, nobody is working on it, AFAIK. The question I have is how do you know which column to return ? Dave On 3-Oct-06, at 6:00 PM, Michael Paesold wrote: > Hi, > > Has anyone look at implementing the missing JDBC3 API parts that > should return auto-generated keys using the new RETURNING clause? > > I had already asked about this one in 2002, but there was no > supporting backend infrastructure at that time. I thought I could > perhaps try to implement this now, given I find enough time in the > next few weeks. > > Is anyone working on this item? > > Best Regards, > Michael Paesold > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
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
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 > >