Search Postgresql Archives

Re: dynamic partitioning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux