Re: Synthesize support for Statement.getGeneratedKeys()?

On 22-Jan-07, at 12:09 AM, 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?

Well, this is widely debated, but in Postgresql since the sequence
cannot be rolled back (easily) you have to design for gaps. Take for
instance the case where you cache sequences for speed. If you drop
that connection you will lose all the cached values.
>
> 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?)

You have to call the sequence n times.
>
> Thank you,
> ken
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>