Re: One source of constant annoyance identified

"Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de> writes:
> Sorry I took so long - I attached the schema as asked.

Thanks. But I'm still unable to reproduce the memory bloat you see on
SELECTs. This seems very peculiar. You said you were running SuSE 7.3
--- how recent is that? Which glibc version is it running? (I've been
reduced to speculating about memory leakage inside libc, which is a
pretty long shot but...)

> So used the already implemented trigger to
> execute the fti-function:

> update ct_com_board_message
> set state_id=3D0
> where state_id=3D0
> and to_char(last_reply, 'yyyymmdd') between '20020301' and '20020830';

> I took a quick look at top: Even this humble query causes memory- and
> processor-load like a giant: 266M RAM, 38.3% processor time, 26.4%
> memory usage. Okay, it's calling the trigger for each row which in turn
> inserts some new tuples into ct_com_board_fti, but is it expected to
> cause so much load?

Wouldn't surprise me. Since you're using an AFTER trigger, the pending
trigger events have to be saved up for commit time, so the list of
pending events is going to grow quite large. (How many rows do you have
in ct_com_board_message, anyway? How many did that query try to
update?) This however does not explain your problem with SELECT, since
selects don't fire triggers.

Could I see the output of EXPLAIN for that problem SELECT on your
machine?

regards, tom lane