X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 007E852871 for ; Fri, 10 Jun 2005 14:36:18 -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 95299-07 for ; Fri, 10 Jun 2005 17:36:10 +0000 (GMT) Received: from polaris.pinpointresearch.com (66-7-238-130.cust.telepacific.net [66.7.238.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1A16852951 for ; Fri, 10 Jun 2005 14:36:08 -0300 (ADT) Received: from betelgeuse.pinpointresearch.com (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id B6002103E5; Fri, 10 Jun 2005 10:36:09 -0700 (PDT) From: Steve Crawford Organization: Pinpoint Research To: "Lee Wu" , Subject: Re: select * and save into a text file failed Date: Fri, 10 Jun 2005 10:36:09 -0700 User-Agent: KMail/1.5.1 References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200506101036.09527.scrawford@pinpointresearch.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.072 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/81 X-Sequence-Number: 17836 On Friday 10 June 2005 10:03 am, Lee Wu wrote: > That is exactly what I did: > > \o a_lot_room_to_hold_my_result > select * from a_table > > either > 1. out of memory for query result > 2. killed > 3. crash PG > > "If you have a very large table you can exhaust memory on the > client side unless you are writing the data directly to a file." > How besides "\o" and pg_dump? > > We have 4G RAM, and shared_buffers= 32768, it is a dedicate test > box, while the table is about 2G. Something to try (I don't know if it will work because I don't know the exact internals of the PG libraries but it's something I've observed): set the output formatting to unaligned (\pset format unaligned). I had a situation once where selecting a few thousand rows exhausted my RAM. The problem was that one item in a text column was ~8k long which meant that every other row had 8k of padding. And this was the case for more than one column so the pager was having to swallow gobs of data most of which was blank space. I ran the output to a file and ran some tests. Switching to unaligned output dropped the size by orders of magnitude. Depending on the nature of your data this may help or do nothing at all. Of course you are going to hit RAM or disk limitations on any given machine. Cursors are there for your use and your project may require them. Cheers, Steve