Re: what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL

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(dot)stehule(at)gmail(dot)com> writes:
>
> > 2007/7/4, Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +