Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 26DC39FB3F4 for ; Thu, 5 Jul 2007 03:33:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 16582-10 for ; Thu, 5 Jul 2007 03:33:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id 8758F9FB3D9 for ; Thu, 5 Jul 2007 03:33:49 -0300 (ADT) thread-index: Ace+znqaGPNreE+1SPWS29s1uTeImg== Received: from [192.168.0.5] ([85.211.38.252]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.3959); Thu, 5 Jul 2007 02:34:02 -0400 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4073 Subject: Re: what is difference between LOCAL and GLOBAL TEMPTABLES in PostgreSQL From: "Simon Riggs" To: "Gregory Stark" Cc: "Pavel Stehule" , "Bruce Momjian" , "Jim C. Nasby" , "Tom Lane" , "Alvaro Herrera" , "PostgreSQL-development" In-Reply-To: <87r6nnx3jk.fsf@oxford.xeocode.com> References: <20070703205746.GW85497@nasby.net> <200707040320.l643KRh09981@momjian.us> <162867790707040022g3d21beddsb6949e5f225be650@mail.gmail.com> <87r6nnx3jk.fsf@oxford.xeocode.com> Content-Type: text/plain; charset="iso-8859-1" Date: Thu, 05 Jul 2007 07:34:14 +0100 Message-ID: <1183617254.5398.83.camel@ebony.site> MIME-Version: 1.0 X-Mailer: Evolution 2.8.2 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 05 Jul 2007 06:34:03.0015 (UTC) FILETIME=[7A718970:01C7BECE] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/129 X-Sequence-Number: 105044 On Wed, 2007-07-04 at 22:27 +0100, Gregory Stark wrote: > "Pavel Stehule" writes: > > 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: IIRC there were similar problems with temp table usage at many DW sites using Teradata. The issue was about locking, specifically the access rights required. We might have that problem, or not, but the issues related to significant numbers of temp tables effect many types of application., not just OLTP. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com