From: | dinesh kumar <dineshkumar02(at)gmail(dot)com> |
---|---|
To: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2 |
Date: | 2016-01-18 22:44:07 |
Message-ID: | CALnrH7oQH33SuE0mcvLuJbiEsnZPHafVxcxP5FLSZrVh1sqCbA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
On Mon, Jan 18, 2016 at 1:37 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
wrote:
> I've created a function that allows me to do an huge update.
>
> But I need to limit this function. I need to do 50k rows (example) and
> then stop it. After that I need to continue from the rows that I've
> stopped... I'll have to modify the call function *select batch_number()* as
> well.
>
> How can I do that? Using for?
>
> The function below is already working, but, the table has 40m rows. And
> it's taking years.
>
>
Do you need to run the function on any Staging(Not Production). I mean, do
you want to run this batch processes on a single transaction.
If not, I had the similar problem, where I needed to implement a function,
which we can run in multiple sessions. I ran this function in one of the BI
servers, where we have around 5 Million records.
Find this
<http://manojadinesh.blogspot.com/2015/07/parallel-operations-with-plpgsql_9.html>link
about the implementation details.
If your question was about "Using Loops", then please ignore my comments.
FUNCTION:
>
> CREATE or REPLACE FUNCTION batch_number()
> RETURNS INTEGER AS $$
> declare
> batch_num integer;
> offset_num integer;begin
> offset_num = 0;
> batch_num = 1;
>
> while (select true from gorfs.nfs_data where batch_number is null limit 1) loop
> with ids(id) as
> (
> select
> file_id
> from
> gorfs.nfs_data
> order by
> file_id
> offset offset_num
> limit 1000
> )
> update
> gorfs.nfs_data
> set
> batch_number = batch_num
> from ids
> where file_id = ids.id;
>
> offset_num = offset_num + 1000;
> batch_num = batch_num + 1;
> end loop;
>
> return batch_num;end
> $$ language 'plpgsql';
>
>
--
Regards,
Dinesh
manojadinesh.blogspot.com
From | Date | Subject | |
---|---|---|---|
Next Message | drum.lucas@gmail.com | 2016-01-18 22:48:49 | Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2 |
Previous Message | drum.lucas@gmail.com | 2016-01-18 22:34:33 | Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2 |