PostgreSQL Partitioning using inheritance

Hi,

I decided to try on my development machine, to set up partitioning using PostgreSQL’s internal inheritance feature.

Essentially you create a parent table, and from that you can inherit all the columns. The benefit is that you can query the parent table and it will automatically go down into the inherited tables based on rules and what not, all the while benefiting from the query planner and optimisations it may apply.

[color=#FF0000]Disclaimer: I do not offer support or plan to support this. I am merely sharing what work I have done just playing around this afternoon.[/color]

[color=#00FF00]Code is free for anyone to use, modify, redistribute at a commercial, academic or personal level.[/color]

My setup is:

  • Ignition 7.6.4 (b2013112117) with developer mode license
  • Windows 7 SP1
  • PostgreSQL 9.3.2, compiled by Visual C++ build 1600, 64-bit
  • Java version “1.7.0_45” / Java™ SE Runtime Environment (build 1.7.0_45-b18) / Java HotSpot™ 64-Bit Server VM (build 24.45-b08, mixed mode)
  • JDBC41 Postgresql Driver, Version 9.3-1100

This is the main data table. Remember to turn off partitioning in Ignition Gateway configuration.

CREATE TABLE sqlth_1_data ( tagid integer NOT NULL, intvalue bigint, floatvalue double precision, stringvalue character varying(255), datevalue timestamp without time zone, dataintegrity integer, t_stamp bigint NOT NULL, CONSTRAINT sqlth_1_data_pkey PRIMARY KEY (tagid, t_stamp) ) WITH ( OIDS=FALSE );

And this is the function which does the partitioning. It is currently hard coded for per month partitions.

[code]CREATE OR REPLACE FUNCTION trg_sqlth_data_partition()
RETURNS trigger AS
$BODY$DECLARE
_newdate timestamp with time zone;
_tablename text;
_startdate text;
_enddate text;
BEGIN

_newdate := to_timestamp(cast(NEW.t_stamp as double precision) / 1000) at time zone ‘00:00’;
tablename := 'sqlth_data’ || to_char(_newdate, ‘YYYY_MM’);

PERFORM 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ‘r’
AND c.relname = _tablename
AND n.nspname = ‘public’;

IF NOT FOUND THEN
_startdate := cast(extract(epoch from date_trunc(‘month’, (_newdate))) as bigint) * 1000;
_enddate := cast(extract(epoch from date_trunc(‘month’, (_newdate + INTERVAL ‘1 month’))) as bigint) * 1000;

EXECUTE 'CREATE TABLE public.' || quote_ident(_tablename)
|| ' (PRIMARY KEY (tagid, t_stamp),'
|| ' CHECK ((t_stamp >= ' || quote_literal(_startdate)
||      ' AND t_stamp < ' || quote_literal(_enddate)
|| '))) INHERITS (public.sqlth_1_data)';

END IF;

EXECUTE ‘INSERT INTO public.’ || quote_ident(_tablename) || ’ SELECT ($1).*’
USING NEW;
RETURN NULL;

END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;[/code]

Last but not least is the trigger definition itself.

CREATE TRIGGER insbef BEFORE INSERT ON sqlth_1_data FOR EACH ROW EXECUTE PROCEDURE trg_sqlth_data_partition();

github.com/keithf4/pg_partman

Checking this out now, will report back in time.