On 11:25 am 07/18/08 Richard Huxton <dev@xxxxxxxxxxxx> wrote: > I'm wondering whether it's memory usage either for the trigger > itself, or for the function (pl/pgsql?). Good point. > If you're doing something > like: INSERT INTO partitioned_table SELECT * FROM big_table > then that's not only taking place within a single transaction, but > within a single statement. Correct. I have kept decreasing work_mem and that does not seem to help. > Without being a hacker, I'd say it's entirely plausible that PG might > clean up triggers at the end of a statement meaning you would need > memory for 200million+ triggers. Sure hope that is not the case. > Alternatively, it could be a memory-leak somewhere in the pl/pgsql or > trigger code. Wouldn't have to be much to affect this particular case. Will post an strace. > What happens if you do the insert/select in stages but all in one > transaction? Will test. The data is about a year worth of data. I will try to do one month at a time, within a single transaction. A single month finishes fine. > Do you see PG's memory requirement stay constant or grow > in steps. That will show whether the memory is growing over the > duration of a statement or a transaction. Right now for the single statement/transaction (the one big process) it is growing slowly over time. It may be a leak. It seems to start growing somewhere between the 1st and 2nd hower. It seems to always be failing around 4 hours. I wrote a little process that shows the amount of free memory every 15 minutes.. I will post strace for the big process and then will try breaking the process down by month, but within a single transaction and report that later when I get some results.
