Re: ShmemAlloc errors

On Fri, 17 Oct 2003 15:47:10 +0100, Nick Burrett <nick@dsvr.net>
wrote:
>CREATE TABLE fiveminute ( server CHAR(32),
>                           stamp TIMESTAMP,
>                           bytesin BIGINT CHECK (bytesin >= 0),
>                           bytesout BIGINT CHECK (bytesout >= 0));
>
>CREATE UNIQUE INDEX fiveminute_idx ON fiveminute(server,stamp);

Making this

    CREATE TABLE server (
        id int NOT NULL PRIMARY KEY,
        name text NOT NULL
    );
    CREATE TABLE fiveminute (
        serverid int NOT NULL REFERENCES server,
        stamp timestamp NOT NULL,
        bytesin bigint CHECK (bytesin >= 0),
        bytesout bigint CHECK (bytesout >= 0),
        PRIMARY KEY (serverid, stamp)
    );

should give you a much smaller index on fiveminute.

If you have to load lots of data initially, better create the tables
without primary and foreign keys, import data and then

    ALTER TABLE server ADD PRIMARY KEY (id);
    ALTER TABLE fiveminute ADD PRIMARY KEY (serverid, stamp);
    ALTER TABLE fiveminute
        ADD FOREIGN KEY (serverid) REFERENCES server;

Servus
 Manfred