GaussDB(DWS)使用存储过程生成批量处理表分区膨胀语句

1.存储过程语句

create or replace procedure get_partition_dead_tuples(tablename text)
as
declare
cursor c1 is select oid,relname from pg_partition where parentid=(select parentid from pg_partition where relname=tablename) and parttype='p';
var1 bigint;
var2 bigint;
var3 bigint;
var4 text;
begin
  open c1;loop
  fetch c1 into var1,var4;
  exit when c1%notfound;
  select pg_stat_get_partition_dead_tuples into var2 from pg_stat_get_partition_dead_tuples(var1);
  select pg_stat_get_partition_tuples_deleted into var3 from pg_stat_get_partition_tuples_deleted(var1);
  dbms_output.put_line(' ');
  dbms_output.put_line(tablename||' partition '||var4||' dead_tuples '||var2);
  dbms_output.put_line(tablename||' partition '||var4||' tuples_deleted '||var3);
  dbms_output.put_line('select * from pg_size_pretty(pg_partition_size('''||tablename||''','||''''||var4||'''));');
  dbms_output.put_line('analyze '||tablename||' partition ('|| var4||');');
  dbms_output.put_line('vacumm full '||tablename||' partition ('|| var4||');');
end loop;
-- dbms_output.put_line('select * from hstore_full_merge('''||tablename||''');');
close c1;
end;
/
复制

不需要的可使用**–**注释掉，或自定义添加新语句。

2.调用存储过程

（1）少量分区的表可直接前台调用

 call get_partition_dead_tuples('student_grade');
复制

（2）分区比较多可生成到文件，批量编辑后进行执行

[omm@gsdb01 ~]$ gsql -d testdb -p25308 -c "call get_partition_dead_tuples('student_grade')" &>/tmp/test.txt
[omm@gsdb01 ~]$ cat /tmp/test.txt 
复制

只保留可执行语句:

testdb=# \e
testdb$# /
CREATE PROCEDURE
testdb=# \p
create or replace procedure get_partition_dead_tuples(tablename text)
as
declare
cursor c1 is select oid,relname from pg_partition where parentid=(select parentid from pg_partition where relname=tablename) and parttype='p';
var1 bigint;
var2 bigint;
var3 bigint;
var4 text;
begin
  open c1;loop
  fetch c1 into var1,var4;
  exit when c1%notfound;
  select pg_stat_get_partition_dead_tuples into var2 from pg_stat_get_partition_dead_tuples(var1);
  select pg_stat_get_partition_tuples_deleted into var3 from pg_stat_get_partition_tuples_deleted(var1);
  dbms_output.put_line(' ');
 -- dbms_output.put_line(tablename||' partition '||var4||' dead_tuples '||var2);
 -- dbms_output.put_line(tablename||' partition '||var4||' tuples_deleted '||var3);
  dbms_output.put_line('select * from pg_size_pretty(pg_partition_size('''||tablename||''','||''''||var4||'''));');
  dbms_output.put_line('analyze '||tablename||' partition ('|| var4||');');
  dbms_output.put_line('vacuum full '||tablename||' partition ('|| var4||');');
end loop;
-- dbms_output.put_line('select * from hstore_full_merge('''||tablename||''');');
close c1;
end;
复制

testdb=#  call get_partition_dead_tuples('student_grade');
 
select * from pg_size_pretty(pg_partition_size('student_grade','gym'));
analyze student_grade partition (gym);
vacuum full student_grade partition (gym);
 
select * from pg_size_pretty(pg_partition_size('student_grade','phys'));
analyze student_grade partition (phys);
vacuum full student_grade partition (phys);
 
select * from pg_size_pretty(pg_partition_size('student_grade','history'));
analyze student_grade partition (history);
vacuum full student_grade partition (history);
 
select * from pg_size_pretty(pg_partition_size('student_grade','math'));
analyze student_grade partition (math);
vacuum full student_grade partition (math);
 get_partition_dead_tuples 
---------------------------
 
(1 row)
复制

对表分区进行analyze及vacuum full

–结束