Re: currval() race condition on server?

On 24-Oct-06, at 10:18 AM, Tom Lane wrote:

> Adriaan Joubert <a(dot)joubert(at)albourne(dot)com> writes:
>> It feels an awful lot like a timing issue where the sequence
>> number is retrieved, but there is a delay until currval can use
>> it. I'm
>> not sure how currval works.

Additionally since there is some confusion about how currval actually
works you need to be aware that currval is not necessarily the
current value of the sequence. It is the last value returned by
nextval for that connection. So what's the difference?

1) sequence = 1

2) con1 ->nextval .. sequence=2 currval = 2 for con1

3)con2 -> nextval .. sequence = 3 currval=3 for con2

4) now unless nextval is called again in connection 1 currval will
still return 2

Dave
>
> There is no "timing issue" in currval --- the server is single-
> threaded
> and it's simply not possible that currval wouldn't be aware of a
> previous nextval.
>
> The theory that sounds best to me is the one someone already mentioned
> about your trigger having a code path that doesn't execute nextval.
> Another straw to grasp at is connection pooling: are you using it,
> if so is it conceivable that the SELECT is being issued on a different
> connection than the UPDATE?
>
> regards, tom lane
>
> ---------------------------(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
>