Re: Synthesize support for Statement.getGeneratedKeys()?

Dave Cramer schrieb:
>
> 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.

You are right, sorry. I wanted to be sure that he did not try to do the
same thing for the JDBC driver.

Best Regards
Michael Paesold