Alvaro Herrera <alvherre@commandprompt.com> 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