Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> writes: > Ed L. wrote: >> I ran a simple select-for-update test on 8.1.2 and was curious as >> to why the semantics are what they are. Specifically, when you >> have multiple select-for-update-limit queries on the same rows, >> why are rows selected by the blocked query before knowing if >> some of those rows will be removed/eliminated by a preceding >> select-for-update-limit? > This is how it is just because of "historical reasons", i.e. it's a > known misfeature that no one has bothered to fix. Not so much not bothered, as that the backwards-compatibility issues seem a bit scary. If we change this behavior we could easily break more apps than we help. The implementation reason why it's like that is that FOR UPDATE filtering is handled in the top-level executor code (execMain.c) while LIMIT is a plan node type. To change it we'd need to make the FOR UPDATE filter into a plan node type that we could put underneath LIMIT instead of atop it. I occasionally think about doing that as a means for supporting FOR UPDATE in sub-SELECTs, but the real issue with that whole idea is that we don't promise a darn thing about how many times a join input relation will be read or how far it will be read or in what order. So the semantic effect of FOR UPDATE in a sub-SELECT, in terms of exactly which rows will get locked, seems impossible to state precisely. Or to put it more plainly: right now, we lock only rows that we are about to return to the client. So "which rows get locked" is exactly as well-defined as the query as a whole is. As soon as we push the locking further down into the plan, there's a bunch of unspecified implementation behaviors that will affect which rows get locked, and it's very likely that some will get locked that have nothing to do with any row that's returned to the client. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
