Re: Synthesize support for Statement.getGeneratedKeys()?

On 22-Jan-07, at 4:16 AM, Michael Paesold wrote:

> Ken Johanson wrote:
>>>> As an aside, how do PG jdbc users get the server generated keys?
>>>> Or does everyone use some kind of UUID system (which I think is
>>>> generally regarded as detrimental to indexes/memory under high
>>>> load and large DB sizes - compared to int/bigint)? Or do PG
>>>> users using some standard or server-specific (RETURNING) SQL
>>>> clause?
>>>
>>> either create the key ahead of time select nextval('sequence')
>>> and insert it explicitly, or insert the row and then select
>>> currval('sequence')
>>>
>> That makes sense; the sequence is retrieved and it internally
>> increments - regardless of whether the key was actually inserted
>> or not. I'm personally not used to this though, it allows for
>> actual keys in the database to possibly have gaps (if the key want
>> actually used / rollback etc). Thats trivial / innocuous I guess,
>> but I'm just used to having sequential keys tables. Would this
>> require two trips to the server, or can we handle in one
>> excecuteUpdate?
>> My real question is, what about the case where multiple VALUES are
>> inserted; if I have 3 values should I call the sequence 3 times?
>> What is the most efficient was to do that? (Can I do it in a
>> single query?)
>
> I don't think you should use "currval" or "nextval" at all. A
> general solution in the JDBC driver should even work in the case of
> triggers that interfere with the value of a sequence. Or which
> might change the value actually inserted into the table. Just think
> of an insert trigger that uses a sequence for a second time.
>
> There is only one way to reliably get the database generated
> values: the RETURNING clause.
>
> So my basic suggestion was to rewrite a query written as:
> "INSERT INTO tab VALUES (...)"
> into
> "INSERT INTO tab VALUES (...) RETURNING x"
>
> With x being either (a) what the user specified using the Java API
> (i.e. any column names) or (b) the primary key column(s) (or other
> columns having a "DEFAULT currval(...)").
> The second case (b) I would leave for later, since it requires
> parsing the query and finding the table which will be inserted
> into. And you would have to use database meta data to find the
> columns to return.
>
Yes, agreed, Ken was just curious how it is being done now.
> Of course, there should be a minimum amount of parsing to detect if
> the query is a valid INSERT query and does not already have a
> different RETURNING clause.
>
> Another option would be to convince backend developers to add a way
> to specify a "RETURNING clause" on the protocol level, i.e. without
> having to change the query string.

Yes, this would be the best solution.
> Best Regards
> Michael Paesold
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>