Thread: Update and cursor

I need to update 300,000 records with many tests on each before
updating.

I first tried a global update on each column, but I have not enough
memory and swap to do that.

Is it possible to do that, one row at time, whithout locking more than
one record ?

Patrick

Patrick COLLIN <patrick@felixfr.com> writes:
> I need to update 300,000 records with many tests on each before
> updating.
> I first tried a global update on each column, but I have not enough
> memory and swap to do that.

If this is 7.1, I'd be interested to see the query and the table
schemas.

If it isn't, time to update.  Older versions leak too much memory...

            regards, tom lane

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