On May 26, 2012, at 5:22 PM, Chris Angelico wrote:
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.