On 7/20/22 9:38 AM, Rama Krishnan wrote:
Reply to list also
Ccing list.
Hi ALL,
I have created the batch wise query but the variable is not working in
the delete.
create or replace function sports_sale() returns void as $$
declare
counter integer := 0;
row_count integer :=0;
start integer :=1;
begin
SELECT sale_start_count INTO start FROM sale_delete_counter ORDER BY
ID DESC LIMIT 1;
SELECT sale_loop_counter INTO counter FROM sale_delete_counter ORDER
BY ID DESC LIMIT 1;
SELECT count(*) INTO row_count FROM sports_sale_archive;
SELECT ceil(row_count/10000) INTO row_count;
while counter < row_count loop
raise notice 'Counter %', counter;
delete from sports_sale where id in (select id from
sports_sale_archive WHERE id between start and start+9999);
counter := counter + 1;
start :=start+10000;
INSERT INTO
sale_delete_counter(sale_start_count,sale_loop_counter)
VALUES(start,counter);
exit when counter>5;
end loop;
end;$$ language plpgsql;
1) I thought this was a date based deletion?
2) How do you know that the ids in "id between start and start+9999"
actually exist?
3) Could this not be simplified to something like?:
create or replace function sports_sale() returns void as $$
declare
counter integer;
begin
select count(*) into counter from sports_sale_archive where
<date/id> between <start> and <end>;
while counter > 0 loop
raise notice 'Counter %', counter;
delete from sports_sale where id in (select id from
sports_sale_archive WHERE id between <start> and <end> order by
<date/id> limit 10000);
counter := counter - 10000;
end loop;
end;$$ language plpgsql;
Not tested and should be taken as starting point as it is not entirely
clear to me what you are trying to achieve.
Here I have created the archive table based on created_date with more
one year data. i want to execute this delete query using batch wise(each
iteration 10K totally 50K records deletion per execution ). The issue
was that the variable was not working in deletion subquery.
Regards
A.Rama Krishnan
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
