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

Thanks so much tom! I feel a lot better going with this fix now that I know for sure what was going wrong.

-- Brian

On May 26, 2012, at 8:08 PM, Tom Lane wrote:

> Brian Palmer <brian(at)codekitchen(dot)net> writes:
>> The final line, the select, will return the row as it was before the
>> function ran, (1,0) instead of (1,1). It's as if the outer select
>> locked its view of the table in place before the inner select ran.
>
> Yes, that's exactly correct. A plain SELECT always returns data that is
> visible as of its "snapshot", ignoring anything that happened later ---
> even volatile functions executing in the same transaction.
>
>> What seems even stranger to me is that if a row is inserted at just the right moment, the inner function can select it and update it, then return its primary key, but the outer select won't even see that row, and so it will return 0 rows even though the row got updated.
>
> Volatile functions have their own snapshot that is independent of the
> calling query's. So it's definitely possible for a volatile function to
> "see" a row that was committed just after the outer select took its
> snapshot. That row cannot be seen by the outer query, though.
>
> You can tweak these rules to some extent by using SELECT FOR UPDATE
> and different transaction isolation modes. See the "concurrency
> control" chapter in the manual.
>
> regards, tom lane