hello again! since there was a problem with my email and the reply was not sent, so I'm re-posting my reply..
Again.. the structure as I exported it from phpPgAdmin is: *-- My table 'foo' * *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()* *);* *-- the trigger before insert on table 'foo' * *CREATE TRIGGER foo_insert BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE foo_insert_trigger(); * * * *-- The function that is supposed to create dynamically new partition tables * *CREATE FUNCTION foo_insert_trigger() RETURNS trigger* * LANGUAGE plpgsql* * AS $_$DECLARE* * entry_id integer;* * from_value integer;* * to_value integer;* * table_name varchar;* *BEGIN* * entry_id = NEW.foo_id/20::int;* * from_value = entry_id;* * 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 || ' ON ' || table_name || ' USING btree (foo_id, blaa_id, blaa_num)';* * EXECUTE 'CREATE UNIQUE INDEX pk_foo_' || from_value || '_to_' || to_value || ' ON ' || table_name ||' USING btree (foo_id, foo_num)';* * * * END IF;* * * * EXECUTE 'INSERT INTO ' || table_name ||' VALUES (($1).*)' USING NEW ;* * * * RETURN NULL;* *END;$_$;* I insert 100 entries (look into the attached file) so I am expecting to have 5 partition tables, each of one contains 20 entries: foo_1_to_20 foo_21_to_40 foo_41_to_60 foo_61_to_80 foo_81_to_100 I did what you suggested: "entry_id = NEW.foo_id/20::int;" and I solved the problem with the creation of 100 partition tables containing 1 value but still there is an error after the insertion of the 21st value: *partitioning_fake_data.sql:41: ERROR: new row for relation "foo_1_to_21" violates check constraint "foo_1_to_21_foo_id_check" CONTEXT: SQL statement "INSERT INTO foo_1_to_21 VALUES (($1).*)" PL/pgSQL function "foo_insert_trigger" line 19 at EXECUTE statement* * * And also the tables that are created until the error occures are: foo_0_to_20 foo_1_to_21 Thank you in advance!! dafni On Wed, Jun 26, 2013 at 5:55 PM, AI Rumman <rumman...@gmail.com> wrote: > That because you are generating table name from from_value which is > distinct everytime. > Like, > > INSERT INTO foo VALUES (1, 11, 11, 1, '2013-06-26 16:38:58.466'); > NOTICE: table_name = (foo_1_to_21) > NOTICE: CREATE TABLE foo_1_to_21 (CHECK ( foo_id >= 1 AND foo_id <= 21 )) > INHERITS (foo) > INSERT 0 0 > postgres=# \d > List of relations > Schema | Name | Type | Owner > --------+---------------+-------+---------- > public | foo | table | postgres > public | foo_1_to_21 | table | postgres > public | foo_99_to_119 | table | postgres > (3 rows) > > postgres=# INSERT INTO foo VALUES (2, 12, 12, 2, '2013-06-26 > 16:38:58.466'); > NOTICE: table_name = (foo_2_to_22) > NOTICE: CREATE TABLE foo_2_to_22 (CHECK ( foo_id >= 2 AND foo_id <= 22 )) > INHERITS (foo) > INSERT 0 0 > postgres=# \d > List of relations > Schema | Name | Type | Owner > --------+---------------+-------+---------- > public | foo | table | postgres > public | foo_1_to_21 | table | postgres > public | foo_2_to_22 | table | postgres > public | foo_99_to_119 | table | postgres > (4 rows) > > Here, for two inserts it creates two tables one for foo_id = 1 and other > for foo_id = 2. > > > Use, > from_value = entry_id/20::int > > > > On Wed, Jun 26, 2013 at 10:50 AM, dafNi zaf <dza...@gmail.com> wrote: > >> 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 >>>> クルズ クリスチアン ダニエル >>>> >>> >>> >> >
partitioning_fake_data.sql
Description: Binary data
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
