Re: currval() race condition on server?

[Dave Cramer <pg(at)fastcrypt(dot)com>]

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