GreenPlum 触发执行vacuum

场景描述

• 问题：GreenPlum中，vacuum只能自己管理自己的表，并且不能在函数或者匿名程序块中动态进行vacuum
• 思路：设计一个触发表，将需要做vacuum的表名插入到该触发表中，在linux配置定时任务，每隔1分钟扫描该触发表，对没有执行的过的vacuum执行，然后回写状态。
• 都在gpload用户下操作的

touch_vacuum_table_record

drop table if exists touch_vacuum_table_record;
create table touch_vacuum_table_record (
	 touchid int primary key
	,touchuser varchar(64)
	,schemaname varchar(64)
	,tablename varchar(64)
	,status varchar(7) default 'wait'
	,touchsql varchar(32767)
	,errmsg text
	,createtime timestamp default current_timestamp
	,begintime timestamp
	,endtime timestamp
) distributed by (touchid);
comment on table touch_vacuum_table_record is '触发执行 vacuum analyze 表的记录';
comment on column touch_vacuum_table_record.touchid is $$select nextval('req_touchid_vacuum');$$;
comment on column touch_vacuum_table_record.touchuser is $$触发用户$$;
comment on column touch_vacuum_table_record.schemaname is $$模式名$$;
comment on column touch_vacuum_table_record.tablename is $$表名$$;
comment on column touch_vacuum_table_record.status is $$状态，wait(等待)、running(运行中)、run(运行完毕)$$;
comment on column touch_vacuum_table_record.touchsql is $$等待运行的sql$$;
comment on column touch_vacuum_table_record.errmsg is $$报错信息$$;
comment on column touch_vacuum_table_record.createtime is $$触发时间$$;
comment on column touch_vacuum_table_record.begintime is $$运行开始时间$$;
comment on column touch_vacuum_table_record.endtime is $$运行结束时间$$;

wait_touch_vacuum_table_record

create or replace view wait_touch_vacuum_table_record as 
select touchid,touchsql,
	$$update gpload.touch_vacuum_table_record set status = 'running',begintime = current_timestamp where status = 'wait' and touchid = $$||touchid||$$;$$ as beforesql
	,'update gpload.touch_vacuum_table_record set status = ''run'',errmsg = ''$'||'{ERRMSG}'||''',endtime = current_timestamp where status = ''running'' and touchid = '||touchid||';' as aftersql
from touch_vacuum_table_record
where status = 'wait';

req_touchid_vacuum

create sequence gpload.req_touchid_vacuum
	increment by 1
	minvalue 1
	maxvalue 9223372036854775807
	start 1;

hastable

create or replace function hastable(table_name character varying, schema_name character varying default ("current_user"())::character varying(64))
	returns boolean
	language plpgsql
as $function$
/*
 * 作者 : v-yuzhenc
 * 功能 : 判断表是否存在，不存在则返回false，存在返回true
 * table_name : 表名
 * schema_name : 模式名，默认当前用户
 * */
declare 
	existbj int;  --存在标记
	ret boolean := false;  --返回值
begin
	select 1
	into existbj
	from pg_tables 
	where tablename = lower(table_name)
		and schemaname = lower(schema_name)
	limit 1;
	if existbj = 1 then 
		ret := true;
	end if;
	return ret;
end;
$function$
;
grant execute on function hastable(varchar,varchar) to public;

insert_touch_vacuum_table_record

create or replace function insert_touch_vacuum_table_record(
	 touch_user varchar
	,schema_name varchar
	,table_name varchar
)
	returns void
	language plpgsql
	security definer
as $function$ 
begin 
	insert into gpload.touch_vacuum_table_record (
		 touchid
		,touchuser
		,schemaname
		,tablename
		,touchsql
	)
	values (
		nextval('gpload.req_touchid_vacuum')
		,touch_user
		,schema_name
		,table_name
		,'vacuum analyze "'||schema_name||'"."'||table_name||'";'
	);
end;
$function$;
grant execute on function insert_touch_vacuum_table_record(varchar,varchar,varchar) to public;

sp_vacuum

create or replace function sp_vacuum(
	 tablename varchar
	,schemaname varchar default user::varchar
)
	returns void
	language plpgsql
as $function$ 
/* 作者 : v-yuzhenc
 * 功能 : 触发执行vacuum analyze 模式.表名 （最多延迟1分钟）
 * tablename : 需要 vacuum analyze 的表名
 * schemaname : 需要 vacuum analyze 的表名所在的模式名
 * */
declare 
	p_tablename varchar := lower(tablename);
	p_schemaname varchar := lower(schemaname);
	p_user varchar := user::varchar;
begin
	--表名为空
	if tablename is null then 
		raise exception '表名不能为空！';
	end if;
	--自己的表自己管理
	if p_schemaname <> p_user then 
		raise exception '只能自己的表自己管理';
	end if;
	--先判断表是否存在
	if hastable(p_tablename,p_schemaname) = false then 
		raise exception '%.% 表不存在！',p_schemaname,p_tablename;
	end if;
	--插入数据
	perform gpload.insert_touch_vacuum_table_record(
		p_user,p_schemaname,p_tablename
	);
end;
$function$
;
grant execute on function sp_vacuum(varchar,varchar) to public;

vacuum_analyze_tb.sh

#! /bin/bash

source /usr/local/greenplum-db/greenplum_path.sh

while true ; do
        V_SQL=`echo 'select beforesql,aftersql,touchsql from gpload.wait_touch_vacuum_table_record limit 1;' | psql etl -At`
        if [ -z "${V_SQL}" ];then
                exit 0
        fi
        #前置sql更新状态
        BEFORESQL=`echo ${V_SQL} | awk -F '|' '{print $1}'`
        #实际执行的sql
        TOUCHSQL=`echo ${V_SQL} | awk -F '|' '{print $3}'`
        #先更新状态
        UPDATEBJ=`echo ${BEFORESQL} | psql etl -At`
        if [ "${UPDATEBJ}" = "UPDATE 1" ]; then
            ERRMSG=`echo ${TOUCHSQL} | psql etl -At`
            #后置sql更新状态
            AFTER=`echo ${V_SQL} | awk -F '|' '{print $2}'`
            AFTER=$(echo "$AFTER" | sed "s/\${ERRMSG}/${ERRMSG}/g")
            psql etl -At <<SQL
$AFTER
\q
SQL
            sleep 1
        fi
done

配置定时调度

crontab -e
*/1 * * * * /home/gpadmin/gpshell/vacuum_analyze_tb.sh