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+up7ynal...@mail.gmail.com
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c new file mode 100644 index 1bcb875..8ed2c4b --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -3291,11 +3291,28 @@ transformLockingClause(ParseState *pstat foreach(rt, qry->rtable) { RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt); + char *rtename; ++i; if (!rte->inFromCl) continue; - if (strcmp(rte->eref->aliasname, thisrel->relname) == 0) + + /* + * A join RTE without an alias is not visible as a relation + * name and needs to be skipped (otherwise it might hide a + * base relation with the same name), except if it has a USING + * alias, which *is* visible. + */ + if (rte->rtekind == RTE_JOIN && rte->alias == NULL) + { + if (rte->join_using_alias == NULL) + continue; + rtename = rte->join_using_alias->aliasname; + } + else + rtename = rte->eref->aliasname; + + if (strcmp(rtename, thisrel->relname) == 0) { switch (rte->rtekind) { diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out new file mode 100644 index 2538bd6..1f0df6b --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2501,6 +2501,39 @@ ERROR: column t1.x does not exist LINE 1: select t1.x from t1 join t3 on (t1.a = t3.x); ^ HINT: Perhaps you meant to reference the column "t3.x". +-- Test matching of locking clause with wrong alias +select t1.*, t2.*, unnamed_join.* from + t1 join t2 on (t1.a = t2.a), t3 as unnamed_join + for update of unnamed_join; + a | b | a | b | x | y +---+---+---+---+---+--- +(0 rows) + +select foo.*, unnamed_join.* from + t1 join t2 using (a) as foo, t3 as unnamed_join + for update of unnamed_join; + a | x | y +---+---+--- +(0 rows) + +select foo.*, unnamed_join.* from + t1 join t2 using (a) as foo, t3 as unnamed_join + for update of foo; +ERROR: FOR UPDATE cannot be applied to a join +LINE 3: for update of foo; + ^ +select bar.*, unnamed_join.* from + (t1 join t2 using (a) as foo) as bar, t3 as unnamed_join + for update of foo; +ERROR: relation "foo" in FOR UPDATE clause not found in FROM clause +LINE 3: for update of foo; + ^ +select bar.*, unnamed_join.* from + (t1 join t2 using (a) as foo) as bar, t3 as unnamed_join + for update of bar; +ERROR: FOR UPDATE cannot be applied to a join +LINE 3: for update of bar; + ^ -- -- regression test for 8.1 merge right join bug -- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql new file mode 100644 index a27a720..b5f41c4 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -520,6 +520,28 @@ select * from t1 left join t2 on (t1.a = select t1.x from t1 join t3 on (t1.a = t3.x); +-- Test matching of locking clause with wrong alias + +select t1.*, t2.*, unnamed_join.* from + t1 join t2 on (t1.a = t2.a), t3 as unnamed_join + for update of unnamed_join; + +select foo.*, unnamed_join.* from + t1 join t2 using (a) as foo, t3 as unnamed_join + for update of unnamed_join; + +select foo.*, unnamed_join.* from + t1 join t2 using (a) as foo, t3 as unnamed_join + for update of foo; + +select bar.*, unnamed_join.* from + (t1 join t2 using (a) as foo) as bar, t3 as unnamed_join + for update of foo; + +select bar.*, unnamed_join.* from + (t1 join t2 using (a) as foo) as bar, t3 as unnamed_join + for update of bar; + -- -- regression test for 8.1 merge right join bug --