Re: Fetching generated keys

On 5-Mar-07, at 4:39 PM, Mike Clements wrote:

> But will it be transactionally safe?
>
> Consider 2 transactions, A and B, each doing:
>
> Start transaction
> Insert into tbl...
> Select lastval('sequence...
> commit
>
> The table's primary key has a default value which is defined by the
> sequence - not provided by the caller.
>
> Now suppose they get executed by Postgres in this order:
>
> A:    insert into tbl...
> B:    insert into tbl...
> B:    select lastval('sequence'...
> B:    commit
> A:    select lastval('sequence'...
> A:    commit
>
> Because the default transaction isolation is "read committed", in the
> above example A will get B's primary key value.
>
currval will do the correct thing, it doesn't matter the order.

and if you do have 8.2 insert returning does the right thing too
Dave
> I'm asking what's the recommended best practice to work around this. I
> could set the transaction isolation to "serializable" but is that
> recommended?
>
> Thanks
>
> -----Original Message-----
> From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of
> Heikki
> Linnakangas
> Sent: Monday, March 05, 2007 4:09 PM
> To: Mike Clements
> Cc: PostgreSQL JDBC List
> Subject: Re: [JDBC] Fetching generated keys
>
> Mike Clements wrote:
>> Up to now, we were selecting the current value of the sequence
>> immediately after the insert. I thought this was safe because
>> transactions should be isolated. But now I realize this could
>> potentially fail because the default transaction isolation is "read
>> committed". Thus if another connection inserts into the same table,
>> causing the sequence to increment, if it commits before we read the
>> sequence value, we might read the wrong value (the value as
>> incremented by the other transaction, not the value as it was for our
> own insert).
>
> You should use the lastval-function. It retrieves the last value
> returned in your connection. "SELECT lastval('sequence')"
>
> --
>    Heikki Linnakangas
>    EnterpriseDB   http://www.enterprisedb.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>