Re: Fetching generated keys

On 5-Mar-07, at 4:08 PM, Heikki Linnakangas wrote:

> 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')"
>
I think you should be using currval( 'sequence_name') in the same
connection. It will not be the wrong one! It does the "right thing".
I believe lastval does not allow you to specify the sequence.

Dave
> --
>   Heikki Linnakangas
>   EnterpriseDB   http://www.enterprisedb.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>