|
I have stored proc that retrieves a bunch of data,
stores it in temp table, computes all sorts of totals/averages/whatnots from the
temp table, and inserts results in another table. It works fine (except I don't
like wrapping all SQL statements in 'execute'), but multiple calls to that proc
from another procedure causes excessive memory usage (upwards of 400M), and
server eventually runs out of swap space. I believe this is because PG
caches transactions in RAM, and this particular one is a bit too big.
Is there any way to make PG write transaction to
disk instead of caching it? Alternatively I would be happy to have full control
over transactions inside stored procedure - like not wrapping proc call inside
implicit transaction, and doing my own 'BEGIN' and 'COMMIT' inside the proc...
but of course PG won't suppport it
Right now only way to make this work is to build
SQL script with hundreds of calls to the stored proc in question, and run it
directly.
Any ideas?
Peter
|
