pg删除大表数据(ctid模式)

 

-- DROP PROCEDURE public.sp_batch_delete_data_new_by_ctid();

CREATE OR REPLACE PROCEDURE public.sp_batch_delete_data_new_by_ctid()
 LANGUAGE plpgsql
AS $procedure$
DECLARE
   l_cnt bigint :=0;
   l_batch_size int:=10000;
   l_todocnt int;
BEGIN
    
    select count(1) 
     from tb_test01 --where id>10
    into l_cnt;
    
    if l_cnt = 0 then
       RAISE NOTICE  '表记录总数为0,跳出过程!';   
       return;
    end if;

    --l_todocnt=mod(l_cnt,l_batch_size)  + 1;
    
    l_todocnt=round(l_cnt / l_batch_size)  + 1;    


    RAISE NOTICE  '表记录总数:%',l_cnt;   
    RAISE NOTICE  '需要循环的次数:%',l_todocnt;

   -- 没有主键的使用ctid处理 
   for i in 1 .. l_todocnt loop
       RAISE NOTICE  '执行次数:%',i;    
       with t1 as (select ctid from tb_test01 limit l_batch_size) 
       delete from tb_test01 where ctid = any (array(select ctid from t1));
       commit;
  end loop;
   
   -- 使用主键id处理
--   for i in 1 .. l_todocnt loop
--       RAISE NOTICE  '执行次数:%',i;    
--       with rows as (select id from tb_test01 limit l_batch_size)
--       delete from tb_test01
--       using rows where tb_test01.id = rows.id;
--       commit;
--   end loop;

END;
$procedure$
;