DELETE Query Hang

On Nov 12, 2019, at 3:47 PM, DrakoRod <drakoflames@hotmail.com> wrote:

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?

You don’t mention which version you’re using be I had this trouble long ago on version 9 when deleting blobs (toast data). You can only wait. Or kill it and do smaller batches.

I understand, yep is a 9.5 version, Can I use query like this to execute
smaller batches?:

DELETE FROM my_table
WHERE id IN (
SELECT id
FROM logtable
LIMIT 10
);

Thanks

-----
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On Nov 12, 2019, at 4:01 PM, DrakoRod <drakoflames@hotmail.com> wrote:

I understand, yep is a 9.5 version, Can I use query like this to execute
smaller batches?:

DELETE FROM my_table
WHERE id IN (
SELECT id
FROM logtable
LIMIT 10
);

Thanks

I think you still want to qualify as you had previously.. id <> 230 or something.

I’m not saying this is faster, in total, but you will get through!

På tirsdag 12. november 2019 kl. 23:47:18, skrev DrakoRod <
drakoflames@hotmail.com <mailto:drakoflames@hotmail.com>>:
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
<https://wiki.postgresql.org/wiki/Lock_Monitoring&gt;

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