Re: [HACKERS] Arrays broken on temp tables

Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> writes:
>> The bottom line here is that we mustn't generate separate RTEs for the
>> logical and physical table names.

> Are you saying a join on a temp table will not work?

Not at all; I'm saying that it's incorrect to generate a join for a
simple UPDATE. What we had was

UPDATE table SET arrayfield[sub] = val;

which is really implemented as (more or less)

UPDATE table SET arrayfield = ARRAYINSERT(arrayfield, sub, val);

which works fine as long as you apply the computation and update once
per tuple in the table (or once per tuple selected by WHERE, if there
is one). But for a temp table, what really gets emitted from the
parser is effectively like

UPDATE logtable SET arrayfield = arrayinsert(phytable.field,
sub, val)
FROM logtable phytable;

This is a Cartesian join, meaning that each tuple in
logtable-as-destination will be processed in combination with each tuple
in logtable-as-phytable. The particular case Kristofer reported
implements the join as a nested loop with logtable-as-destination as the
inner side of the join. So, each target tuple gets updated once with
an arrayfield value computed off each available source tuple --- and
when the dust settles, they've all got the value computed from the last
source tuple. That's why they're all the same in his bug report.

Adding a WHERE clause limits the damage, but the target tuples will all
still get the same value, if I'm visualizing the behavior correctly.
It's the wrong thing in any case; the very best you could hope for is
that the tuples all manage to get the right values after far more
processing than necessary. There should be no join for a simple UPDATE.

regards, tom lane