> >> 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
