From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vik Fearing <vik@postgresfriends.org>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: Pantelis Theodosiou <ypercube@gmail.com>
Cc: Lukas Eder <lukas.eder@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16958: "Invalid reference to FROM-clause entry for table" when qualifying columns in "on conflict .. where"
Date: Sat, 10 Apr 2021 12:28:38 -0400
Message-ID: <2364236.1618072118@sss.pgh.pa.us> (raw)
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>
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
Attachments:allow-table-qual-in-ON-CONFLICT-wip.patch (text/x-diff, 843B) downloaddiff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.cindex 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,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, vik@postgresfriends.org, pg@bowt.ie, ypercube@gmail.com, 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: <2364236.1618072118@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox