数据档案

Question

我有这样的代码来删除列"id“的行。我希望限制每个循环中删除的行数。此代码删除前25k行，下一个循环没有删除任何内容，因为它已经在第一个循环中删除了。在这里我应该做哪些修改来删除循环和退出中的100万行。这是在postgres 11号

CREATE OR REPLACE FUNCTION delFunction() RETURNS integer AS $
declare
    count numeric;
begin
    count := 0;
    LOOP
        delete from message where id in (select id from to_archive_id order by id desc LIMIT 25000);
        GET DIAGNOSTICS count = ROW_COUNT;
        raise notice 'Delete Count: %', count;             
    end loop;
END;
$ LANGUAGE plpgsql;


Delete Count: 25000
Delete Count: 0
Delete Count: 0

Answer 1

首先，这个函数不工作，您指定了RETURNS integer，然后一个接一个循环地返回什么都不返回(无论如何，我不认为它是必要的，我只想做returns void)

第二，它生成无限循环，因为没有指定要完成的条件，例如使用WHILE。

第三，您必须移动到下一个it包，例如使用偏移量，但也可以将列is_deleted boolean添加到to_archive_id表中，并在删除行的同时对其进行更新。

 CREATE OR REPLACE FUNCTION delFunction() RETURNS integer AS $
    declare
        count numeric;
        i integer = 0; -- iteration counter used in offset
    begin
        count := 1;
        WHILE count > 0 LOOP
            delete from message where id in 
        (select id from to_archive_id order by id desc LIMIT 25000 offset i * 25000);
            GET DIAGNOSTICS count = ROW_COUNT;
            raise notice 'Delete Count: % || Loop no. %', count, i; 
        i = i+1;
        commit; -- commit to take some advantage of doing this in loop
        end loop;
        return i;
    END;
    $ LANGUAGE plpgsql;

V2

alter table to_archive_id add column is_deleted boolean;

CREATE OR REPLACE procedure delProcedure() AS $
    declare
        count numeric;
        i integer = 0; -- iteration counter used in offset
    begin
        count := 1;
        WHILE count > 0 LOOP
           with del as ( delete from message where id in 
        (select id from to_archive_id where is_deleted is not true order by id LIMIT 25000)
        returning id)
            update to_archive_id set is_deleted = true where id in (select id from del);
            GET DIAGNOSTICS count = ROW_COUNT;
            raise notice 'Delete Count: % || Loop no. %', count, i; 
        i = i+1;
        commit; -- commit to take some advantage of doing this in loop
        end loop;
    END;
    $ LANGUAGE plpgsql;

    call  delProcedure()