Horton

Thread

Commits

  1. Fix alias matching in transformLockingClause().

  1. transformLockingClause() bug

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2022-07-06T14:12:08Z

    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

  2. Re: transformLockingClause() bug

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-07-06T14:30:38Z

    Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> 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).

Agreed.

> 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:

Yeah, that's clearly an oversight in the join_using_alias patch.

			regards, tom lane