Thread

Re: [HACKERS] Arrays broken on temp tables

Bruce Momjian <maillist@candle.pha.pa.us> — 1999-11-11T05:28:23Z

> Bruce Momjian <maillist@candle.pha.pa.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.

OK, I see it now.  They are assigning the relname at this point using
the in-tuple relname, which is the physical name, not the logical name.

If I look at all calls to RelationGetRelationName(), I can see several
problem cases where the code it assigning the rel/refname based on the
in-tuple name.

Ideas?  Should i add reverse-lookup code in temprel.c, and make the
lookups happen for those cases?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026