Re: Table Partitioning - Mailing list pgsql-general
From | Richard Onorato |
---|---|
Subject | Re: Table Partitioning |
Date | |
Msg-id | 82202049-3519-4344-8B4A-593F5E066DB2@yahoo.com Whole thread Raw |
In response to | Re: Table Partitioning (Raghavendra <raghavendra.rao@enterprisedb.com>) |
List | pgsql-general |
Interesting. I wonder what I am doing wrong. I will try and setup the database again and see if I can get it to work.
thank you for testing it out for me.
Richard
On May 21, 2013, at 1:06 PM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
On Tue, May 21, 2013 at 11:03 PM, Richard Onorato <richard_onorato@yahoo.com> wrote: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 $$BEGINIF ( (NEW.c1 % 5) = 0 ) THENINSERT INTO MyMappingTableT1 VALUES (NEW.*);ELSIF ( (NEW.c1 % 5) = 1 ) THENINSERT INTO MyMappingTableT2 VALUES (NEW.*);ELSIF ( (NEW.c1 % 5) = 2 ) THENINSERT INTO MyMappingTableT3 VALUES (NEW.*);ELSIF ( (NEW.c1 % 5) = 3 ) THENINSERT INTO MyMappingTableT4 VALUES (NEW.*);ELSIF ( (NEW.c1 % 5) = 4 ) THENINSERT INTO MyMappingTableT5 VALUES (NEW.*);ELSERAISE 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_triggerBEFORE INSERT ON MyMappingTableFOR EACH ROW EXECUTE PROCEDURE my_mapping_table_insert_trigger();SET constraint_exclusion = ON;Regards,RichardI tried your test case, its working fine from my end and populating data properly to partition childs.insert into mymappingtable values (1,7,20,30,1,now());insert into mymappingtable values (2,6,20,30,1,now());insert into mymappingtable values (3,8,20,30,1,now());insert into mymappingtable values (4,9,20,30,1,now());insert into mymappingtable values (5,10,20,30,1,now());postgres=# \dt+ MyMappingTable*List of relationsSchema | Name | Type | Owner | Size | Description--------+------------------+-------+----------+------------+-------------public | mymappingtable | table | postgres | 0 bytes |public | mymappingtablet1 | table | postgres | 8192 bytes |public | mymappingtablet2 | table | postgres | 8192 bytes |public | mymappingtablet3 | table | postgres | 8192 bytes |public | mymappingtablet4 | table | postgres | 8192 bytes |public | mymappingtablet5 | table | postgres | 8192 bytes |---Regards,RaghavendraEnterpriseDB Corporation
pgsql-general by date: