From revoohc@sermonaudio.com Thu Mar 24 18:59:08 2005 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 7EE845343F for ; Thu, 24 Mar 2005 18:59:08 +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 29873-03 for ; Thu, 24 Mar 2005 18:58:59 +0000 (GMT) Received: from mail.astudios.com (ns1.astudios.com [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 46E3953472 for ; Thu, 24 Mar 2005 18:58:57 +0000 (GMT) thread-index: AcUwo93iFUqy/3cMTJinRcLRRfwi0g== Received: from [10.4.13.50] ([66.194.26.150]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.211); Thu, 24 Mar 2005 14:01:19 -0500 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181 Message-ID: <42430E20.1010600@sermonaudio.com> Date: Thu, 24 Mar 2005 13:59:44 -0500 From: "Chris Hoover" User-Agent: Mozilla Thunderbird 0.8 (X11/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "PostgreSQL Admin" Subject: Copying large tables with DBLink Content-Type: text/plain; format=flowed; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 24 Mar 2005 19:01:21.0000 (UTC) FILETIME=[DDDD5680:01C530A3] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/401 X-Sequence-Number: 16899 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? The problem we were trying to solve is the fact that due to new column additions that are populated during the conversion, our db's are doubling in space. This is requiring lengthy vacuum fulls to reclaim the space and making for a very long conversion time. We are very concerned that this system lockup could happen on our production boxes since we have several db's that are very large and probably have tables larger than our memory on the systems. Anyway, any ideas on how to get around this, or how we might speed it up and not use so much space would be appreciated. Chris PG 7.3.4 From mail@joeconway.com Thu Mar 24 19:21:20 2005 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 From tgl@sss.pgh.pa.us Thu Mar 24 19:40:26 2005 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 96C82534B5 for ; Thu, 24 Mar 2005 19:40:26 +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 43077-06 for ; Thu, 24 Mar 2005 19:40:24 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 088FB53474 for ; Thu, 24 Mar 2005 19:40:21 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2OJeMPI005021; Thu, 24 Mar 2005 14:40:22 -0500 (EST) To: "Chris Hoover" Cc: "PostgreSQL Admin" Subject: Re: Copying large tables with DBLink In-reply-to: <42430E20.1010600@sermonaudio.com> References: <42430E20.1010600@sermonaudio.com> Comments: In-reply-to "Chris Hoover" message dated "Thu, 24 Mar 2005 13:59:44 -0500" Date: Thu, 24 Mar 2005 14:40:22 -0500 Message-ID: <5020.1111693222@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/403 X-Sequence-Number: 16901 "Chris Hoover" writes: > 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. You're not going to be able to do that with dblink, nor any other set-returning function, because the current implementation of SRFs always materializes the entire function result in temporary memory/swap. Consider something like pg_dump -t srctab srcdb | psql destdb instead. regards, tom lane From mike@fuhr.org Thu Mar 24 19:51:14 2005 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 60D1D534AB for ; Thu, 24 Mar 2005 19:51:14 +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 46865-02 for ; Thu, 24 Mar 2005 19:51:03 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 810EB53457 for ; Thu, 24 Mar 2005 19:51:02 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j2OJp0Ed034755 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 24 Mar 2005 12:51:03 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j2OJp0nI013293; Thu, 24 Mar 2005 12:51:00 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j2OJp0O8013292; Thu, 24 Mar 2005 12:51:00 -0700 (MST) (envelope-from mfuhr) Date: Thu, 24 Mar 2005 12:50:59 -0700 From: Michael Fuhr To: Chris Hoover Cc: PostgreSQL Admin Subject: Re: Copying large tables with DBLink Message-ID: <20050324195059.GA13146@winnie.fuhr.org> References: <42430E20.1010600@sermonaudio.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42430E20.1010600@sermonaudio.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/404 X-Sequence-Number: 16902 On Thu, Mar 24, 2005 at 01:59:44PM -0500, 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. Hmmm...doesn't dblink use libpq, and doesn't libpq fetch the entire result set before doing anything with it? If so, then that could explain the memory exhaustion. > 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? How about using pg_dump to dump the original table and restore it into the new table? If you just want the table's contents without the table definition then you could use the -a (--data-only) option. Another possibility would be to write a function that uses a cursor: dblink_open() and a loop that calls dblink_fetch() until you reach the end of the result set. I think that wouldn't have a memory exhaustion problem (but test it to be sure). -- Michael Fuhr http://www.fuhr.org/~mfuhr/