Re: dynamic partitioning

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
> クルズ クリスチアン ダニエル
>