Subject Re: [firebird-support] Delete causes server to h ang
Author Mark Rotteveel
The rows clause does work on delete (and update) statements.


----- Bericht beantwoorden -----
Van: "'livius' liviuslivius@... [firebird-support]" <>
Aan: <>
Onderwerp: [firebird-support] Delete causes server to hang
Datum: vr, aug. 26, 2016 19:30

your statement is incorrect
select count(* )
should by after autonomous transaction commit not inside it
if it is in the same transaction then garbage collector cannot remove versions
and also i do not suppose that
delete from requests where created < :delete_all_before rows 1000;
is correct – i do not beleve that rows affect this query but i can be wrong
i supopose that it process all records not 1000
Karol Bieniaszewski
Sent: Friday, August 26, 2016 10:27 AM
Subject: Re: [firebird-support] Delete causes server to hang

Hey Tim
The code does not do anything active, but we run backup 4 times a day. 
I have tried batching the delete, so that one batch runs in a autonomous transaction, and made at count on the table after each batch, but with no luck. The idea was to reduce the number of deleted rows that the garbage collector should process.
v_deletedcnt = 1000;
    while (:v_deletedcnt = 1000) do
        in autonomous transaction do
            delete from requests where created < :delete_all_before rows 1000;
            v_deletedcnt = row_count;
            select count(*) from requests into :TEMP_CNT;
< div>    end