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