Re: DELETE eats up all memory and crashes box

"Worky Workerson" <worky(dot)workerson(at)gmail(dot)com> writes:
> On 10/6/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Well, the memory eating is easy to explain: pending-trigger-event list.

> Is there any way to tune PG to execute such a query, or am I forced to
> forgo the convenience of the "ON DELETE CASCADE" and manually delete
> the records with a subselect?

You'd have to tweak the query to not delete so many records at once.
Note that whether you have CASCADE or not is not the issue --- if you
are doing a delete in a foreign-key-referenced relation at all, you
are going to have a trigger event per deleted row no matter what the
details of the FK are.

We've had a TODO item for awhile to spill the pending-trigger-event list
to disk when it gets too big, but no one's gotten around to it, probably
because once you're in that regime performance is going to suck anyway :-(

regards, tom lane