I'm fooling around with pushing FOR UPDATE locking into a new plan node type, and I just noticed a behavior that seems a bit bogus. Historically we have dealt with FOR UPDATE in sub-selects by flattening the sub-select if we could, because the alternative was to fail altogether. For example, consider
select * from a join (select * from b for update) ss on a.x = ss.y; The FOR UPDATE effectively got hoisted to the top because that's where we could implement it, making this equivalent to select * from a join b on a.x = b.y for update of b; It seems to me, though, that this is changing the semantics. In the latter case it's clear that we should only lock b rows that have a join partner in a (which indeed is what happens). In the former case, what I think should be expected to happen is that *all* b rows get locked. With FOR UPDATE as a plan node, it's possible to fix this by treating FOR UPDATE in a sub-select as an optimization fence that prevents flattening of the sub-select, much like LIMIT has always done. The FOR UPDATE node will end up at the top of the subplan and it will act as the syntax would suggest. Of course the downside of changing it is that queries that worked fine before might work differently (and much slower) now; first because not flattening the sub-select might lead to a worse plan, and second because locking more rows takes more time. The alternative would be to let it continue to flatten such sub-selects when possible, and to tell anyone who doesn't want that to stick in OFFSET 0 as an optimization fence. It's an entirely trivial code change either way. I'm inclined to think that we should prevent flattening, on the grounds of least astonishment. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers