March 22, 2018

PostgreSql create weekly child partition table

It seems that doing a quarterly partition is causing issues with the auto-vacum and clean up process, so instead now we are creating a single child table each week and remove the data every seven days,
so it took no time to figure it out and created the function trigger, but there should be a better way somewhere, but for now we still using inherit tables instead of actual file partitions.
Cheers,


CREATE 
OR 
replace FUNCTION weeklypartition_insert() 
returns TRIGGER language 'plpgsql' cost 100 volatile NOT leakproof AS $body$
DECLARE _partition_year VARCHAR(25);_partition              varchar(80);_IsNewChild             boolean;_current_week           varchar(4);_tablename              varchar(80);_currentdate timestamp ;BEGIN
  _currentdate := CURRENT_TIMESTAMP; 
  -- get current week 
  _current_week := date_part('week',new.insertdate)::text; ----- Week of the year (1-52) 
  -- get YYYY_MM format 
  _partition_year := to_char (new.insertdate,'YYYY')||'_'||date_part('week',new.insertdate); 
  _partition := tg_table_name || '_' || _partition_year; 
  _tablename := _partition; 
  if NOT EXISTS 
  ( 
         SELECT 1 
         FROM   pg_tables 
         WHERE  schemaname= tg_table_schema 
         AND    tablename= _partition) THEN 
  raise notice 'A partition has been created %',tg_table_schema ||'.'|| _partition; 
  _isnewchild = true; 
  execute 'create table '|| tg_table_schema ||'.'|| _partition || ' (check( to_char (insertdate,''YYYY'||'_'|| _current_week||''') = '''|| to_char (new.insertdate,'YYYY')||'_'|| _current_week||''')) INHERITS ( ParentTableName);';
endIF;EXECUTE 'insert into '|| tg_table_schema || '.' || _partition || ' SELECT(' || tg_table_schema || '.' || tg_table_name || ' ' || quote_literal(new) || ').* RETURNING PrimaryKeyId;';IF (_isnewchild) then
-- Add primary key 
EXECUTE format 
  ('ALTER TABLE '||tg_table_schema||'.%s ADD PRIMARY KEY(PrimaryKeyId)', _partition); 
-- Add indexesEXECUTE format 
  ('CREATE INDEX ix_%s_PrimarykeyID ON '||tg_table_schema||'.%s USING btree ("SequenceID")', _tablename, _tablename);EXECUTE format
  ('CREATE INDEX ix_%s_Insertdate ON '||tg_table_schema||'.%s USING btree ("insertdate")', _tablename, _tablename);ENDIF;RETURN NULL;END;$BODY$;ALTER FUNCTION weeklypartition_insert() owner TO db_owner;
grant EXECUTE ON FUNCTION weeklypartition_insert() TO db_datawriter; 
grant EXECUTE ON FUNCTION weeklypartition_insert() TO PUBLIC; 
grant EXECUTE ON FUNCTION weeklypartition_insert() TO db_executor; 
grant EXECUTE ON FUNCTION weeklypartition_insert() TO db_owner;

Contact Form

Name

Email *

Message *