On 11:25 am 07/18/08 Richard Huxton <dev@archonet.com> 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.