>> 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.=20
is that multiple simultaneous calls? maybe you are over committing
your sort memory. If you can reproduce the out of memory behavior
from a single backend that argues for a memory leak.
Single backend.
p.s. you can create one function temp_tables_init(), called after
connection to backend (and not in a transaction) which creates all
temp tables for the process. If you do that and remember to truncate
the tables (not drop), you can use non-dynamic pl/pgsql calls.
That's an interesting option... would make our PHP frontend a bit more
complex thou. Obviously performance would be better in this case as query
plans will be pre-compiled.
Peter
