Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lVNqt-0008IG-N4 for pgsql-bugs@arkaria.postgresql.org; Sun, 11 Apr 2021 00:21:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lVNqM-000751-N6 for pgsql-bugs@arkaria.postgresql.org; Sun, 11 Apr 2021 00:20:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lVNqM-00074u-FC for pgsql-bugs@lists.postgresql.org; Sun, 11 Apr 2021 00:20:50 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lVNqK-00036z-KG for pgsql-bugs@lists.postgresql.org; Sun, 11 Apr 2021 00:20:50 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 13B0KhW72556686; Sat, 10 Apr 2021 20:20:44 -0400 From: Tom Lane To: Peter Geoghegan cc: Vik Fearing , Pantelis Theodosiou , Lukas Eder , PostgreSQL mailing lists Subject: Re: BUG #16958: "Invalid reference to FROM-clause entry for table" when qualifying columns in "on conflict .. where" In-reply-to: References: <16958-963f638020de271c@postgresql.org> <8a396e1e-c2a0-5ba3-180e-697082ae397f@postgresfriends.org> <2364236.1618072118@sss.pgh.pa.us> Comments: In-reply-to Peter Geoghegan message dated "Sat, 10 Apr 2021 09:54:06 -0700" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <2556657.1618100402.0@sss.pgh.pa.us> Date: Sat, 10 Apr 2021 20:20:43 -0400 Message-ID: <2556685.1618100443@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2556657.1618100402.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Peter Geoghegan writes: > On Sat, Apr 10, 2021 at 9:28 AM Tom Lane wrote: >> 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? > No. The intention was to make it like CREATE INDEX. Apparently CREATE > INDEX allows the columns to be qualified, though, so that explanation > doesn't justify it. OK. Here's a more fleshed-out patch that makes an effort to get rid of duplicate namespace-munging. I found that creating the EXCLUDED RTE soon enough to draw the complaint I wanted about bogus references also caused this interesting change in the regression test results: ERROR: column "keyy" does not exist LINE 1: ...nsertconflicttest values (1, 'Apple') on conflict (keyy) do ..= . ^ -HINT: Perhaps you meant to reference the column "insertconflicttest.key"= . +HINT: Perhaps you meant to reference the column "insertconflicttest.key"= or the column "excluded.key". -- Have useful HINT for EXCLUDED.* RTE within UPDATE: This seems to me like an independent bug: why is the misspelled-column- name hint machinery including inaccessible columns in its results? That seems much more likely to be confusing than helpful. But if there is actually a defensible reason for doing that, then this seems like an okay change. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="allow-table-qual-in-ON-CONFLICT-1.patch"; charset="us-ascii" Content-ID: <2556657.1618100402.2@sss.pgh.pa.us> Content-Description: allow-table-qual-in-ON-CONFLICT-1.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 9f13880d19..862f18a92f 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -869,25 +869,27 @@ transformInsertStmt(ParseState *pstate, InsertStmt *= stmt) attr_num - FirstLowInvalidHeapAttributeNumber); } = - /* Process ON CONFLICT, if any. */ - if (stmt->onConflictClause) - qry->onConflict =3D transformOnConflictClause(pstate, - stmt->onConflictClause); - /* - * If we have a RETURNING clause, we need to add the target relation to - * the query namespace before processing it, so that Var references in - * RETURNING will work. Also, remove any namespace entries added in a + * If we have any clauses yet to process, set the query namespace to + * contain only the target relation, removing any entries added in a * sub-SELECT or VALUES list. */ - if (stmt->returningList) + if (stmt->onConflictClause || stmt->returningList) { pstate->p_namespace =3D NIL; addNSItemToQuery(pstate, pstate->p_target_nsitem, false, true, true); + } + + /* Process ON CONFLICT, if any. */ + if (stmt->onConflictClause) + qry->onConflict =3D transformOnConflictClause(pstate, + stmt->onConflictClause); + + /* Process RETURNING, if any. */ + if (stmt->returningList) qry->returningList =3D transformReturningList(pstate, stmt->returningList); - } = /* done building the range table and jointree */ qry->rtable =3D pstate->p_rtable; @@ -1014,6 +1016,7 @@ static OnConflictExpr * transformOnConflictClause(ParseState *pstate, OnConflictClause *onConflictClause) { + ParseNamespaceItem *exclNSItem =3D NULL; List *arbiterElems; Node *arbiterWhere; Oid arbiterConstraint; @@ -1023,29 +1026,17 @@ transformOnConflictClause(ParseState *pstate, List *exclRelTlist =3D NIL; OnConflictExpr *result; = - /* Process the arbiter clause, ON CONFLICT ON (...) */ - transformOnConflictArbiter(pstate, onConflictClause, &arbiterElems, - &arbiterWhere, &arbiterConstraint); - - /* Process DO UPDATE */ + /* + * If this is ON CONFLICT ... UPDATE, first create the range table entry + * for the EXCLUDED pseudo relation, so that that will be present while + * processing arbiter expressions. (You can't actually reference it fro= m + * there, but this provides a useful error message if you try.) + */ if (onConflictClause->action =3D=3D ONCONFLICT_UPDATE) { Relation targetrel =3D pstate->p_target_relation; - ParseNamespaceItem *exclNSItem; RangeTblEntry *exclRte; = - /* - * All INSERT expressions have been parsed, get ready for potentially - * existing SET statements that need to be processed like an UPDATE. - */ - pstate->p_is_insert =3D false; - - /* - * Add range table entry for the EXCLUDED pseudo relation. relkind is - * set to composite to signal that we're not dealing with an actual - * relation, and no permission checks are required on it. (We'll - * check the actual target relation, instead.) - */ exclNSItem =3D addRangeTableEntryForRelation(pstate, targetrel, RowExclusiveLock, @@ -1054,6 +1045,11 @@ transformOnConflictClause(ParseState *pstate, exclRte =3D exclNSItem->p_rte; exclRelIndex =3D exclNSItem->p_rtindex; = + /* + * relkind is set to composite to signal that we're not dealing with + * an actual relation, and no permission checks are required on it. + * (We'll check the actual target relation, instead.) + */ exclRte->relkind =3D RELKIND_COMPOSITE_TYPE; exclRte->requiredPerms =3D 0; /* other permissions fields in exclRte are already empty */ @@ -1061,14 +1057,27 @@ transformOnConflictClause(ParseState *pstate, /* Create EXCLUDED rel's targetlist for use by EXPLAIN */ exclRelTlist =3D BuildOnConflictExcludedTargetlist(targetrel, exclRelIndex); + } + + /* Process the arbiter clause, ON CONFLICT ON (...) */ + transformOnConflictArbiter(pstate, onConflictClause, &arbiterElems, + &arbiterWhere, &arbiterConstraint); + + /* Process DO UPDATE */ + if (onConflictClause->action =3D=3D ONCONFLICT_UPDATE) + { + /* + * Expressions in the UPDATE targetlist need to be handled like UPDATE + * not INSERT. We don't need to save/restore this because all INSERT + * expressions have been parsed already. + */ + pstate->p_is_insert =3D false; = /* - * Add EXCLUDED and the target RTE to the namespace, so that they can - * be used in the UPDATE subexpressions. + * Add the EXCLUDED pseudo relation to the query namespace, making it + * available in the UPDATE subexpressions. */ addNSItemToQuery(pstate, exclNSItem, false, true, true); - addNSItemToQuery(pstate, pstate->p_target_nsitem, - false, true, true); = /* * Now transform the UPDATE subexpressions. @@ -1079,6 +1088,14 @@ transformOnConflictClause(ParseState *pstate, onConflictWhere =3D transformWhereClause(pstate, onConflictClause->whereClause, EXPR_KIND_WHERE, "WHERE"); + + /* + * Remove the EXCLUDED pseudo relation from the query namespace, since + * it's not supposed to be available in RETURNING. (Maybe someday we + * could allow that, and drop this step.) + */ + Assert((ParseNamespaceItem *) llast(pstate->p_namespace) =3D=3D exclNSI= tem); + pstate->p_namespace =3D list_delete_last(pstate->p_namespace); } = /* Finally, build ON CONFLICT DO [NOTHING | UPDATE] expression */ diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_= clause.c index af80aa4593..89d95d3e94 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -3222,17 +3222,6 @@ transformOnConflictArbiter(ParseState *pstate, /* ON CONFLICT DO NOTHING does not require an inference clause */ if (infer) { - List *save_namespace; - - /* - * While we process the arbiter expressions, accept only non-qualified - * 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); - if (infer->indexElems) *arbiterExpr =3D resolve_unique_index_expr(pstate, infer, pstate->p_target_relation); @@ -3245,8 +3234,6 @@ transformOnConflictArbiter(ParseState *pstate, *arbiterWhere =3D transformExpr(pstate, infer->whereClause, EXPR_KIND_INDEX_PREDICATE); = - pstate->p_namespace =3D save_namespace; - /* * If the arbiter is specified by constraint name, get the constraint * OID and mark the constrained columns as requiring SELECT privilege, diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regr= ess/expected/insert_conflict.out index a54a51e5c7..66d8633e3e 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -248,7 +248,7 @@ insert into insertconflicttest values (1, 'Apple') on = conflict (keyy) do update ERROR: column "keyy" does not exist LINE 1: ...nsertconflicttest values (1, 'Apple') on conflict (keyy) do ..= . ^ -HINT: Perhaps you meant to reference the column "insertconflicttest.key"= . +HINT: Perhaps you meant to reference the column "insertconflicttest.key"= or the column "excluded.key". -- Have useful HINT for EXCLUDED.* RTE within UPDATE: insert into insertconflicttest values (1, 'Apple') on conflict (key) do u= pdate set fruit =3D excluded.fruitt; ERROR: column excluded.fruitt does not exist @@ -373,7 +373,7 @@ drop index fruit_index; create unique index partial_key_index on insertconflicttest(key) where fr= uit like '%berry'; -- Succeeds insert into insertconflicttest values (23, 'Blackberry') on conflict (key= ) where fruit like '%berry' do update set fruit =3D excluded.fruit; -insert into insertconflicttest values (23, 'Blackberry') on conflict (key= ) where fruit like '%berry' and fruit =3D 'inconsequential' do nothing; +insert into insertconflicttest as t values (23, 'Blackberry') on conflict= (key) where fruit like '%berry' and t.fruit =3D 'inconsequential' do noth= ing; -- fails insert into insertconflicttest values (23, 'Blackberry') on conflict (key= ) do update set fruit =3D excluded.fruit; ERROR: there is no unique or exclusion constraint matching the ON CONFLI= CT specification diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/s= ql/insert_conflict.sql index 43691cd335..23d5778b82 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -214,7 +214,7 @@ create unique index partial_key_index on insertconflic= ttest(key) where fruit lik = -- Succeeds insert into insertconflicttest values (23, 'Blackberry') on conflict (key= ) where fruit like '%berry' do update set fruit =3D excluded.fruit; -insert into insertconflicttest values (23, 'Blackberry') on conflict (key= ) where fruit like '%berry' and fruit =3D 'inconsequential' do nothing; +insert into insertconflicttest as t values (23, 'Blackberry') on conflict= (key) where fruit like '%berry' and t.fruit =3D 'inconsequential' do noth= ing; = -- fails insert into insertconflicttest values (23, 'Blackberry') on conflict (key= ) do update set fruit =3D excluded.fruit; ------- =_aaaaaaaaaa0--