While doing more testing of [1], I realised that it has a bug, which
reveals a pre-existing problem in transformLockingClause():
CREATE TABLE t1(a int);
CREATE TABLE t2(a int);
CREATE TABLE t3(a int);
SELECT 1
FROM t1 JOIN t2 ON t1.a = t2.a,
t3 AS unnamed_join
FOR UPDATE OF unnamed_join;
ERROR: FOR UPDATE cannot be applied to a join
which is wrong, because it should lock t3.
Similarly:
SELECT foo.*
FROM t1 JOIN t2 USING (a) AS foo,
t3 AS unnamed_join
FOR UPDATE OF unnamed_join;
ERROR: FOR UPDATE cannot be applied to a join
The problem is that the parser has generated a join rte with
eref->aliasname = "unnamed_join", and then transformLockingClause()
finds that before finding the relation rte for t3 whose user-supplied
alias is also "unnamed_join".
I think the answer is that transformLockingClause() should ignore join
rtes that don't have a user-supplied alias, since they are not visible
as relation names in the query (and then [1] will want to do the same
for subquery and values rtes without aliases).
Except, if the rte has a join_using_alias (and no regular alias), I
think transformLockingClause() should actually be matching on that and
then throwing the above error. So for the following:
SELECT foo.*
FROM t1 JOIN t2 USING (a) AS foo,
t3 AS unnamed_join
FOR UPDATE OF foo;
ERROR: relation "foo" in FOR UPDATE clause not found in FROM clause
the error should actually be
ERROR: FOR UPDATE cannot be applied to a join
So something like the attached.
Thoughts?
Regards,
Dean
[1] https://www.postgresql.org/message-id/flat/CAEZATCUCGCf82=hxd9N5n6xGHPyYpQnxW8HneeH+uP7yNALkWA@mail.gmail.com
