pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Darren Reed <darrenr+postgres@fastmail.net>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Is my database now too big?
Date: Mon, 15 Oct 2007 23:33:11 -0700
Message-ID: <47145B27.9030200@fastmail.net> (raw)
In-Reply-To: <dcc563d10710152103x7b3b2f68q2e133172731f7c0a@mail.gmail.com>
References: <1191730382.18880.1214536197@webmail.messagingengine.com>
	<dcc563d10710070713q6aa1fbfdqe8f07e5356d39d41@mail.gmail.com>
	<47097EA7.902@fastmail.net>
	<dcc563d10710071803ia92a046neef1296999173d13@mail.gmail.com>
	<4712D97E.7010004@fastmail.net>
	<4712E15B.8000405@fastmail.net>
	<17093.1192464190@sss.pgh.pa.us>
	<4713CA8C.5020303@fastmail.net>
	<dcc563d10710151346w430da8a2s265bd8e129ee7d@mail.gmail.com>
	<471435D1.4030806@fastmail.net>
	<dcc563d10710152103x7b3b2f68q2e133172731f7c0a@mail.gmail.com>

Scott Marlowe wrote:
> On 10/15/07, Darren Reed <darrenr+postgres@fastmail.net> 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

Attachments:

sanitised (text/plain, 2.6K) download 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;

view thread (27+ messages)  latest in thread

Message-ID: <47145B27.9030200@fastmail.net>
Permalink:  ../47145B27.9030200@fastmail.net/
Also on:    postgresql.org/message-id/47145B27.9030200@fastmail.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: darrenr+postgres@fastmail.net, scott.marlowe@gmail.com, tgl@sss.pgh.pa.us, jd@commandprompt.com
  Subject: Re: Is my database now too big?
  In-Reply-To: <47145B27.9030200@fastmail.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox