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;