Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 862D19FA4EE for ; Tue, 16 Oct 2007 03:33:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 37245-08 for ; Tue, 16 Oct 2007 03:33:01 -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 BDBF59FA2A0 for ; Tue, 16 Oct 2007 03:33:00 -0300 (ADT) Received: from compute1.internal (compute1.internal [10.202.2.41]) by out1.messagingengine.com (Postfix) with ESMTP id C79B03292A; Tue, 16 Oct 2007 02:32:59 -0400 (EDT) Received: from heartbeat1.messagingengine.com ([10.202.2.160]) by compute1.internal (MEProxy); Tue, 16 Oct 2007 02:32:59 -0400 X-Sasl-enc: s/LZH+WONtLkpenWKPY8AQVfPnFpjvQPbYzyst3O16gx 1192516379 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 E121141A7; Tue, 16 Oct 2007 02:32:58 -0400 (EDT) Message-ID: <47145B27.9030200@fastmail.net> Date: Mon, 15 Oct 2007 23:33:11 -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> <47097EA7.902@fastmail.net> <4712D97E.7010004@fastmail.net> <4712E15B.8000405@fastmail.net> <17093.1192464190@sss.pgh.pa.us> <4713CA8C.5020303@fastmail.net> <471435D1.4030806@fastmail.net> In-Reply-To: Content-Type: multipart/mixed; boundary="------------080901060603010705090704" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/137 X-Sequence-Number: 27051 This is a multi-part message in MIME format. --------------080901060603010705090704 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Scott Marlowe wrote: > On 10/15/07, Darren Reed wrote: > > 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 > > How, exactly, did you move those indexes? > With "ALTER TABLE". Since then I recreated the database and after merging a chunk of data, I see this: (various errors about duplicate keys and values too wide for fields...) ERROR: duplicate key violates unique constraint "t_a_pkey" LOG: unexpected EOF on client connection ERROR: value too long for type character(12) LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: could not send data to client: Broken pipe LOG: unexpected EOF on client connection ERROR: relation "t_a" does not exist I've attached the commands I've used to create the schema. It's called sanitised because I've culled all of the fields that aren't used. This script was used to create the database that I then imported records into before seeing the above. Darren --------------080901060603010705090704 Content-Type: text/plain; name="sanitised" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="sanitised" CREATE TABLESPACE ext LOCATION '/data/index/ext'; CREATE ROLE root; CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; CREATE TABLE t_a ( int1 INTEGER NOT NULL PRIMARY KEY, str1 CHARACTER(20), bool1 boolean ); CREATE VIEW a_v1 AS SELECT DISTINCT(str1),bool1 FROM t_a WHERE bool1 IS TRUE ORDER BY str1; CREATE VIEW a_v2 AS SELECT distinct(str1),count(*) FROM t_a GROUP BY t_a.str1; CREATE TABLE t_b ( int1 INTEGER NOT NULL, str2 CHARACTER VARYING(20) NOT NULL, bool1 BOOLEAN ); CREATE TABLE t_c ( str1 CHAR(20) NOT NULL, str2 VARCHAR(20) NOT NULL PRIMARY KEY ); CREATE TABLE ifl ( recno SERIAL PRIMARY KEY, int1 INTEGER NOT NULL ); DROP FUNCTION add_str1tot_a(); CREATE OR REPLACE FUNCTION add_str1tot_a() RETURNS TRIGGER AS $ptot_a$ DECLARE temp VARCHAR(20); BEGIN SELECT p.str1 INTO temp FROM t_c p WHERE p.str2=NEW.str2; UPDATE t_a SET str1=temp WHERE str1 IS NULL AND int1=NEW.int1; RETURN NULL; END; $ptot_a$ LANGUAGE plpgsql; DROP FUNCTION sett_astr1bool1(); CREATE OR REPLACE FUNCTION sett_astr1bool1() RETURNS TRIGGER as $sepi$ DECLARE ig BOOLEAN; BEGIN IF NEW.str1 IS NOT NULL AND NEW.bool1 IS NULL THEN SELECT b.bool1 INTO ig FROM a_v1 b WHERE b.str1=NEW.str1; IF ig IS NOT NULL THEN UPDATE t_a SET bool1=ig WHERE int1=NEW.int1; END IF; END IF; RETURN NULL; END; $sepi$ LANGUAGE plpgsql; DROP FUNCTION sett_abool1(); CREATE OR REPLACE FUNCTION sett_abool1() RETURNS TRIGGER as $sei$ DECLARE temp BOOLEAN; temp2 CHAR(20); BEGIN SELECT b.bool1 INTO temp FROM badt_b b WHERE (b.str2=NEW.str2) AND (bool1 IS NOT NULL); IF temp IS NOT NULL THEN UPDATE t_b SET bool1=temp WHERE str2=NEW.str2; END IF; SELECT t.str1 INTO temp2 FROM t_a t WHERE t.int1=NEW.int1; IF temp2 IS NULL THEN SELECT u.str1 INTO temp2 FROM t_c u WHERE u.str2=NEW.str2; IF temp2 IS NOT NULL THEN IF temp IS NOT NULL THEN UPDATE t_a SET str1=temp2,bool1=temp WHERE int1=NEW.int1; ELSE UPDATE t_a SET str1=temp2 WHERE int1=NEW.int1; END IF; ELSE IF temp IS NOT NULL THEN UPDATE t_a SET bool1=temp WHERE int1=NEW.int1; END IF; END IF; ELSE IF temp IS NOT NULL THEN UPDATE t_a SET bool1=temp WHERE int1=NEW.int1; END IF; END IF; RETURN NULL; END; $sei$ LANGUAGE plpgsql; CREATE INDEX t_a_str1 ON t_a USING btree (str1) TABLESPACE ext; CREATE INDEX str2_index ON t_b(str2); CREATE INDEX t_b_int1_index ON t_b(int1); CREATE INDEX t_c_str1_idx ON t_c(str1) TABLESPACE ext; CREATE INDEX t_c_str2_idx ON t_c(str2) TABLESPACE ext; ALTER INDEX t_c_pkey SET TABLESPACE ext; ALTER INDEX ifl_pkey SET TABLESPACE ext; --------------080901060603010705090704--