Custom partition maintenance functions in Greenplum

The partitioning table in Greenplum is very useful for boosting query performance in the case of large data volumes, but Greenplum itself does not provide a tool for automatic maintenance of partitioned tables, where we use Greenplum's PL/SQL custom two partition table to automatically maintain stored procedures ( can also be a function).

Before you create a stored procedure, you first create a view of the details of the partition table, which you can see in the previous post. Since there are multiple tables in the business that need to be partitioned, and the types of partitioning fields are not the same, we first create a dictionary table that records the partition type for each table, as follows:

create table op_tb_partition  (  tb_name varchar ( DEFAULT NULL),   timetype varchar (8)  default null) distributed by  (tb_name); select *  from op_tb_partition;                                                                                                                  tb_name           |  timetype -----------------------------+---------- nl_app_action_error_trace   | unixtime nl_mob_app_ Anr_data         | usertime nl_mob_app_error_trace_test  | datetime (3 rows)

  Create a stored procedure to add a partition, the escape character for single quotes in Greenplum is two single quotes, the detailed code is as follows:

Create or replace function add_partition_day ()  returns text as$ $declare  tb_options record;declare curr_partvarchar (8);d Eclare max_part varchar;d Eclare  part_name varchar (9);d Eclare end_part varchar (8);d eclare start_range int; declare end_range int;declare x int;beginfor tb_options in select *  from   op_tb_partition group by tb_name,timetype loopif tb_ options.timetype =  ' datetime '  thenselect replace (substring (current_date +  interval  ' 7 day '  from 1 for 10), '-', ')  as date into max_ Part;select substring (Max (partition_name)  from 2 for 8)  into curr_part  from  v_gp_range_partition_meta a join pg_class b on a.table_ Name=b.oid where b.relname =tb_oPtions.tb_name;select date (Max_part)  - date (Curr_part)  into x;while x >  0 loopselect replace (substring (date (curr_part)  + interval  ' 1 day '   FROM 1 FOR 10), '-', '  as date into curr_part;select replace (substring (Date (Curr_part)  + interval  ' 1 day '  from 1 for 10), '-', ')  as  date into end_part;select  ' P '  | |  curr_part into part_name;execute  ' alter table  '  | |   tb_options.tb_name | |   '  add partition  '  | |  part_name | |   '  start  ('  | |  curr_part | |   "::d ate)  end  ('  | |  end_part | |   '::d ate) '; x = x-1;end loop;end_part =  '; end_range = 0;elsif  tb_options.timetype =  ' Usertime '  thenselect replace (substring (current_date + interval  ' 7 day '  from 1 for 10), '-', ')  as  date into max_part;select substring (Max (partition_name)  from 2 for 8)  into curr_part from  v_gp_range_partition_meta a join pg_class b  on a.table_name=b.oid where b.relname =tb_options.tb_name;select date (Max_part)  - date (Curr_part)  into x;while x > 0 loopselect replace ( SUBSTRING (date (curr_part)  + interval  ' 1 day '  from 1 for 10), '-', ')  as date into curr_part;select replace (substring (date (curr_part)  + interval   ' 1 day '  from 1 for 10), '-', ')  as date into end_part;select  nl_to_timestamp (Date (Curr_part))  into start_range;select nl_to_timestamp (date (End_part))  into end_range;select  ' P '  | |  curr_part into part_name;execute  ' alter table  '  | |   tb_options.tb_name | |   '  add partition  '  | |  part_name | |   '  start  ('  | |  start_range | |   ':: int)  end  ('  | |  end_range | |   ':: int) ';  -- alter table tb_options.tb_name add partition part_name  start  (Start_range::int)  end  (end_range::int); X = x-1;end loop;end_part  =  ';end_range = 0;elsif tb_options.timetype =  ' unixtime '  thenselect  replace (substring (current_date + interval  ' 7 day '  from 1 for 10) , '-', ')  as date into max_part;select substring (max (partition_name)  from 2  FOR 8)  into curr_part from  v_gp_range_partition_meta a join  pg_claSs b on a.table_name=b.oid where b.relname =tb_options.tb_name;select date ( Max_part)  - date (Curr_part)  into x;while x > 0 loopselect  Replace (substring (date (curr_part)  + interval  ' 1 day '  from 1 for 10), '- ', ')  as date into curr_part;select replace (substring (date (curr_part)  +  interval  ' 1 day '  from 1 for 10), '-', ')  as date into end_ Part;select unix_timestamp (Date (Curr_part))  into start_range;select unix_timestamp (Date ( End_part)  into end_range;select  ' P '  | |  curr_part into part_name;execute  ' alter table  '  | |   tb_options.tb_name | |   '  add partition  '  | |  part_name | |   '  start  ('  | |  start_range | |   ':: int)  end  ('  | |  end_range | |   ':: int) ';  -- alter table tb_options.tb_name add partition part_name  start  (Start_range::int)  end  (end_range::int); X = x-1;end loop;end_part  =  ';end_range = 0;end if;end loop; return  ' OK '; end;$ $LANGUAGE   Plpgsql;

Create a stored procedure that deletes a partition, where the data is stored for 3 months, with detailed code as follows:

Call the declared stored procedure as follows:

This article from "Brave forward, resolutely left" blog, declined reprint!

Custom partition maintenance functions in Greenplum