Re: Is my database now too big? - Mailing list pgsql-admin

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

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;