Table Partitioning

From: Richard Onorato <richard_onorato(at)yahoo(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Table Partitioning
Date: 2013-05-21 17:33:59
Message-ID: 1369157639.28306.YahooMailNeo@web141002.mail.bf1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am wanting to partition my data based on a mod of one of the bigint columns, but when I run my insert test all the data goes into the base table and not the partitions.  Here is what the table looks like:

CREATE table MyMappingTable ( id bigserial NOT NULL,
                                     c1 bigInt NOT NULL,
                                     c2 bigInt NOT NULL,
                                     c3 bigint NOT NULL,
                                     count bigint DEFAULT 1,
                                     createdTime timestamp with time zone default CURRENT_TIMESTAMP,
                                     CONSTRAINT MyMappingTable_index PRIMARY KEY (id) )
with (OIDS=FALSE);

CREATE TABLE MyMappingTableT1 (PRIMARY KEY (id), CHECK((c1 % 5) = 0)) INHERITS (MyMappingTable);
CREATE TABLE MyMappingTableT2 (PRIMARY KEY (id), CHECK((c1 % 5) = 1)) INHERITS (MyMappingTable);
CREATE TABLE MyMappingTableT3 (PRIMARY KEY (id), CHECK((c1 % 5) = 2)) INHERITS (MyMappingTable);
CREATE TABLE MyMappingTableT4 (PRIMARY KEY (id), CHECK((c1 % 5) = 3)) INHERITS (MyMappingTable);
CREATE TABLE MyMappingTableT5 (PRIMARY KEY (id), CHECK((c1 % 5) = 4)) INHERITS (MyMappingTable);

Here is the trigger function that I added to the database:

CREATE OR REPLACE FUNCTION my_mapping_table_insert_trigger()
RETURNS trigger AS $$
BEGIN
  IF ( (NEW.c1 % 5) = 0 ) THEN 
    INSERT INTO MyMappingTableT1 VALUES (NEW.*); 
  ELSIF ( (NEW.c1 % 5) = 1 ) THEN 
    INSERT INTO MyMappingTableT2 VALUES (NEW.*);
  ELSIF ( (NEW.c1 % 5) = 2 ) THEN 
    INSERT INTO MyMappingTableT3 VALUES (NEW.*);
  ELSIF ( (NEW.c1 % 5) = 3 ) THEN 
    INSERT INTO MyMappingTableT4 VALUES (NEW.*);
  ELSIF ( (NEW.c1 % 5) = 4 ) THEN 
    INSERT INTO MyMappingTableT5 VALUES (NEW.*);
  ELSE

    RAISE EXCEPTION 'c1 mod out of range.  Something wrong with the my_mapping_table_insert_trigger() function!';
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Here is the Trigger that I added to the table:

CREATE TRIGGER insert_my_mapping_table_trigger 
  BEFORE INSERT ON MyMappingTable 
  FOR EACH ROW EXECUTE PROCEDURE my_mapping_table_insert_trigger();

SET constraint_exclusion = ON;

Regards,

Richard

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raghavendra 2013-05-21 18:06:09 Re: Table Partitioning
Previous Message Adarsh Sharma 2013-05-21 11:48:30 Re: WAL contains references to invalid pages