Re: BUG #16958: "Invalid reference to FROM-clause entry for table" when qualifying columns in "on conflict .. where"

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,