Although the manual is extensive, during the production usage some problem have arose.
First, it was a problem with SELECT queries to partitioned tables that was fixed by a separate patch before Zabbix v.1.8.13. As of this version the patch is no more required. It brings a significant performance gain.
Secondly, it's about the fact that procedures in the manual above have an unpleasant effect. The data of first INSERT query that triggers a partition creation are always lost. I didn't have any desire to rewrite the proposed SQL, instead I implemented the obvious fix. To make INSERTs into history* tables beforehand. This also helps to solve the problem with table load. Partitioning procedures put a lock on tables and this can easily results in Zabbix malfunction. Planing will help you to avoid being awaken at 0.00 when Partitioning usually starts.
Example:
INSERT INTO history_uint (itemid, clock, value) VALUES ('1', extract( epoch FROM date_trunc('hour', now() + interval '12 hour')), '1')";The third fix is related to conditions that are wrongly used in the recipe above. I just put the corrected versions here. Corresponding lines are in the check_condition definition and I made them bold.
Hope, this notes will help someone.
CREATE OR REPLACE FUNCTION "public"."partition_every_day" (in parentoid oid, in scheme varchar, in clock int4) RETURNS text AS
$BODY$
declare
parent text := parentoid::regclass;
suffix text := to_char (to_timestamp(clock), '_YYYY_MM_DD');
child text := scheme || (select relname from pg_class where oid = parentoid) || suffix;
check_beg varchar;
check_end varchar;
check_condition varchar;
check_field varchar := null;
tmp record;
script text := '';
i int := 0;
j int := 0;
begin
perform child::regclass;
return child;
exception
when undefined_table then
check_beg = extract(epoch FROM date_trunc('day', to_timestamp(clock)));
check_end = extract(epoch FROM date_trunc('day', to_timestamp(clock) + interval '1 day'));
j = (select count(*) from pg_attribute where attrelid = parentoid and attnum >0);
for tmp in
select attname from pg_attribute where attrelid = parentoid and attnum >0 order by attnum loop
i = i + 1;
script = script || 'NEW.' || tmp.attname || case i when j then '' else ',' end;
if (col_description (parentoid, i) ~* 'partition') and (check_field is null)
then check_field = tmp.attname; end if;
end loop;
script = script || ')';
check_condition =
'(
' || check_field || ' >= ' || quote_literal (check_beg) || ' and
' || check_field || ' < ' || quote_literal (check_end) || '
)';
execute
'create table ' || child || '
(
constraint partition' || suffix || ' check '
|| check_condition || '
)
inherits (' || parent || ')';
execute
'create rule route' || suffix || ' as '
|| ' on insert to ' || parent || ' where '
|| check_condition
|| ' do instead insert into ' || child
|| ' values (' || script;
perform copy_constraints(parent, child);
perform copy_indexes(parent, child);
execute 'GRANT SELECT ON ' || child || ' TO some_other_user';
execute 'GRANT ALL ON ' || child || ' TO zabbix';
return child;
end;
$BODY$
LANGUAGE 'plpgsql'
-----
CREATE OR REPLACE FUNCTION "public"."partition_every_month" (in parentoid oid, in scheme varchar, in clock int4) RETURNS text AS
$BODY$
declare
parent text := parentoid::regclass;
suffix text := to_char (to_timestamp(clock), '_YYYY_MM');
child text := scheme || (select relname from pg_class where oid = parentoid) || suffix;
check_beg varchar;
check_end varchar;
check_condition varchar;
check_field varchar := null;
tmp record;
script text := '';
i int := 0;
j int := 0;
begin
perform child::regclass;
return child;
exception
when undefined_table then
check_beg = extract(epoch FROM date_trunc('month', to_timestamp(clock)));
check_end = extract(epoch FROM date_trunc('month', to_timestamp(clock) + interval '1 month'));
j = (select count(*) from pg_attribute where attrelid = parentoid and attnum >0);
for tmp in
select attname from pg_attribute where attrelid = parentoid and attnum >0 order by attnum loop
i = i + 1;
script = script || 'NEW.' || tmp.attname || case i when j then '' else ',' end;
if (col_description (parentoid, i) ~* 'partition') and (check_field is null)
then check_field = tmp.attname; end if;
end loop;
script = script || ')';
check_condition =
'(
' || check_field || ' >= ' || quote_literal (check_beg) || ' and
' || check_field || ' < ' || quote_literal (check_end) || '
)';
execute
'create table ' || child || '
(
constraint partition' || suffix || ' check '
|| check_condition || '
)
inherits (' || parent || ')';
execute
'create rule route' || suffix || ' as '
|| ' on insert to ' || parent || ' where '
|| check_condition
|| ' do instead insert into ' || child
|| ' values (' || script;
perform copy_constraints(parent, child);
perform copy_indexes(parent, child);
execute 'GRANT SELECT ON ' || child || ' TO xxx';
execute 'GRANT ALL ON ' || child || ' TO zabbix';
return child;
end;
$BODY$
LANGUAGE 'plpgsql'
