data archival postgres

Question

I have this code to delete rows with column "id". I want to limit the number of rows deleted in each loop. This code deletes the first 25k rows and the next loop deletes nothing since its already deleted at the first loop. what changes i should make here to delete 1 million rows in loop and exit. This is on 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

First of all this function doesn't work, you specified RETURNS integer but then returns nothing after loop (anyway I don't think it's necessary, I would just make returns void)

Second thing, it generates infinite loop because no condition to finish is specified, using WHILE for example.

Third you have to move to next package of ids, one example is to use offset, but you can also add column is_deleted boolean to to_archive_id table and update it together with deletion of rows.

 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()