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 <[email protected]> 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
