Re: what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL - Mailing list pgsql-hackers

Added to TODO:
       o Allow GLOBAL temporary tables to exist as empty by default in         all sessions
         http://archives.postgresql.org/pgsql-hackers/2007-07/msg00006.php


---------------------------------------------------------------------------

Gregory Stark wrote:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 
> > 2007/7/4, Bruce Momjian <bruce@momjian.us>:
> >> > The use case is any system that uses temp tables in an OLTP setting,
> >> > which certainly isn't uncommon. The problem is that today (and as well
> >> > with a global temp table that is still writing to the catalogs) is that
> >> > every OLTP operation that creates or drops a temp table is doing DDL.
> >> > At best, that leads to a lot of catalog bloat. Right now, it appears to
> >> > also expose some race conditions (we've got a customer that's been bit
> >> > by this and we've been able to reproduce some odd behavior in the lab).
> >>
> >> The solution is to fix the bloat, not add a work-around.
> 
> The bloat is a direct consequence of performing DDL in the midst of an OLTP
> transaction. And it's not the only consequence either. Off the top of my head
> trying to do DDL in an OLTP environment will cause OID inflation, locking
> issues, catcache problems, unnecessary prepared query replans, and the list
> goes on, what happens to views defined on the temporary tables? Foreign key
> references to the temporary tables?
> 
> You've got it backwards: addressing the artificially imposed requirement to do
> DDL to create new tables for what should be purely DML operations is fixing
> the root problem, not a work-around. What would be a work-around is trying to
> deal with the consequences as they come up.
> 
> > Catalog bloat is one unwanted effect. Second is different behave of
> > temp tables  than other mayor rdbms, and uncomfortable work with temp
> > tables in stored procedures. Third argument for implementation of
> > global temp tables is full support of ANSI SQL,
> 
> I think the ANSI concept of temporary tables which are defined once but give
> you a fresh empty work-space for each transaction only makes sense if you're
> thinking in terms of an OLTP environment. Otherwise you would just go ahead
> and do the DDL to create new tables for each query and not worry about the
> down-sides.
> 
> The advantages of the ANSI temporary tables are all things you would worry
> about in an OLTP environment but not a data warehousing environment:
> 
> 1) Overhead to perform DDL
> 
> 2) Replanning overhead
> 
> 3) Security issues of doing DDL at run-time
> 
> 4) Difficulty structuring code when multiple procedures need the same
>    temporary tables but the procedures may be called in different orders for
>    different jobs and need different sets of tables.
> 
> -- 
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +