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:
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;$_$;
