dynamic partitioning

From: dafNi zaf <dzaf88(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: dynamic partitioning
Date: 2013-06-26 13:47:05
Message-ID: CAAega+4FBS44FxHNxkMf07SyTHokz+bUvm_ft64wSokqmf3fOQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Attachment Content-Type Size
partitioning_fake_data.sql application/octet-stream 7.0 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message dafNi zaf 2013-06-26 13:54:59 Re: dynamic partitioning
Previous Message Albe Laurenz 2013-06-26 12:14:44 Re: utf8 errors