I wrote:
> Bezverhijs Eduards <Eduards.Bezverhijs@tieto.com> writes:
>> We encountered a bug in our systems with update statement, but long story short, here's the self-containing test
casewhich results in segmentation fault.
> Huh. I can reproduce this in 9.6 and 10, but not earlier or later
> branches. Looking ...
Ah, I see the problem: v10 generates a plan like this:
regression=# explain verbose UPDATE t1
SET (a) = (SELECT b FROM t2 WHERE t2.b = t1.a)
WHERE current_user != 'x';
QUERY PLAN
-------------------------------------------------------------------------------
Update on public.t1 (cost=0.01..100995.96 rows=2470 width=43)
-> Result (cost=0.01..100995.96 rows=2470 width=43)
Output: $1, ((SubPlan 1 (returns $1))), t1.ctid
One-Time Filter: (CURRENT_USER <> 'x'::name)
-> Seq Scan on public.t1 (cost=0.01..100995.96 rows=2470 width=43)
Output: $1, (SubPlan 1 (returns $1)), t1.ctid
SubPlan 1 (returns $1)
-> Seq Scan on public.t2 (cost=0.00..40.88 rows=12 width=5)
Output: t2.b
Filter: ((t2.b)::text = (t1.a)::text)
(10 rows)
The implementation of multiassignments assumes that Params referencing
the output of a multiassignment subplan will appear in the same targetlist
as the SubPlan node for that multiassignment. Here, that's valid in
the SeqScan's tlist, but the Result has another occurrence of the same
Param, and that one is misplaced. That's because fix_upper_expr_mutator
is doing things in the wrong order, causing it to emit a naked Param
where it should emit a Var referencing the Param output from the lower
plan node.
I think it's just accidental that v11 and HEAD don't show the same
problem. We've refactored where the main tlist evaluation happens:
regression=# explain verbose UPDATE t1
SET (a) = (SELECT b FROM t2 WHERE t2.b = t1.a)
WHERE current_user != 'x';
QUERY PLAN
--------------------------------------------------------------------------
Update on public.t1 (cost=0.01..100995.96 rows=2470 width=43)
-> Result (cost=0.01..100995.96 rows=2470 width=43)
Output: $1, (SubPlan 1 (returns $1)), t1.ctid
One-Time Filter: (CURRENT_USER <> 'x'::name)
-> Seq Scan on public.t1 (cost=0.01..34.70 rows=2470 width=11)
Output: t1.a, t1.ctid
SubPlan 1 (returns $1)
-> Seq Scan on public.t2 (cost=0.00..40.88 rows=12 width=5)
Output: t2.b
Filter: ((t2.b)::text = (t1.a)::text)
(10 rows)
but if there were somehow another plan node in between, it'd be just
as broken. The same is true of 9.5.
(Note: in these examples, I replaced your constant-true WHERE clause
with "current_user != 'x'", because v11+ are smart enough to fold the
NOT IN to constant true and then not generate a Result at all,
obscuring the issue.)
regards, tom lane
