Re: Not understanding this behavior of a subselect + volatile function

On May 26, 2012, at 5:22 PM, Chris Angelico wrote:

> The function is actually immaterial to this; the same thing occurs
> with this single statement:
>
> with t1upd as (update t1 set b = b + 1 where b < 1 returning a) select
> * from t1 join t1upd using (a);
>
> Poking around with the latter form of the statement and Google showed up this:
>
> http://stackoverflow.com/questions/7191902/cannot-select-from-update-returning-clause-in-postgres

That's a good link, thanks Chris. I'm not sure it entirely answers what I'm seeing though. It does explain why the outer select doesn't see the updated values, but the other thing that I'm seeing is that sometimes the function will update a row that was just inserted, and then the outer select will return 0 results. It behaves as if from the view of the outer select, that row doesn't exist yet. So I end up with a row in the table that's been updated by the function, but never returned to the caller.

With 45 clients doing this select in a continuous loop, and ~100 clients inserting into the table a few times a second, this only happens a couple times a day, so it's been difficult to debug so far.

Is it possible for the subselect to have a view from a slightly different point in time than the outer select? I wouldn't think so, but I'm not sure how else to explain what is happening.

-- Brian