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