Thread: Table Partitioning
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
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,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
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
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.rao@enterprisedb.com>
To: Richard Onorato <richard_onorato@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Tuesday, May 21, 2013 1:06 PM
Subject: Re: [GENERAL] Table Partitioning
From: Raghavendra <raghavendra.rao@enterprisedb.com>
To: Richard Onorato <richard_onorato@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.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@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,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
On Tuesday, May 21, 2013, Richard Onorato 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:
This works for me, on 9.2.4. Can you show exactly how you do the inserts?
CREATE TABLE MyMappingTableT1 (PRIMARY KEY (id), CHECK((c1 % 5) = 0)) INHERITS (MyMappingTable);
This is not going to work well. constraint_exclusion does not understand the modulus operator.
Cheers,
Jeff
On Wed, May 22, 2013 at 6:54 AM, Richard Onorato <richard_onorato@yahoo.com> wrote:
Were you able to get it to insert with the bigserial being used on the table?
Yes.
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
Hmm, I guess you are inserting on the parent table not directly into inherited table.
Can you share the INSERT statement.
Is auto-increment supported on table partitioning?
Yes, BIGSERIAL will create a sequence that will be shared by all child partitions.
Check below example as per your test case, INSERT statement do not have BIGSERIAL column still its auto-increment and populated data in child tables.
postgres=# insert into mymappingtable(c1,c2,c3,count,createdtime) values (9,20,30,1,now());
INSERT 0 0
postgres=# insert into mymappingtable(c1,c2,c3,count,createdtime) values (7,20,30,1,now());
INSERT 0 0
postgres=# select * from mymappingtablet5;
id | c1 | c2 | c3 | count | createdtime
----+----+----+----+-------+----------------------------------
8 | 9 | 20 | 30 | 1 | 2013-05-18 02:08:33.061548+05:30
postgres=# select * from mymappingtablet3;
id | c1 | c2 | c3 | count | createdtime
----+----+----+----+-------+----------------------------------
9 | 7 | 20 | 30 | 1 | 2013-05-18 02:12:03.076529+05:30
(1 row)
(Request not to top-post please ... :) )
---
Raghavendra,
I am doing my inserts via Java JPA statements embedded in my Data Access Layer. I can share them if you would like to see them.
Regards,
Richard
From: Raghavendra <raghavendra.rao@enterprisedb.com>
To: Richard Onorato <richard_onorato@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Wednesday, May 22, 2013 2:39 AM
Subject: Re: [GENERAL] Table Partitioning
From: Raghavendra <raghavendra.rao@enterprisedb.com>
To: Richard Onorato <richard_onorato@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Wednesday, May 22, 2013 2:39 AM
Subject: Re: [GENERAL] Table Partitioning
On Wed, May 22, 2013 at 6:54 AM, Richard Onorato <richard_onorato@yahoo.com> wrote:
Were you able to get it to insert with the bigserial being used on the table?
Yes.
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
Hmm, I guess you are inserting on the parent table not directly into inherited table.
Can you share the INSERT statement.
Is auto-increment supported on table partitioning?
Yes, BIGSERIAL will create a sequence that will be shared by all child partitions.
Check below example as per your test case, INSERT statement do not have BIGSERIAL column still its auto-increment and populated data in child tables.
postgres=# insert into mymappingtable(c1,c2,c3,count,createdtime) values (9,20,30,1,now());
INSERT 0 0
postgres=# insert into mymappingtable(c1,c2,c3,count,createdtime) values (7,20,30,1,now());
INSERT 0 0
postgres=# select * from mymappingtablet5;
id | c1 | c2 | c3 | count | createdtime
----+----+----+----+-------+----------------------------------
8 | 9 | 20 | 30 | 1 | 2013-05-18 02:08:33.061548+05:30
postgres=# select * from mymappingtablet3;
id | c1 | c2 | c3 | count | createdtime
----+----+----+----+-------+----------------------------------
9 | 7 | 20 | 30 | 1 | 2013-05-18 02:12:03.076529+05:30
(1 row)
(Request not to top-post please ... :) )
---
So I worked around most of my errors. I removed the bigserial and used two of the columns as the primary key. I am now getting the following hibernate exception back:
Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
This appears to be caused by the fact that the function is not returning back the row count. I did a google search and found a few suggestions on how to resolve this issue, but they do not seem to work well. I tried returning NEW, but that seems to cause the engine to also insert the record in the base table as well as a partition. Thus I end up with 120 records when I am expecting just 60.
Any ideas on how I can fix this issue?
Regards,
Richard
From: Richard Onorato <richard_onorato@yahoo.com>
To: Raghavendra <raghavendra.rao@enterprisedb.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Wednesday, May 22, 2013 7:27 PM
Subject: Re: [GENERAL] Table Partitioning
From: Richard Onorato <richard_onorato@yahoo.com>
To: Raghavendra <raghavendra.rao@enterprisedb.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Wednesday, May 22, 2013 7:27 PM
Subject: Re: [GENERAL] Table Partitioning
Raghavendra,
I am doing my inserts via Java JPA statements embedded in my Data Access Layer. I can share them if you would like to see them.
Regards,
Richard
From: Raghavendra <raghavendra.rao@enterprisedb.com>
To: Richard Onorato <richard_onorato@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Wednesday, May 22, 2013 2:39 AM
Subject: Re: [GENERAL] Table Partitioning
From: Raghavendra <raghavendra.rao@enterprisedb.com>
To: Richard Onorato <richard_onorato@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Wednesday, May 22, 2013 2:39 AM
Subject: Re: [GENERAL] Table Partitioning
On Wed, May 22, 2013 at 6:54 AM, Richard Onorato <richard_onorato@yahoo.com> wrote:
Were you able to get it to insert with the bigserial being used on the table?
Yes.
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
Hmm, I guess you are inserting on the parent table not directly into inherited table.
Can you share the INSERT statement.
Is auto-increment supported on table partitioning?
Yes, BIGSERIAL will create a sequence that will be shared by all child partitions.
Check below example as per your test case, INSERT statement do not have BIGSERIAL column still its auto-increment and populated data in child tables.
postgres=# insert into mymappingtable(c1,c2,c3,count,createdtime) values (9,20,30,1,now());
INSERT 0 0
postgres=# insert into mymappingtable(c1,c2,c3,count,createdtime) values (7,20,30,1,now());
INSERT 0 0
postgres=# select * from mymappingtablet5;
id | c1 | c2 | c3 | count | createdtime
----+----+----+----+-------+----------------------------------
8 | 9 | 20 | 30 | 1 | 2013-05-18 02:08:33.061548+05:30
postgres=# select * from mymappingtablet3;
id | c1 | c2 | c3 | count | createdtime
----+----+----+----+-------+----------------------------------
9 | 7 | 20 | 30 | 1 | 2013-05-18 02:12:03.076529+05:30
(1 row)
(Request not to top-post please ... :) )
---