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
