Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2

On 19 January 2016 at 11:44, dinesh kumar <dineshkumar02(at)gmail(dot)com> wrote:

> 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.
>

I don't want to run this batch in a single transaction. I'm already doing
that and, as it has 40 million rows, it's taking years.

So, the new plan is:

1 - Select 50.000 rows and gives it a batch number.
2 - Select *MORE* 50,000 rows and gives it a *NEW* batch number.
3 - Select *MORE* 50,000 rows and gives it a *NEW* batch number.
4 - etc etc etc

>
> 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.
>