Search Postgresql Archives

Re: Table Partitioning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxx>
To: Richard Onorato <richard_onorato@xxxxxxxxx>
Cc: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Tuesday, May 21, 2013 1:06 PM
Subject: Re: Table Partitioning

On Tue, May 21, 2013 at 11:03 PM, Richard Onorato <richard_onorato@xxxxxxxxx> 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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux