Re: dynamic partitioning

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