Broken EXPLAIN output for SubPlan in MERGE
От | Dean Rasheed |
---|---|
Тема | Broken EXPLAIN output for SubPlan in MERGE |
Дата | |
Msg-id | CAEZATCWAv-sZuH+wG5xJ-+Gt7qGNGX8wUQd3XYydMFDKgRB9nw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Broken EXPLAIN output for SubPlan in MERGE
|
Список | pgsql-hackers |
While playing around with EXPLAIN and SubPlans, I noticed that there's a bug in how this is handled for MERGE. For example: drop table if exists src, tgt, ref; create table src (a int, b text); create table tgt (a int, b text); create table ref (a int); explain (verbose, costs off) merge into tgt t using (select (select r.a from ref r where r.a = s.a) a, b from src s) s on t.a = s.a when not matched then insert values (s.a, s.b); QUERY PLAN ----------------------------------------------------------- Merge on public.tgt t -> Merge Left Join Output: t.ctid, s.a, s.b, s.ctid Merge Cond: (((SubPlan 1)) = t.a) -> Sort Output: s.a, s.b, s.ctid, ((SubPlan 1)) Sort Key: ((SubPlan 1)) -> Seq Scan on public.src s Output: s.a, s.b, s.ctid, (SubPlan 1) SubPlan 1 -> Seq Scan on public.ref r Output: r.a Filter: (r.a = s.a) -> Sort Output: t.ctid, t.a Sort Key: t.a -> Seq Scan on public.tgt t Output: t.ctid, t.a SubPlan 2 -> Seq Scan on public.ref r_1 Output: r_1.a Filter: (r_1.a = t.ctid) The final filter condition "(r_1.a = t.ctid)" is incorrect, and should be "(r_1.a = s.a)". What's happening is that the right hand side of that filter expression is an input Param node which get_parameter() tries to display by calling find_param_referent() and then drilling down through the ancestor node (the ModifyTable node) to try to find the real name of the variable (s.a). However, that isn't working properly for MERGE because the inner_plan and inner_tlist of the corresponding deparse_namespace aren't set correctly. Actually the inner_tlist is correct, but the inner_plan is set to the ModifyTable node, whereas it needs to be the outer child node -- in a MERGE, any references to the source relation will be INNER_VAR references to the targetlist of the join node immediately under the ModifyTable node. So I think we want to do something like the attached. Regards, Dean
Вложения
В списке pgsql-hackers по дате отправления: