From cpc@cybees.com Tue Oct 25 07:24:06 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 83349DADCB for ; Tue, 25 Oct 2005 04:24:06 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26255-02 for ; Tue, 25 Oct 2005 07:24:02 +0000 (GMT) Received: from mailer.eglobalreach.net (cybees.com [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id E55AFDADC3 for ; Tue, 25 Oct 2005 04:24:02 -0300 (ADT) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9P7O3U31211 for ; Tue, 25 Oct 2005 15:24:03 +0800 From: "Christian Paul B. Cosinas" To: Subject: Temporary Table Date: Tue, 25 Oct 2005 07:28:15 -0000 Message-ID: <001201c5d935$aa544270$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0013_01C5D935.AA55C910" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXWVwx9TKFORE3pS6yM9N43/8G7SwB5T9QAAAEl7OAAPS8/0A== X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.68 required=5 tests=[AWL=-0.000, FORGED_RCVD_HELO=0.05, HTML_MESSAGE=0.001, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/350 X-Sequence-Number: 15118 This is a multi-part message in MIME format. ------=_NextPart_000_0013_01C5D935.AA55C910 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ------=_NextPart_000_0013_01C5D935.AA55C910 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Does Creating = Temporary table in a function and NOT dropping them affects the performance of the = database?

 



I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html ------=_NextPart_000_0013_01C5D935.AA55C910-- From cpc@cybees.com Wed Oct 26 02:11:38 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9303CDAEAE for ; Tue, 25 Oct 2005 23:11:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48995-02 for ; Wed, 26 Oct 2005 02:11:33 +0000 (GMT) Received: from mailer.eglobalreach.net (gc4-mx.gc4.com [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 031D1DAECD for ; Tue, 25 Oct 2005 23:11:34 -0300 (ADT) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9Q2BPx23751; Wed, 26 Oct 2005 10:11:25 +0800 From: "Christian Paul B. Cosinas" To: Cc: "'Alvaro Nunes Melo'" Subject: Re: Temporary Table Date: Wed, 26 Oct 2005 02:15:37 -0000 Message-ID: <004a01c5d9d3$28199820$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 thread-index: AcXZUvDG9UlwHeZWTqupmuaYzCrPXwAf4BSQ In-Reply-To: <435E0F9B.3050101@atua.com.br> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.679 required=5 tests=[AWL=0.000, FORGED_RCVD_HELO=0.05, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/377 X-Sequence-Number: 15145 I am creating a temporary table in every function that I execute. Which I think is bout 100,000 temporary tables a day. What is the command for vacuuming these 3 tables? Also I read about the auto vacuum of postgresql. How can I execute this auto vacuum or the settings in the configuration? -----Original Message----- From: Alvaro Nunes Melo [mailto:al_nunes@atua.com.br] Sent: Tuesday, October 25, 2005 10:58 AM To: Christian Paul B. Cosinas Subject: Re: [PERFORM] Temporary Table Hi Christian, Christian Paul B. Cosinas wrote: > Does Creating Temporary table in a function and NOT dropping them > affects the performance of the database? > I believe it will depend on how many temporary tables you will create in a daily basis. We had a performance problem caused by them, and by not monitoring properly the database size. The pg_attribite, pg_class and pg_depend tables grow a lot. When I found out that this was the problem I saw some messages in the list archieve, and now the overall performance is great. What I do is daily run VACUUM FULL and REINDEX in this three tables. Alvaro I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html From alvherre@commandprompt.com Mon Nov 7 11:07:22 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B3EAED6807 for ; Mon, 7 Nov 2005 07:07:22 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02662-08 for ; Mon, 7 Nov 2005 11:07:19 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx2.surnet.cl (mx2.surnet.cl [216.155.73.181]) by svr1.postgresql.org (Postfix) with ESMTP id 6F176D7C81 for ; Mon, 7 Nov 2005 07:07:19 -0400 (AST) Received: from unknown (HELO cluster.surnet.cl) ([216.155.73.165]) by mx2.surnet.cl with ESMTP; 07 Nov 2005 08:06:30 -0300 X-IronPort-AV: i="3.97,299,1125892800"; d="scan'208"; a="23875065:sNHT57897578" Received: from alvh.no-ip.org (216.155.78.23) by cluster.surnet.cl (7.0.043) (authenticated as alvherre@surnet.cl) id 43501597002D8D7D; Mon, 7 Nov 2005 08:07:17 -0300 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 174F5C2D450; Mon, 7 Nov 2005 08:07:20 -0300 (CLST) Date: Mon, 7 Nov 2005 08:07:20 -0300 From: Alvaro Herrera To: "Christian Paul B. Cosinas" Cc: pgsql-performance@postgresql.org Subject: Re: Temporary Table Message-ID: <20051107110719.GA7012@surnet.cl> Mail-Followup-To: "Christian Paul B. Cosinas" , pgsql-performance@postgresql.org References: <004101c5e3b0$ee534130$1e21100a@ghwk02002147> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <004101c5e3b0$ee534130$1e21100a@ghwk02002147> User-Agent: Mutt/1.5.10i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=2.006 required=5 tests=[AWL=-0.240, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44, RCVD_IN_NJABL_PROXY=0.327] X-Spam-Score: 2.006 X-Spam-Level: ** X-Archive-Number: 200511/118 X-Sequence-Number: 15375 Christian Paul B. Cosinas wrote: > Does Creating Temporary table in a function and NOT dropping them affects > the performance of the database? The system will drop it automatically, so it shouldn't affect. What _could_ be affecting you if you execute that function a lot, is accumulated bloat in pg_class, pg_attribute, or other system catalogs. You may want to make sure these are vacuumed often. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support From cpc@cybees.com Mon Nov 7 07:31:32 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C7995D7AF6 for ; Mon, 7 Nov 2005 03:31:32 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22918-08 for ; Mon, 7 Nov 2005 07:31:30 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mailer.eglobalreach.net (mail.heliustech.net [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id B9E61D7028 for ; Mon, 7 Nov 2005 03:31:30 -0400 (AST) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id jA77VUm20602 for ; Mon, 7 Nov 2005 15:31:30 +0800 From: "Christian Paul B. Cosinas" To: Subject: Temporary Table Date: Mon, 7 Nov 2005 15:35:49 -0000 Message-ID: <004101c5e3b0$ee534130$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0042_01C5E3B0.EE534130" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXWVwx9TKFORE3pS6yM9N43/8G7SwB5T9QAAAEl7OAC3AFREA== X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.748 required=5 tests=[AWL=-0.845, DATE_IN_FUTURE_06_12=1.498, HTML_MESSAGE=0.001, URIBL_SBL=1.094] X-Spam-Score: 1.748 X-Spam-Level: * X-Archive-Number: 200511/113 X-Sequence-Number: 15370 This is a multi-part message in MIME format. ------=_NextPart_000_0042_01C5E3B0.EE534130 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ------=_NextPart_000_0042_01C5E3B0.EE534130 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Does Creating = Temporary table in a function and NOT dropping them affects the performance of the database?

 



I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html



I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html ------=_NextPart_000_0042_01C5E3B0.EE534130-- From ralph.mason@telogis.com Mon Nov 7 21:40:14 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C8AC2D6892 for ; Mon, 7 Nov 2005 17:40:14 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69119-05 for ; Mon, 7 Nov 2005 21:40:12 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from nz.telogis.com (unknown [203.98.10.169]) by svr1.postgresql.org (Postfix) with ESMTP id 770E5D6834 for ; Mon, 7 Nov 2005 17:40:13 -0400 (AST) Received: from [192.168.3.1] ([192.168.3.1]) by nz.telogis.com with esmtp; Tue, 08 Nov 2005 10:40:08 +1300 id 006116D6.436FC9BD.00003B66 Message-ID: <436FC9B1.4000906@telogis.com> Date: Tue, 08 Nov 2005 10:40:01 +1300 From: Ralph Mason User-Agent: Thunderbird 1.4 (Windows/20050908) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Temporary Table References: <004101c5e3b0$ee534130$1e21100a@ghwk02002147> <20051107110719.GA7012@surnet.cl> In-Reply-To: <20051107110719.GA7012@surnet.cl> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/125 X-Sequence-Number: 15382 Alvaro Herrera wrote: > Christian Paul B. Cosinas wrote: > >> Does Creating Temporary table in a function and NOT dropping them affects >> the performance of the database? >> > > The system will drop it automatically, so it shouldn't affect. > > What _could_ be affecting you if you execute that function a lot, is > accumulated bloat in pg_class, pg_attribute, or other system catalogs. > You may want to make sure these are vacuumed often. > > The answer in my experience is a very loud YES YES YES If you use lots of temporary tables you will grow and dirty your system catalogs, so you need to be vacuuming them regularly also (pg_call, pg_attribute) Otherwise your db will slow to a crawl after a while. Ralph From jd@commandprompt.com Tue Nov 8 02:10:42 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EF067DAD68 for ; Mon, 7 Nov 2005 22:10:42 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68577-09 for ; Tue, 8 Nov 2005 02:10:39 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id 6DCF5DAA49 for ; Mon, 7 Nov 2005 22:10:41 -0400 (AST) Received: from [192.168.1.105] (clbb-248.saw.net [64.146.135.248]) (authenticated bits=0) by hosting.commandprompt.com (8.13.4/8.13.4) with ESMTP id jA82680h014921; Mon, 7 Nov 2005 18:06:13 -0800 Message-ID: <4370091E.8060700@commandprompt.com> Date: Mon, 07 Nov 2005 18:10:38 -0800 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Christian Paul B. Cosinas" Cc: "'Alvaro Nunes Melo'" , pgsql-performance@postgresql.org Subject: Re: Temporary Table References: <008301c5e44c$70eee900$1e21100a@ghwk02002147> In-Reply-To: <008301c5e44c$70eee900$1e21100a@ghwk02002147> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (hosting.commandprompt.com [192.168.1.101]); Mon, 07 Nov 2005 18:06:13 -0800 (PST) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.567 required=5 tests=[AWL=-0.527, URIBL_SBL=1.094] X-Spam-Score: 0.567 X-Spam-Level: X-Archive-Number: 200511/128 X-Sequence-Number: 15385 Christian Paul B. Cosinas wrote: > I try to run this command in my linux server. > VACUUM FULL pg_class; > VACUUM FULL pg_attribute; > VACUUM FULL pg_depend; > > But it give me the following error: > -bash: VACUUM: command not found That needs to be run from psql ... > > > > > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org From chriskl@familyhealth.com.au Tue Nov 8 02:13:52 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7074DD72B6 for ; Mon, 7 Nov 2005 22:13:52 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71052-10 for ; Tue, 8 Nov 2005 02:13:49 +0000 (GMT) X-Greylist: delayed 21:36:12.623567 by SQLgrey- Received: from houston.familyhealth.com.au (houston.au.fhnetwork.com [203.22.197.21]) by svr1.postgresql.org (Postfix) with ESMTP id DC879D6E2C for ; Mon, 7 Nov 2005 22:13:50 -0400 (AST) Received: from houston.familyhealth.com.au (localhost [127.0.0.1]) by houston.familyhealth.com.au (Postfix) with ESMTP id 5F59525070; Tue, 8 Nov 2005 10:13:52 +0800 (WST) Received: from [127.0.0.1] (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (Postfix) with ESMTP id 5E70824FFA; Tue, 8 Nov 2005 10:13:51 +0800 (WST) Message-ID: <43700A15.9000506@familyhealth.com.au> Date: Tue, 08 Nov 2005 10:14:45 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Christian Paul B. Cosinas" Cc: 'Alvaro Nunes Melo' , pgsql-performance@postgresql.org Subject: Re: Temporary Table References: <008301c5e44c$70eee900$1e21100a@ghwk02002147> In-Reply-To: <008301c5e44c$70eee900$1e21100a@ghwk02002147> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-familyhealth-MailScanner-Information: Please contact the ISP for more information X-familyhealth-MailScanner: Found to be clean X-familyhealth-MailScanner-From: chriskl@familyhealth.com.au X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.581 required=5 tests=[AWL=-0.513, URIBL_SBL=1.094] X-Spam-Score: 0.581 X-Spam-Level: X-Archive-Number: 200511/129 X-Sequence-Number: 15386 Ummm...they're SQL commands. Run them in PostgreSQL, not on the unix command line... Christian Paul B. Cosinas wrote: > I try to run this command in my linux server. > VACUUM FULL pg_class; > VACUUM FULL pg_attribute; > VACUUM FULL pg_depend; > > But it give me the following error: > -bash: VACUUM: command not found > > > > > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org From chriskl@familyhealth.com.au Tue Nov 8 02:14:14 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BDCD0DACE8 for ; Mon, 7 Nov 2005 22:14:14 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70702-07 for ; Tue, 8 Nov 2005 02:14:11 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from houston.familyhealth.com.au (houston.au.fhnetwork.com [203.22.197.21]) by svr1.postgresql.org (Postfix) with ESMTP id F0EB6D9A17 for ; Mon, 7 Nov 2005 22:14:12 -0400 (AST) Received: from houston.familyhealth.com.au (localhost [127.0.0.1]) by houston.familyhealth.com.au (Postfix) with ESMTP id 6344E25092; Tue, 8 Nov 2005 10:14:16 +0800 (WST) Received: from [127.0.0.1] (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (Postfix) with ESMTP id 80DBB25091; Tue, 8 Nov 2005 10:14:15 +0800 (WST) Message-ID: <43700A2E.8060008@familyhealth.com.au> Date: Tue, 08 Nov 2005 10:15:10 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Christian Paul B. Cosinas" Cc: 'Alvaro Nunes Melo' , pgsql-performance@postgresql.org Subject: Re: Temporary Table References: <008201c5e44b$c0cdf390$1e21100a@ghwk02002147> In-Reply-To: <008201c5e44b$c0cdf390$1e21100a@ghwk02002147> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-familyhealth-MailScanner-Information: Please contact the ISP for more information X-familyhealth-MailScanner: Found to be clean X-familyhealth-MailScanner-From: chriskl@familyhealth.com.au X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.035 required=5 tests=[AWL=0.035] X-Spam-Score: 0.035 X-Spam-Level: X-Archive-Number: 200511/130 X-Sequence-Number: 15387 > In what directory in my linux server will I find these 3 tables? Directory? They're tables in your database... From jeff@frostconsultingllc.com Tue Nov 8 02:29:38 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1F78ED9A17 for ; Mon, 7 Nov 2005 22:29:38 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81356-02 for ; Tue, 8 Nov 2005 02:29:30 +0000 (GMT) X-Greylist: delayed 00:06:38.782794 by SQLgrey- Received: from smtp111.sbc.mail.mud.yahoo.com (smtp111.sbc.mail.mud.yahoo.com [68.142.198.210]) by svr1.postgresql.org (Postfix) with SMTP id 484D8D6892 for ; Mon, 7 Nov 2005 22:29:32 -0400 (AST) Received: (qmail 99335 invoked from network); 8 Nov 2005 02:22:55 -0000 Received: from unknown (HELO discord.dyndns.org) (jeffroe996@sbcglobal.net@69.227.55.89 with plain) by smtp111.sbc.mail.mud.yahoo.com with SMTP; 8 Nov 2005 02:22:55 -0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by discord.dyndns.org (8.13.4/8.13.4) with ESMTP id jA82MsUC018935; Mon, 7 Nov 2005 18:22:54 -0800 Date: Mon, 7 Nov 2005 18:22:54 -0800 (PST) From: Jeff Frost X-X-Sender: jeff@discord.dyndns.org To: "Christian Paul B. Cosinas" Cc: "'Joshua D. Drake'" , "'Alvaro Nunes Melo'" , pgsql-performance@postgresql.org Subject: Re: Temporary Table In-Reply-To: <008401c5e44e$42600c20$1e21100a@ghwk02002147> Message-ID: References: <008401c5e44e$42600c20$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.572 required=5 tests=[AWL=-0.522, URIBL_SBL=1.094] X-Spam-Score: 0.572 X-Spam-Level: X-Archive-Number: 200511/133 X-Sequence-Number: 15390 You can use the vacuumdb external command. Here's an example: vacuumdb --full --analyze --table mytablename mydbname On Tue, 8 Nov 2005, Christian Paul B. Cosinas wrote: > But How Can I put this in the Cron of my Linux Server? > I really don't have an idea :) > What I want to do is to loop around all the databases in my server and > execute the vacuum of these 3 tables in each tables. > > -----Original Message----- > From: Joshua D. Drake [mailto:jd@commandprompt.com] > Sent: Tuesday, November 08, 2005 2:11 AM > To: Christian Paul B. Cosinas > Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Temporary Table > > Christian Paul B. Cosinas wrote: >> I try to run this command in my linux server. >> VACUUM FULL pg_class; >> VACUUM FULL pg_attribute; >> VACUUM FULL pg_depend; >> >> But it give me the following error: >> -bash: VACUUM: command not found > > That needs to be run from psql ... > >> >> >> >> >> >> I choose Polesoft Lockspam to fight spam, and you? >> http://www.polesoft.com/refer.html >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org > > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Jeff Frost, Owner Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 From chriskl@familyhealth.com.au Tue Nov 8 02:23:27 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 72CF9D72B6 for ; Mon, 7 Nov 2005 22:23:27 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78079-07 for ; Tue, 8 Nov 2005 02:23:23 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from houston.familyhealth.com.au (houston.au.fhnetwork.com [203.22.197.21]) by svr1.postgresql.org (Postfix) with ESMTP id AF2ECD6E2C for ; Mon, 7 Nov 2005 22:23:25 -0400 (AST) Received: from houston.familyhealth.com.au (localhost [127.0.0.1]) by houston.familyhealth.com.au (Postfix) with ESMTP id 8AADF25094; Tue, 8 Nov 2005 10:23:29 +0800 (WST) Received: from [127.0.0.1] (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (Postfix) with ESMTP id DE48025087; Tue, 8 Nov 2005 10:23:25 +0800 (WST) Message-ID: <43700C54.2010701@familyhealth.com.au> Date: Tue, 08 Nov 2005 10:24:20 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Christian Paul B. Cosinas" Cc: "'Joshua D. Drake'" , 'Alvaro Nunes Melo' , pgsql-performance@postgresql.org Subject: Re: Temporary Table References: <008401c5e44e$42600c20$1e21100a@ghwk02002147> In-Reply-To: <008401c5e44e$42600c20$1e21100a@ghwk02002147> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-familyhealth-MailScanner-Information: Please contact the ISP for more information X-familyhealth-MailScanner: Found to be clean X-familyhealth-MailScanner-From: chriskl@familyhealth.com.au X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.583 required=5 tests=[AWL=-0.511, URIBL_SBL=1.094] X-Spam-Score: 0.583 X-Spam-Level: X-Archive-Number: 200511/132 X-Sequence-Number: 15389 Or you could just run the 'vacuumdb' utility... Put something like this in cron: # Vacuum full local pgsql database 30 * * * * postgres vacuumdb -a -q -z You really should read the manual. Chris Christian Paul B. Cosinas wrote: > I see. > > But How Can I put this in the Cron of my Linux Server? > I really don't have an idea :) > What I want to do is to loop around all the databases in my server and > execute the vacuum of these 3 tables in each tables. > > -----Original Message----- > From: Joshua D. Drake [mailto:jd@commandprompt.com] > Sent: Tuesday, November 08, 2005 2:11 AM > To: Christian Paul B. Cosinas > Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Temporary Table > > Christian Paul B. Cosinas wrote: > >>I try to run this command in my linux server. >>VACUUM FULL pg_class; >>VACUUM FULL pg_attribute; >>VACUUM FULL pg_depend; >> >>But it give me the following error: >> -bash: VACUUM: command not found > > > That needs to be run from psql ... > > >> >> >> >> >>I choose Polesoft Lockspam to fight spam, and you? >>http://www.polesoft.com/refer.html >> >> >>---------------------------(end of >>broadcast)--------------------------- >>TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org > > > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster From andrew@catalyst.net.nz Tue Nov 8 03:02:39 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 86F79DB127 for ; Mon, 7 Nov 2005 23:02:39 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91812-07 for ; Tue, 8 Nov 2005 03:02:26 +0000 (GMT) X-Greylist: delayed 00:26:46.548572 by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by svr1.postgresql.org (Postfix) with ESMTP id F24B6DB1D9 for ; Mon, 7 Nov 2005 23:01:55 -0400 (AST) Received: from mail1.catalyst.net.nz (godel.catalyst.net.nz [202.78.240.40]) by svr2.postgresql.org (Postfix) with ESMTP id 7B336F0DE4 for ; Tue, 8 Nov 2005 02:35:33 +0000 (GMT) Received: from 218-101-8-114.xtreme.net.nz ([218.101.8.114] helo=ubu.mcmillan.net.nz) by mail1.catalyst.net.nz with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA:32) (Exim 4.50) id 1EZJKS-00037g-VN; Tue, 08 Nov 2005 15:35:21 +1300 Received: by ubu.mcmillan.net.nz (Postfix, from userid 1000) id 96B396F0A0A; Tue, 8 Nov 2005 15:35:20 +1300 (NZDT) Subject: Re: Temporary Table From: Andrew McMillan To: "Christian Paul B. Cosinas" Cc: "'Joshua D. Drake'" , 'Alvaro Nunes Melo' , pgsql-performance@postgresql.org In-Reply-To: <008401c5e44e$42600c20$1e21100a@ghwk02002147> References: <008401c5e44e$42600c20$1e21100a@ghwk02002147> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-bSOP61PawZ62D/NruzET" Organization: Catalyst .Net Ltd Date: Tue, 08 Nov 2005 15:35:20 +1300 Message-Id: <1131417320.5555.10.camel@ubu.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.5.1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/135 X-Sequence-Number: 15392 --=-bSOP61PawZ62D/NruzET Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Tue, 2005-11-08 at 10:22 +0000, Christian Paul B. Cosinas wrote: > I see. >=20 > But How Can I put this in the Cron of my Linux Server? > I really don't have an idea :) > What I want to do is to loop around all the databases in my server and > execute the vacuum of these 3 tables in each tables. I usually write a small shell script something like: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D #!/bin/sh psql somedatabase <; Mon, 7 Nov 2005 21:59:52 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72938-01 for ; Tue, 8 Nov 2005 01:59:49 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mailer.eglobalreach.net (mail.heliustech.net [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 0CB97D9E41 for ; Mon, 7 Nov 2005 21:59:49 -0400 (AST) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id jA81xjt17399; Tue, 8 Nov 2005 09:59:47 +0800 From: "Christian Paul B. Cosinas" To: "'Alvaro Nunes Melo'" , Subject: Re: Temporary Table Date: Tue, 8 Nov 2005 10:04:01 -0000 Message-ID: <008201c5e44b$c0cdf390$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXaGuESJlWsZB6mToaTb7utC7xN7gKMJJlQ In-Reply-To: <435F5F1C.1010703@atua.com.br> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.83 required=5 tests=[AWL=-0.762, DATE_IN_FUTURE_06_12=1.498, URIBL_SBL=1.094] X-Spam-Score: 1.83 X-Spam-Level: * X-Archive-Number: 200511/126 X-Sequence-Number: 15383 In what directory in my linux server will I find these 3 tables? -----Original Message----- From: Alvaro Nunes Melo [mailto:al_nunes@atua.com.br] Sent: Wednesday, October 26, 2005 10:49 AM To: Christian Paul B. Cosinas Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: >I am creating a temporary table in every function that I execute. >Which I think is bout 100,000 temporary tables a day. > > I think that a lot. ;) >What is the command for vacuuming these 3 tables? > > VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; I'm using this ones. Before using them, take a look in the size that this tables are using in your HD, and compare to what you get after running this commands. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html From cpc@cybees.com Tue Nov 8 02:04:46 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1F33ED9AFA for ; Mon, 7 Nov 2005 22:04:46 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72156-07 for ; Tue, 8 Nov 2005 02:04:42 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mailer.eglobalreach.net (mail.heliustech.net [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 45B2CD8055 for ; Mon, 7 Nov 2005 22:04:43 -0400 (AST) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id jA824g417794; Tue, 8 Nov 2005 10:04:43 +0800 From: "Christian Paul B. Cosinas" To: "'Alvaro Nunes Melo'" , Subject: Re: Temporary Table Date: Tue, 8 Nov 2005 10:09:00 -0000 Message-ID: <008301c5e44c$70eee900$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXaGuESJlWsZB6mToaTb7utC7xN7gKMUdQw In-Reply-To: <435F5F1C.1010703@atua.com.br> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.853 required=5 tests=[AWL=-0.739, DATE_IN_FUTURE_06_12=1.498, URIBL_SBL=1.094] X-Spam-Score: 1.853 X-Spam-Level: * X-Archive-Number: 200511/127 X-Sequence-Number: 15384 I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html From cpc@cybees.com Tue Nov 8 02:17:49 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A6782DAA49 for ; Mon, 7 Nov 2005 22:17:49 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76871-04 for ; Tue, 8 Nov 2005 02:17:46 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mailer.eglobalreach.net (mail.heliustech.net [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 969E4DA48F for ; Mon, 7 Nov 2005 22:17:47 -0400 (AST) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id jA82Hi927629; Tue, 8 Nov 2005 10:17:44 +0800 From: "Christian Paul B. Cosinas" To: "'Joshua D. Drake'" Cc: "'Alvaro Nunes Melo'" , Subject: Re: Temporary Table Date: Tue, 8 Nov 2005 10:22:01 -0000 Message-ID: <008401c5e44e$42600c20$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXkCZ/+hrHRUvOURJiU8IRxQspFigARG0Sw In-Reply-To: <4370091E.8060700@commandprompt.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.917 required=5 tests=[AWL=-0.675, DATE_IN_FUTURE_06_12=1.498, URIBL_SBL=1.094] X-Spam-Score: 1.917 X-Spam-Level: * X-Archive-Number: 200511/131 X-Sequence-Number: 15388 I see. But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: Tuesday, November 08, 2005 2:11 AM To: Christian Paul B. Cosinas Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: > I try to run this command in my linux server. > VACUUM FULL pg_class; > VACUUM FULL pg_attribute; > VACUUM FULL pg_depend; > > But it give me the following error: > -bash: VACUUM: command not found That needs to be run from psql ... > > > > > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html From franklin.haut@gmail.com Fri Jun 23 21:52:26 2006 X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 742579FB0D1 for ; Fri, 23 Jun 2006 18:52:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 20439-02 for ; Fri, 23 Jun 2006 18:52:19 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.203]) by postgresql.org (Postfix) with ESMTP id BA3509FA60B for ; Fri, 23 Jun 2006 18:52:20 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id i31so564396wxd for ; Fri, 23 Jun 2006 14:52:18 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:x-mimeole; b=fJjBY9uo6cPvvb2JRRxWk5j0m8ZNBZuTZ9pEQLRRqsvMv4+Un2oL3tiam9je7OPp3dz0Ap5obtigWJVrGV5FU5Moe3y3meIZaADUj7g8PlLaZWJ8vYRKPpPjW4u5Ho47YuJLapLdNiNIV+H1O8JMViMG7MovBjnrOchJ+M+JMxw= Received: by 10.70.44.11 with SMTP id r11mr5363542wxr; Fri, 23 Jun 2006 14:52:18 -0700 (PDT) Received: from franklin ( [201.2.219.138]) by mx.gmail.com with ESMTP id h34sm3028600wxd.2006.06.23.14.52.15; Fri, 23 Jun 2006 14:52:17 -0700 (PDT) From: "Franklin Haut" To: Subject: Temporary table Date: Fri, 23 Jun 2006 18:58:22 -0300 Message-ID: <000001c69710$264fe940$8adb02c9@franklin> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcaWJ6kB91I7F+1CQfamxokjjD/fiQAycwtQAAeoJXA= X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.504 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/466 X-Sequence-Number: 19823 Hello, I=B4m have some problems with a temporary table, i need create a table, = insert some values, make a select and at end of transaction the table must = droped, but after i created a table there not more exist, is this normal ? How to reproduce : CREATE TEMP TABLE cademp ( codemp INTEGER, codfil INTEGER, nomemp varchar(50) ) ON COMMIT DROP; INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); =09 Select * from cademp; In this case, the table cademp doesn=B4t exist at the first insert, in = the same transaction. Tks, Franklin From tgl@sss.pgh.pa.us Fri Jun 23 22:04:41 2006 X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id DBA659FA60C for ; Fri, 23 Jun 2006 19:04:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 20400-06 for ; Fri, 23 Jun 2006 19:04:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id DDEFC9F9AD3 for ; Fri, 23 Jun 2006 19:04:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k5NM4SGY029400; Fri, 23 Jun 2006 18:04:28 -0400 (EDT) To: "Franklin Haut" cc: pgsql-performance@postgresql.org Subject: Re: Temporary table In-reply-to: <000001c69710$264fe940$8adb02c9@franklin> References: <000001c69710$264fe940$8adb02c9@franklin> Comments: In-reply-to "Franklin Haut" message dated "Fri, 23 Jun 2006 18:58:22 -0300" Date: Fri, 23 Jun 2006 18:04:28 -0400 Message-ID: <29399.1151100268@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/467 X-Sequence-Number: 19824 "Franklin Haut" writes: > How to reproduce : > CREATE TEMP TABLE cademp ( > codemp INTEGER, > codfil INTEGER, > nomemp varchar(50) > ) ON COMMIT DROP; > INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); > INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); > Select * from cademp; You need a BEGIN/COMMIT around that, or else rethink using ON COMMIT DROP. As is, the temp table goes away instantly when the CREATE commits. regards, tom lane From ler@lerctr.org Fri Jun 23 22:08:24 2006 X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B6C5F9FA30D for ; Fri, 23 Jun 2006 19:08:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 20972-05 for ; Fri, 23 Jun 2006 19:08:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from thebighonker.lerctr.org (thebighonker.lerctr.org [192.147.25.65]) by postgresql.org (Postfix) with ESMTP id C87DA9F9AD3 for ; Fri, 23 Jun 2006 19:08:11 -0300 (ADT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=lerami; d=lerctr.org; b=SIYrbhWl4xhSaNYjUPtsxg1ZIEQ/oE5j5f9qoyr327tU1Ugx/VDZnAgH1IJc0lzrrucKmqS1thinSfpZPK+00T/SVIsCUFrRxJhESuyb1fFqMyypldB0ZF+L0LZsNDZ35+oIlSOal9lxDy9X7NpI3Yd6kDHRmCNUO85alFgAxfE=; Received: from 64-132-13-2.static.twtelecom.net ([64.132.13.2]:53786 helo=LROSENMAC8010P) by thebighonker.lerctr.org with esmtpsa (TLSv1:RC4-MD5:128) (Exim 4.62 (FreeBSD)) (envelope-from ) id 1Fttos-000F4t-4V; Fri, 23 Jun 2006 17:08:08 -0500 From: "Larry Rosenman" To: "'Franklin Haut'" , Subject: Re: Temporary table Date: Fri, 23 Jun 2006 17:08:05 -0500 Message-ID: <039001c69711$81058a60$0202fea9@aus.pervasive.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook 11 In-Reply-To: <000001c69710$264fe940$8adb02c9@franklin> Thread-Index: AcaWJ6kB91I7F+1CQfamxokjjD/fiQAycwtQAAeoJXAAAFL0AA== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-LERCTR-Spam-Score: -4.4 (----) X-LERCTR-Spam-Report: (-4.4 points, 5.0 required) ALL_TRUSTED=-1.8 BAYES_00=-2.599 DomainKey-Status: no signature X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/468 X-Sequence-Number: 19825 Franklin Haut wrote: > Hello, >=20 > I=B4m have some problems with a temporary table, i need create a = table, > insert some values, make a select and at end of transaction the table > must droped, but after i created a table there not more exist, is > this normal ?=20 >=20 > How to reproduce : >=20 >=20 > CREATE TEMP TABLE cademp ( > codemp INTEGER, > codfil INTEGER, > nomemp varchar(50) > ) ON COMMIT DROP; >=20 > INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); > INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); >=20 > Select * from cademp; >=20 >=20 >=20 > In this case, the table cademp doesn=B4t exist at the first insert, in > the same transaction. >=20 It is NOT the same transaction. By default, each STATEMENT is it's own transaction. Stick a BEGIN; before the create table, and a commit; after the select. Larry Rosenman >=20 >=20 >=20 > Tks, >=20 > Franklin >=20 >=20 > ---------------------------(end of > broadcast)--------------------------- TIP 6: explain analyze is your > friend=20 --=20 Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893