Thread: BUG #15676: FOR UPDATE is not allowed with UNION ALL (and of course with UNION/INTERSECT/EXCEPT, DISTINCT?)
BUG #15676: FOR UPDATE is not allowed with UNION ALL (and of course with UNION/INTERSECT/EXCEPT, DISTINCT?)
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15676 Logged by: Gunther Schadow Email address: gunther.schadow@gmail.com PostgreSQL version: 11.2 Operating system: all Description: SELECT ... FOR UPDATE locks the roiw(s) being retrieved. UNION ALL is a nice way to retrieving rows from two tables, and implementation of UNION ALL is just a concatenation, each row returned comes from either one of the base table, and thus could be locked. Obviously the hard set semantics relational operators UNION (without ALL), INTERSECT, EXCEPT, and really also DISTINCT, should justifiable have limits in how you might to row locking. So it is justified not to support FOR UPDATE on those concepts. But on a UNION ALL it is not justified, only that it has not been done, and the error suggests that the UNION ALL case has not bee considered as fundamentally different from the other set operators.
Re: BUG #15676: FOR UPDATE is not allowed with UNION ALL (and of course with UNION/INTERSECT/EXCEPT, DISTINCT?)
From
Tom Lane
Date:
PG Bug reporting form <noreply@postgresql.org> writes: > SELECT ... FOR UPDATE locks the roiw(s) being retrieved. > UNION ALL is a nice way to retrieving rows from two tables, and > implementation of UNION ALL is just a concatenation, each row returned comes > from either one of the base table, and thus could be locked. You can get the result you want like this: (SELECT ... FROM ... FOR UPDATE) UNION ALL (SELECT ... FROM ... FOR UPDATE) UNION ALL (SELECT ... FROM ... FOR UPDATE) ... The parens are necessary for syntactic reasons (FOR UPDATE has the wrong precedence otherwise). > Obviously the hard set semantics relational operators UNION (without ALL), > INTERSECT, EXCEPT, and really also DISTINCT, should justifiable have limits > in how you might to row locking. So it is justified not to support FOR > UPDATE on those concepts. But on a UNION ALL it is not justified, only that > it has not been done, and the error suggests that the UNION ALL case has not > bee considered as fundamentally different from the other set operators. Even if it were a good idea to treat UNION ALL as significantly different from the other set operators, which I dispute, it'd still be fairly weird to allow something like FOR UPDATE to propagate down into the individual UNION arms from outside. For one thing, SQL generally avoids letting context affect the semantics of a sub-construct. For another, an outer FOR UPDATE might be meaningless in some UNION arms or have significantly different meanings in some arms than others. The only constraint UNION puts on the sub-queries is that they yield compatible result columns, not that they are all similar SELECTs. regards, tom lane