Vik Fearing <vik@postgresfriends.org> writes:
> On 4/10/21 9:57 AM, Pantelis Theodosiou wrote:
>> On Fri, Apr 9, 2021 at 11:00 PM PG Bug reporting form <
>> noreply@postgresql.org> wrote:
>>> SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table "t"
>>> Hint: There is an entry for table "t", but it cannot be referenced from
>>> this part of the query.
>>>
>>> Notice the qualification of the t.b column in the "on conflict .. where"
>>> clause. I don't understand why b cannot be qualified at this location. It
>>> can be qualified in the index definition, looks like a bug to me.
>> You don't need and shouldn't prefix the column with the table name in that
>> part. This should work:
> Not needing to and not being able to are two completely different
> things. I believe tablename qualification should be allowed here even
> if it isn't necessary.
It does seem like a pointless prohibition, but the comment about it in
the source code implies it was intentional. Peter, do you remember
why?
Anyway, if the prohibition isn't necessary, the attached one-liner
gets most of the way to removing it. I'm not quite satisfied with
this though:
regression=# create table t(a int primary key, b int, c int);
CREATE TABLE
regression=# insert into t (a,b) values(1,1) on conflict(a)
where excluded.b is null do update set c=excluded.c+1;
ERROR: missing FROM-clause entry for table "excluded"
LINE 2: where excluded.b is null do update set c=excluded.c+1;
^
Seems like we want the same cannot-be-referenced HINT here as in
the complaint, so just nuking the whole namespace as the code
currently does ought to be revisited.
regards, tom lane
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index af80aa4593..4402be6845 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -3225,13 +3225,13 @@ transformOnConflictArbiter(ParseState *pstate,
List *save_namespace;
/*
- * While we process the arbiter expressions, accept only non-qualified
- * references to the target table. Hide any other relations.
+ * While we process the arbiter expressions, accept only references to
+ * the target table. Hide any other relations.
*/
save_namespace = pstate->p_namespace;
pstate->p_namespace = NIL;
addNSItemToQuery(pstate, pstate->p_target_nsitem,
- false, false, true);
+ false, true, true);
if (infer->indexElems)
*arbiterExpr = resolve_unique_index_expr(pstate, infer,