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 230CE53205 for ; Thu, 24 Mar 2005 19:21:20 +0000 (GMT) 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 37567-02 for ; Thu, 24 Mar 2005 19:21:11 +0000 (GMT) Received: from joeconway.com (wsip-24-249-201-67.sd.sd.cox.net [24.249.201.67]) by svr1.postgresql.org (Postfix) with ESMTP id 43D3852A50 for ; Thu, 24 Mar 2005 19:21:09 +0000 (GMT) Received: from [206.19.64.3] (account jconway HELO [172.16.1.115]) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 2712790; Thu, 24 Mar 2005 12:20:48 -0800 Message-ID: <42431326.2010908@joeconway.com> Date: Thu, 24 Mar 2005 11:21:10 -0800 From: Joe Conway User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.7.3) Gecko/20040921 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Chris Hoover Cc: PostgreSQL Admin Subject: Re: Copying large tables with DBLink References: <42430E20.1010600@sermonaudio.com> In-Reply-To: <42430E20.1010600@sermonaudio.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.047 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/402 X-Sequence-Number: 16900 Chris Hoover wrote: > Has anyone had problems with memory exhaustion and dblink? We were > trying to use dblink to convert our databases to our new layout, and had > our test server lock up several times when trying to copy a table that > was significantly larger than our memory and swap. > Basically where were doing an insert into select * from > dblink('dbname=olddb','select * from large_table) as t_large_table(table > column listing); > > Does anyone know of a way around this? dblink just uses libpq, and libpq reads the entire result into memory. There is no direct way around that that I'm aware of. You could, however, use a cursor, and fetch/manipulate rows in more reasonably sized groups. HTH, Joe