Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 9D5DE9F9A02 for ; Tue, 16 Oct 2007 00:53:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 96439-10 for ; Tue, 16 Oct 2007 00:53:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from out1.smtp.messagingengine.com (out1.smtp.messagingengine.com [66.111.4.25]) by postgresql.org (Postfix) with ESMTP id 3A7769F99A3 for ; Tue, 16 Oct 2007 00:53:47 -0300 (ADT) Received: from compute1.internal (compute1.internal [10.202.2.41]) by out1.messagingengine.com (Postfix) with ESMTP id 32A513234E; Mon, 15 Oct 2007 23:53:45 -0400 (EDT) Received: from heartbeat2.messagingengine.com ([10.202.2.161]) by compute1.internal (MEProxy); Mon, 15 Oct 2007 23:53:45 -0400 X-Sasl-enc: YTvEHQ2jBdFDXmxAvI2oeSYGWPrcKP93UYV78YsfC09M 1192506821 Received: from [192.168.1.235] (64-142-85-108.dsl.dynamic.sonic.net [64.142.85.108]) by mail.messagingengine.com (Postfix) with ESMTP id 88B0D17B18; Mon, 15 Oct 2007 23:53:40 -0400 (EDT) Message-ID: <471435D1.4030806@fastmail.net> Date: Mon, 15 Oct 2007 20:53:53 -0700 From: Darren Reed User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) MIME-Version: 1.0 To: Scott Marlowe CC: Tom Lane , "Joshua D. Drake" , pgsql-admin@postgresql.org Subject: Re: Is my database now too big? References: <1191730382.18880.1214536197@webmail.messagingengine.com> <4708A9DC.5060306@fastmail.net> <47097EA7.902@fastmail.net> <4712D97E.7010004@fastmail.net> <4712E15B.8000405@fastmail.net> <17093.1192464190@sss.pgh.pa.us> <4713CA8C.5020303@fastmail.net> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/134 X-Sequence-Number: 27048 Scott Marlowe wrote: > ... > > Again, I'm kinda shooting in the dark here as you reveal more and more > what you are doing a little at a time. A test case that can invoke > this failure would be most useful. > After seeing this today: ERROR: duplicate key violates unique constraint "ers_pkey" ERROR: duplicate key violates unique constraint "foo_pkey" ERROR: duplicate key violates unique constraint "foo_pkey" ERROR: duplicate key violates unique constraint "foo_pkey" ERROR: duplicate key violates unique constraint "foo_pkey" ERROR: duplicate key violates unique constraint "foo_pkey" ERROR: could not open segment 1 of relation 1663/10793/2659 (target block 858862642): No such file or directory ERROR: could not open segment 1 of relation 1663/10793/2659 (target block 858862642): No such file or directory ERROR: could not open segment 1 of relation 1663/10793/2659 (target block 858862642): No such file or directory ERROR: could not open segment 1 of relation 1663/10793/2659 (target block 858862642): No such file or directory ... ...there was little or no activity during this time, apart from some inserts, maybe some selects, etc. Nothing that should have caused this kind of upset. There is a file that matches this: -rw------- 1 postgres wheel 57344 Oct 14 22:57 /data/db/second/base/10793/2659 but it isn't in the directory where I moved most of the indexes to: ls /data/index/ext/10793/ 16390 16397 16399 16406 16407 16410 16414 16425 16434 16435 I don't know if the file numbers have any meaning? But in addition, the list of tables (\dp) is now fubar'd. I'm starting to wonder if it is a combination of: - the operating system (NetBSD 4.99.20) - the hardware (small HP box, not meant for hard work like this but shouldn't be impossible for it) - the way pkgsrc compiles postgresql for NetBSD I'm shying away from the hardware (or at least RAM/CPU) because I'd expect there to be some other kind kinds of faults show up, ultimately leading to a panic due to just random corruption of some kernel data structure. As it is, everything else seems to be functioning ok. Darren