mpp动态新增和删除分区的定时任务创建

动态创建分区的存储过程

CREATE OR REPLACE FUNCTION "spiderman"."prc_job_add_partition"()

  RETURNS "pg_catalog"."void" AS $BODY$ DECLARE 

dateName VARCHAR2(110);

dateEnd VARCHAR2(110);

p_sql VARCHAR2(200);

BEGIN

    select concat('DAY' , TO_CHAR(sysdate + 1, 'YYYYMMDD')) into dateName;

    select TO_CHAR(sysdate + 2, 'YYYYMMDD') into dateEnd;

    dbms_output.put_line (  dateName ) ;

    dbms_output.put_line (  dateEnd ) ;

    p_sql := 'ALTER TABLE public.motorvehicle_first_shoot ADD PARTITION '||dateName||' VALUES LESS THAN (DATE '''||dateEnd||''' )';

    dbms_output.put_line(p_sql);

    execute immediate p_sql;

END$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

创建定时任务

call dbms_job.submit('call spiderman.prc_job_add_partition(); ', sysdate, 'interval ''1 day''', :a);

动态删除过期分区的存储过程

CREATE OR REPLACE FUNCTION spiderman.prc_job_remove_partition()

 RETURNS void

 LANGUAGE plpgsql

 NOT FENCED

AS $$ DECLARE 

dateName VARCHAR2(110);

p_sql VARCHAR2(200);

BEGIN

    select concat('DAY' , TO_CHAR(sysdate - 150, 'YYYYMMDD')) into dateName;

    dbms_output.put_line (  dateName ) ;

    p_sql := 'ALTER TABLE public.motorvehicle_first_shoot DROP PARTITION '||dateName||'';

    dbms_output.put_line(p_sql);

    execute immediate p_sql;

END$$

/

创建定时任务

call dbms_job.submit('call spiderman.prc_job_remove_partition(); ', sysdate, 'interval ''1 day''', :a);

查询定时任务

select job,dbname,start_date,last_date,this_date,next_date,broken,status,interval,failures,what from user_jobs;

 取消定时任务

call dbms_job.broken(1,true);

查看所有分区

select * from PG_PARTITION;