Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Arrays broken on temp tables

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>, Kristofer Munn <kmunn(at)munn(dot)com>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Arrays broken on temp tables
Date: 1999-11-30 02:57:34
Message-ID: 199911300257.VAA22996@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
I believe this is fixed was fixed by my RelationGetRelationName and
RelationGetPhysicalRelationName.


> 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
> 
> ************
> 


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

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 1999-11-30 03:11:06
Subject: Re: [HACKERS] Status of sql_help.h
Previous:From: Bruce MomjianDate: 1999-11-30 02:41:44
Subject: Re: [HACKERS] union and LIMIT problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group