1

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
6
  • 2
    "I want to limit the number of rows deleted in each loop" Why? They are all going to be deleted in the same transaction anyway (as you are currently doing it anyway), so what is the point of doing it in a loop rather than just deleting them all in one statement?
    – jjanes
    Commented Dec 18, 2022 at 0:28
  • @jjanes if i delete millions of rows in single transaction, thought it would affect the transaction log while the db is very active.
    – Falcon
    Commented Dec 18, 2022 at 18:15
  • 1
    No, deleting millions or billions of rows in a single statement does not affect the transaction log any more than doing it painfully slowly in chunks. Commented Dec 19, 2022 at 8:57
  • @LaurenzAlbe thank you for the confirmation. since you mentioned "any more", Is this always the case or a feature introduced in one of the pg version. my prod db is pg 11, about to migrate to pg14
    – Falcon
    Commented Dec 19, 2022 at 13:55
  • 1
    I meant "a big transaction is no more expensive than many small ones". This behavior hasn't changed in the last decades. Commented Dec 19, 2022 at 17:41

1 Answer 1

2

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()
3
  • Thank you. I tried the v2 which matches my criteria. but i ended up receiving the error listed here . I removed the commit within the function and also tested with psql + autocommit on, STILL the changes are not committing to the database when delete+update happen within the function. Running this delete+update manually with autocommit on saves the changes to the disk.
    – Falcon
    Commented Dec 18, 2022 at 20:19
  • Sorry I messed up, commits are possible in procedures, I updated V2 code.
    – sh4rkyy
    Commented Dec 19, 2022 at 9:26
  • np. thank you for the update.
    – Falcon
    Commented Dec 22, 2022 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.