Re: Synthesize support for Statement.getGeneratedKeys()?

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.

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.

Best Regards
Michael Paesold