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