I solved the problem with the error! thank you very much! But there is still 1 issue:
when I insert multiple rows (for exaple with the attachment in my fist email) it creates 100 partition tables that contain 1 entry instead of 5 partitions with 20 entries.. Any ideas in that?? Thanks again! Dafni On Wed, Jun 26, 2013 at 5:32 PM, AI Rumman <rumman...@gmail.com> wrote: > Yes, you missed the trigger part. And also you will get error like below > during insert: > > INSERT INTO foo VALUES (99, 109, 109, 99, '2013-06-26 16:38:58.466'); > NOTICE: table_name = (foo_100_to_119) > NOTICE: CREATE TABLE foo_100_to_119 (CHECK ( foo_id >= 100 AND foo_id <= > 119 )) INHERITS (foo) > ERROR: new row for relation "foo_100_to_119" violates check constraint > "foo_100_to_119_foo_id_check" > DETAIL: Failing row contains (99, 109, 109, 99, 2013-06-26 > 16:38:58.466-04). > CONTEXT: SQL statement "INSERT INTO foo_100_to_119 VALUES (($1).*)" > PL/pgSQL function foo_insert_trigger() line 22 at EXECUTE statement > > > That because you added "entity_id + 1" in your function and hence when you > are giving foo_id = 99, it is creating table with check constraint where > foo_id >= 100 and foo_id <= 119. > > I modified it as below: > *from_value = entry_id ;* > * > * > Now its working: > > INSERT INTO foo VALUES (99, 109, 109, 99, '2013-06-26 16:38:58.466'); > NOTICE: table_name = (foo_99_to_119) > NOTICE: CREATE TABLE foo_99_to_119 (CHECK ( foo_id >= 99 AND foo_id <= > 119 )) INHERITS (foo) > INSERT 0 0 > postgres=# select * from foo; > foo_id | blaa_id | blaa_num | foo_num | createdatetime > --------+---------+----------+---------+---------------------------- > 99 | 109 | 109 | 99 | 2013-06-26 16:38:58.466-04 > (1 row) > > postgres=# select * from foo_99_to_119; > foo_id | blaa_id | blaa_num | foo_num | createdatetime > --------+---------+----------+---------+---------------------------- > 99 | 109 | 109 | 99 | 2013-06-26 16:38:58.466-04 > (1 row) > > postgres=# show constraint_exclusion ; > constraint_exclusion > ---------------------- > partition > (1 row) > > > > On Wed, Jun 26, 2013 at 10:25 AM, Daniel Cristian Cruz < > danielcrist...@gmail.com> wrote: > >> You forgot to set the trigger on foo: >> >> CREATE TRIGGER foo_insert >> BEFORE INSERT ON foo >> FOR EACH ROW EXECUTE PROCEDURE foo_insert_trigger(); >> >> >> >> 2013/6/26 dafNi zaf <dza...@gmail.com> >> >>> one note: I create a table of 100 entries in order to test it so I want >>> 5 partition of 20 entries each. >>> (And not a table of 100000 entries) >>> >>> >>> thanks again! >>> dafni >>> >>> >>> On Wed, Jun 26, 2013 at 4:47 PM, dafNi zaf <dza...@gmail.com> wrote: >>> >>>> Hello! >>>> >>>> I want to dynamically create partition tables that inherit a main table >>>> called "foo". >>>> The creation must occur when needed. >>>> >>>> For example, lets say that I want to insert 100000 entries and I want 5 >>>> partition >>>> tables (with 20000 entries each). >>>> >>>> So, first I need a partition for the first 20000 entries and when the >>>> entries reach >>>> the number 20000, another partition must be created, e.t.c.. >>>> >>>> I guess I need something like that: >>>> >>>> --the main table is: >>>> >>>> CREATE TABLE foo ( >>>> foo_id integer NOT NULL, >>>> blaa_id integer NOT NULL, >>>> blaa_num integer NOT NULL, >>>> foo_num integer NOT NULL, >>>> createdatetime timestamp with time zone DEFAULT now() >>>> ); >>>> >>>> --and the trigger function is: >>>> >>>> CREATE OR REPLACE FUNCTION foo_insert_trigger() >>>> RETURNS trigger AS $$ >>>> DECLARE >>>> entry_id integer; >>>> from_value integer; >>>> to_value integer; >>>> table_name varchar; >>>> BEGIN >>>> entry_id = NEW.foo_id; >>>> from_value = entry_id + 1; >>>> to_value = entry_id + 20; >>>> table_name='foo_' || from_value || '_to_' || to_value; >>>> >>>> IF not exists(select * from pg_class where relname = table_name) THEN >>>> EXECUTE 'CREATE TABLE ' || table_name || '(CHECK ( foo_id >=' || >>>> from_value || 'AND foo_id <=' || to_value || ' )) INHERITS (foo)' ; >>>> EXECUTE 'CREATE UNIQUE INDEX by_blaa_num_' || from_value || '_to_' || >>>> to_value || 'k ON ' || table_name ||' USING btree (foo_id, blaa_id, >>>> blaa_num)'; >>>> EXECUTE 'CREATE UNIQUE INDEX pk_foo_' || from_value || '_to_' || >>>> to_value || 'k ON ' || table_name ||' USING btree (foo_id, foo_num)'; >>>> EXECUTE 'GRANT ALL ON TABLE ' || table_name || ' TO foorole, >>>> postgres'; >>>> EXECUTE 'GRANT SELECT ON TABLE ' || table_name || ' TO blarole'; >>>> >>>> END IF; >>>> >>>> EXECUTE 'INSERT INTO ' || table_name ||' VALUES (($1).*)' USING NEW ; >>>> >>>> RETURN NULL; >>>> END; >>>> $$ >>>> LANGUAGE plpgsql; >>>> >>>> >>>> but it doesn't seem to work. It doesn't actually create new partition >>>> tables. >>>> The entries are inserted into "foo" >>>> >>>> I attach a test .sql file that contains the data of the table >>>> >>>> >>>> any help would save me from a lot of time! >>>> >>>> thank you in advance! >>>> >>>> dafni >>>> >>> >>> >> >> >> -- >> Daniel Cristian Cruz >> クルズ クリスチアン ダニエル >> > >
