On Sat, May 16, 2015 at 11:48 PM, Peter Geoghegan <p...@heroku.com> wrote: > FYI, I found an unrelated bug within ruleutils.c (looks like the > targetlist kludge in set_deparse_planstate() isn't sufficiently > general): > > postgres=# explain insert into upsert as u values('Bat', 'Bar') on > conflict (key) do update set val = excluded.val where exists (select 1 > from upsert ii where ii.key = excluded.key); > ERROR: XX000: bogus varno: 65000 > LOCATION: get_variable, ruleutils.c:5916
You pointed out that the reason for this trivial bug on Jabber, but here's the obvious fix, including an EXPLAIN regression test. -- Peter Geoghegan
From de25a4eeb108308108ba18932e03eedeb87b2bfa Mon Sep 17 00:00:00 2001 From: Peter Geoghegan <peter.geoghega...@gmail.com> Date: Mon, 18 May 2015 14:06:48 -0700 Subject: [PATCH 3/3] Fix initialization of ON CONFLICT WHERE quals In passing, add an EXPLAIN regression test for this. --- src/backend/executor/nodeModifyTable.c | 2 +- src/test/regress/expected/insert_conflict.out | 16 ++++++++++++++++ src/test/regress/sql/insert_conflict.sql | 2 ++ 3 files changed, 19 insertions(+), 1 deletion(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 72bbd62..1e9a82b 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -1697,7 +1697,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) ExprState *qualexpr; qualexpr = ExecInitExpr((Expr *) node->onConflictWhere, - mtstate->mt_plans[0]); + &mtstate->ps); resultRelInfo->ri_onConflictSetWhere = (List *) qualexpr; } diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index a6abd8b..eca9690 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -43,6 +43,22 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con -> Result (4 rows) +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do update set fruit = excluded.fruit + where exists (select 1 from insertconflicttest ii where ii.key = excluded.key); + QUERY PLAN +------------------------------------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: UPDATE + Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key + Conflict Filter: (alternatives: SubPlan 1 or hashed SubPlan 2) + -> Result + SubPlan 1 + -> Index Only Scan using both_index_expr_key on insertconflicttest ii + Index Cond: (key = excluded.key) + SubPlan 2 + -> Seq Scan on insertconflicttest ii_1 +(10 rows) + -- Neither collation nor operator class specifications are required -- -- supplying them merely *limits* matches to indexes with matching opclasses -- used for relevant indexes diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index ba2b66b..a0bdd7f 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -20,6 +20,8 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do nothing; explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit, key) do nothing; explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing; +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do update set fruit = excluded.fruit + where exists (select 1 from insertconflicttest ii where ii.key = excluded.key); -- Neither collation nor operator class specifications are required -- -- supplying them merely *limits* matches to indexes with matching opclasses -- used for relevant indexes -- 1.9.1
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers