Re: Batch process

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(dot)klaver(at)aklaver(dot)com