Sv: DELETE Query Hang

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

På tirsdag 12. november 2019 kl. 23:47:18, skrev DrakoRod <drakoflames@xxxxxxxxxxx>:

Hi folks!

I Have a question, in a database are a table with many files (bytea) stored
(I support this database a don't design it), but we need delete many rows
(38000 rows approx),  but I when execute query:

BEGIN;
ALTER TABLE my_file_table DISABLE TRIGGER ALL;
DELETE FROM my_file_table WHERE id_table <> 230;

This query hang... 50 minutes and the query do not finish.

Any suggestion?

 

Check for locks and blocking statements:

https://wiki.postgresql.org/wiki/Lock_Monitoring

 

You can delete in chunks like this:

 

do $_$
declare
    num_rows bigint;
begin
    loop
        delete from YourTable where id in
                                    (select id from YourTable where id < 500 limit 100);
        get diagnostics num_rows = row_count;
        raise notice 'deleted % rows', num_rows;
        exit when num_rows = 0;
    end loop;
end;$_$;

 

--
Andreas Joseph Krogh