Re: Update and cursor

Patrick COLLIN <patrick@felixfr.com> writes:
> I first tried a global update on each column, but I have not enough
> memory and swap to do that.

>  FOR nouvEnreg IN SELECT * FROM mfnf00 LOOP
>    nouvCoupal := 2 * nouvEnreg.coupal;
>    UPDATE mfnf00 SET coupal = nouvCoupal
>     WHERE cbase = nouvEnreg.cbase  AND
>      satel = nouvEnreg.satel  AND
>      citm8 = nouvEnreg.citm8;
>  END LOOP;

I think the problem here is not so much the UPDATEs as it is the
SELECT; IIRC, plpgsql will try to fetch the whole result of the
select into memory before it starts to run the loop.  You could
work around that, I think, by using a cursor to fetch the rows
one at a time.  But in this case, you're just coding a gratutiously
inefficient way of doing a global update: why not replace the
whole loop with

    UPDATE mfnf00 SET coupal = 2 * coupal;

which will be vastly faster as well as not having a memory issue.

            regards, tom lane