Re: dynamic partitioning

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 <rummandba(at)gmail(dot)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 <
> danielcristian(at)gmail(dot)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 <dzaf88(at)gmail(dot)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 <dzaf88(at)gmail(dot)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
>> クルズ クリスチアン ダニエル
>>
>
>