Update and cursor

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