On 21-Jan-07, at 2:06 PM, Ken Johanson wrote:
> Dave Cramer wrote:
>>>
>>> 3) Is there a) an efficient RETURNING clause to pre-populate the
>>> generated-keys result set, or b) should I synthesize it.
>> I take it from this that you are intending on returning all
>> generated columns?, just generated columns that have keys ?
>>>
>
> Yes, ideally. I'd prefer not to try and synthesize the values from
> only a single (last or first row's) key, since sequences can have
> increment values other than one, and because OIDs are not
> predictable (are they?). Other server generated key types also seem
> to make the synthetic idea unfeasible.
I wouldn't worry about OID's first of all, they are no longer the
default on user tables, secondly, they are not indexed.
>
>>> 4) If 3b is required, then besides incrementing (by one)
>>> sequences, any suggestions on how to correctly synthesize other
>>> increment values? Other key types (OIDs, etc?)
>> Are you suggesting here that you are planning on incrementing the
>> sequences by 1 ? Why not just let the insert occur and get the
>> currval of the sequence ?
>
> I'm thinking what you're thinking; get the current value; however
> the increment I mention is just in case I cant get more than one
> curval... I don't know, can I get 3 values from RETURN if I insert
> three VALUEs in one query??
hmmm good question, one which I doubt the Sun people thought about. I
wouldn't bother trying to return any more than the last one.
>
>>>
>>> 5) To be absolutely sure, inserting multiple values then getting
>>> the sequence back (RETURNING) will happen atomically in the
>>> server, correct?
>>> (to avoid getting another transaction's keys).
>> You don't want to return the sequence, as it can be changed by
>> another transaction, you want to return the value in the row that
>> was inserted.
>>>
>
> Agreed.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
