From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vik Fearing <vik@postgresfriends.org>
cc: Peter Geoghegan <pg@bowt.ie>, Pantelis Theodosiou <ypercube@gmail.com>,
        Lukas Eder <lukas.eder@gmail.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16958: "Invalid reference to FROM-clause entry for table"
 when qualifying columns in "on conflict .. where"
In-reply-to: <8a396e1e-c2a0-5ba3-180e-697082ae397f@postgresfriends.org>
References: <16958-963f638020de271c@postgresql.org>
 <CAE3TBxxXQ2+7EaBvb+zEFL6iTw90xq0+faHEqK2gDS0tTgiNmQ@mail.gmail.com>
 <8a396e1e-c2a0-5ba3-180e-697082ae397f@postgresfriends.org>
Comments: In-reply-to Vik Fearing <vik@postgresfriends.org>
	message dated "Sat, 10 Apr 2021 12:28:04 +0200"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <2364211.1618072075.0@sss.pgh.pa.us>
Date: Sat, 10 Apr 2021 12:28:38 -0400
Message-ID: <2364236.1618072118@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2364236.1618072118%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2364211.1618072075.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable

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 t=
able "t"
>>> Hint: There is an entry for table "t", but it cannot be referenced fro=
m
>>> this part of the query.
>>> =

>>> Notice the qualification of the t.b column in the "on conflict .. wher=
e"
>>> 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 t=
hat
>> 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=3D# create table t(a int primary key, b int, c int);
CREATE TABLE
regression=3D# insert into t (a,b) values(1,1) on conflict(a)
where excluded.b is null do update set c=3Dexcluded.c+1;
ERROR:  missing FROM-clause entry for table "excluded"
LINE 2: where excluded.b is null do update set c=3Dexcluded.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


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="allow-table-qual-in-ON-CONFLICT-wip.patch";
	charset="us-ascii"
Content-ID: <2364211.1618072075.2@sss.pgh.pa.us>
Content-Description: allow-table-qual-in-ON-CONFLICT-wip.patch
Content-Transfer-Encoding: quoted-printable

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 =3D pstate->p_namespace;
 		pstate->p_namespace =3D NIL;
 		addNSItemToQuery(pstate, pstate->p_target_nsitem,
-						 false, false, true);
+						 false, true, true);
 =

 		if (infer->indexElems)
 			*arbiterExpr =3D resolve_unique_index_expr(pstate, infer,

------- =_aaaaaaaaaa0--