From: | Richard Onorato <richard_onorato(at)yahoo(dot)com> |
---|---|
To: | Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Table Partitioning |
Date: | 2013-05-22 01:24:58 |
Message-ID: | 1369185898.17446.YahooMailNeo@web141004.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Were you able to get it to insert with the bigserial being used on the table? Every time I go to do an insert into one of the inherited tables I am now getting the following exception:
org.hibernate.HibernateException: The database returned no natively generated identity value
Is auto-increment supported on table partitioning?
Regards,
Richard
________________________________
From: Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com>
To: Richard Onorato <richard_onorato(at)yahoo(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, May 21, 2013 1:06 PM
Subject: Re: [GENERAL] Table Partitioning
On Tue, May 21, 2013 at 11:03 PM, Richard Onorato <richard_onorato(at)yahoo(dot)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 $$
>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
I 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 relations
Schema | 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,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Neeraj Rai | 2013-05-22 01:26:08 | |
Previous Message | Adrian Klaver | 2013-05-22 00:57:03 | Re: DECLARING THE CURSOR WITH HOLD |