transformLockingClause() bug - Mailing list pgsql-hackers

From Dean Rasheed
Subject transformLockingClause() bug
Date
Msg-id CAEZATCUY_KOBnqxbTSPf=7fz9HWPnZ5Xgb9SwYzZ8rFXe7nb=w@mail.gmail.com
Whole thread Raw
Responses Re: transformLockingClause() bug
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Dean Rasheed
Date:
Subject: Re: Making the subquery alias optional in the FROM clause
Next
From: Tom Lane
Date:
Subject: Re: AIX support - alignment issues