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

Reply via email to