"Pavel Stehule" <[EMAIL PROTECTED]> writes: > 2007/7/4, Bruce Momjian <[EMAIL PROTECTED]>: >> > 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