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.klaver@aklaver.com