transformLockingClause() bug

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: transformLockingClause() bug
Date: 2022-07-06 14:12:08
Message-ID: CAEZATCUY_KOBnqxbTSPf=7fz9HWPnZ5Xgb9SwYzZ8rFXe7nb=w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)mail(dot)gmail(dot)com

Attachment Content-Type Size
transform-locking-clause.patch text/x-patch 3.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-07-06 14:20:33 Re: AIX support - alignment issues
Previous Message Dean Rasheed 2022-07-06 14:09:48 Re: Making the subquery alias optional in the FROM clause