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

"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