Huge amount of memory consumed during transaction

Hi,

I'm running into a problem with PostgreSQL 8.2.4 (running on 32 bit Debian Etch/2x dual core C2D/8GB mem). The thing is that I have a huge transaction that does 2 things: 1) delete about 300.000 rows from a table with about 15 million rows and 2) do some (heavy) calculations and re-insert a litlte more than 300.000 new rows.

My problem is that this consumes huge amounts of memory. The transaction runs for about 20 minutes and during that transaction memory usage peaks to about 2GB. Over time, the more rows that are involved in this transaction, the higher the peak memory requirements.

Lately we increased our shared_buffers to 1.5GB, and during this transaction we reached the process memory limit, causing an out of memory and a rollback of the transaction:

BEGIN
DELETE 299980
ERROR: out of memory
DETAIL: Failed on request of size 4194304.
ROLLBACK
DROP SEQUENCE

real 19m45.797s
user 0m0.024s
sys 0m0.000s

On my development machine, which has less than 2GB of memory, I can not even finish the transaction.

Is there a way to tell PG to start swapping to disk instead of using ram memory during such a transaction?

Thanks in advance for all help

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/