Subject Re: [firebird-support] Delete causes server to h ang
Author Mark Rotteveel
Post date 2016-08-26T19:13:35Z
The rows clause does work on delete (and update) statements.

Mark


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

Hi,
 
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
 
regards,
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
    begin
        in autonomous transaction do
        begin
            delete from requests where created < :delete_all_before rows 1000;
            v_deletedcnt = row_count;
            select count(*) from requests into :TEMP_CNT;
        end
< div>    end