Search Postgresql Archives

dynamic partitioning

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

 



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: partitioning_fake_data.sql
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[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