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