From WernerBohl@infutor.com Fri Feb 4 21:39:36 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 C64A88B9BD0 for ; Fri, 4 Feb 2005 21:39:36 +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 48962-05 for ; Fri, 4 Feb 2005 21:39:24 +0000 (GMT) Received: from omr3.netsolmail.com (omr3.netsolmail.com [216.168.230.164]) by svr1.postgresql.org (Postfix) with ESMTP id 73CC68B9B57 for ; Fri, 4 Feb 2005 21:39:25 +0000 (GMT) Received: from ms9.netsolmail.com (IDENT:mirapoint@[216.168.230.183]) by omr3.netsolmail.com (8.12.10/8.12.10) with ESMTP id j14LdCmq008295 for ; Fri, 4 Feb 2005 16:39:25 -0500 (EST) Received: from [192.168.2.2] ([200.12.239.103]) by ms9.netsolmail.com (Mirapoint Messaging Server MOS 3.2.2-GA) with ESMTP id CNG85287; Fri, 4 Feb 2005 16:39:10 -0500 (EST) Subject: PG 8 Out of memory issuing queries From: Werner Bohl Reply-To: WernerBohl@infutor.com To: PgSQL ADMIN Content-Type: text/plain Organization: Infutor de Costa Rica S.A. Message-Id: <1107553146.16535.6.camel@sirio> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 04 Feb 2005 15:39:06 -0600 Content-Transfer-Encoding: 7bit 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: 200502/38 X-Sequence-Number: 16206 Hi! Hope anybody can help me with this issue. I have a fairly large table (~75 million rows) that where imported without pk. Tried to create a pk on a supposed unique field, but failed with duplicates. So i issued: select oldid from table group by oldid having count(*) > 1 and it fails with Out of memory. Tried to create a new pk using: alter table add newid serial primary key; and it also fails with out of memory error. Are there any settings on postgresql.conf that I may change to prevent this error? This is on Windows XP profesional with Postgresql 8.0 TIA, -- Werner Bohl Infutor de Costa Rica S.A. From jd@commandprompt.com Sat Feb 5 01:36:32 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 D26998B9E81 for ; Sat, 5 Feb 2005 01:36:32 +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 64160-04 for ; Sat, 5 Feb 2005 01:36:26 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id 2AB018B9E2A for ; Sat, 5 Feb 2005 01:36:28 +0000 (GMT) Received: from [192.168.1.51] (fc1smp [66.93.38.87]) (authenticated bits=0) by hosting.commandprompt.com (8.12.8/8.12.8) with ESMTP id j151aN8p013313; Fri, 4 Feb 2005 17:36:24 -0800 Message-ID: <4204230D.9050900@commandprompt.com> Date: Fri, 04 Feb 2005 17:36:13 -0800 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: WernerBohl@infutor.com Cc: PgSQL ADMIN Subject: Re: PG 8 Out of memory issuing queries References: <1107553146.16535.6.camel@sirio> In-Reply-To: <1107553146.16535.6.camel@sirio> Content-Type: multipart/mixed; boundary="------------040401040608080006070303" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.043 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/45 X-Sequence-Number: 16213 This is a multi-part message in MIME format. --------------040401040608080006070303 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Werner Bohl wrote: > Hi! > > Hope anybody can help me with this issue. I have a fairly large table > (~75 million rows) that where imported without pk. Tried to create a pk > on a supposed unique field, but failed with duplicates. So i issued: > select oldid from table group by oldid having count(*) > 1 > and it fails with Out of memory. Tried to create a new pk using: alter > table
add newid serial primary key; and it also fails with out > of memory error. Use a cursor during your large count(*) query and you can find you bad entries. J > > Are there any settings on postgresql.conf that I may change to prevent > this error? > > This is on Windows XP profesional with Postgresql 8.0 > > > TIA, > -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com --------------040401040608080006070303 Content-Type: text/x-vcard; charset=utf-8; name="jd.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jd.vcf" begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard --------------040401040608080006070303--