problem delete row in postgresql with commit [duplicate]

Question

I have a table who make a lot of time to delete some rows (32000 record by country). I create a function. I split the delete and use Loop instruction and commit but i have an error when i execute the function

My function DeleteMeasure :

CREATE OR REPLACE FUNCTION public.DeleteMeasure(
in_id_country integer)
RETURNS integer
LANGUAGE 'plpgsql'

COST 100
VOLATILE AS $BODY$ DECLARE
err_pgsql_message   TEXT;
_err_message        TEXT;
nbdelete INTEGER=0;
BEGIN   

LOOP
    
        DELETE FROM public.measure WHERE id_measure IN (SELECT id_measure FROM public.measure WHERE id_country=in_id_country LIMIT 1000);
        EXIT WHEN NOT FOUND;
        COMMIT;
END LOOP;

    
RETURN nbdelete;
END
$BODY$;

Select * from DeleteMeasure();

Return an error on COMMIT line. Can i have some help?

can you send the entire function? Why do you need the delete statement in a loop? — Vania Oliveira
– Vania Oliveira, Commented Mar 21, 2024 at 18:42
You can't commit in a function, only in a procedure. And for a procedure you use call instead of select. One of these things is wrong, you don't use a procedure or you don't use the proper syntax to invoke the procedure. — Frank Heikens
– Frank Heikens, Commented Mar 21, 2024 at 18:44
Not seeing how you are deleting 32000 records when you have limit=1000? — Adrian Klaver
– Adrian Klaver, Commented Mar 21, 2024 at 20:39

Lajos Arpad · Accepted Answer · 2024-03-21 23:59:20Z

0

You should try the simple

delete from measure where id_country = 10;

Make sure that is_country is indexed before you delete. Selecting repeatedly 1000 items until there are no more matches slows down the process.

If you need to remove the majority of your records, then you could save the records you want to keep into another table whose schema is copied from this one, remove this table and then rename the other table.

answered Mar 21, 2024 at 23:59

Lajos Arpad

80.3k42 gold badges122 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

problem delete row in postgresql with commit [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related