Re: delete fails with out of memory

=?UTF-8?B?0K7Qu9C40Y8g0JTRg9Cx0LjQvdC40L3QsA==?= <yuliada@gmail.com> writes:
> I have a large database and I'm trying to execute delete on a table which
> has some related tables. The query fails with following error:
> ERROR:  out of memory
> DETAIL:  Failed on request of size 1048576.

Seems like you don't have enough memory for the list of pending AFTER
trigger events:

>     AfterTriggerEvents: 872443512 total in 839 blocks; 9152 free (5 chunks);
> 872434360 used

You didn't say exactly what the "related tables" are, but I'm going to
guess that this table is the target of one or more foreign key
references, and that the trigger events are being queued to make sure
there are no remaining references to any of the deleted rows.

Are you trying to delete the *whole* table contents, or just a lot of
the rows?  If the former, try to use TRUNCATE instead.  If the latter,
the best bet might be to drop the foreign-key constraints, do the
deletions, and then re-create the constraints.  Re-creating the
constraints will result in a bulk check that there are no invalid
references, which is probably going to be faster than the retail checks
you'd get from the triggers.  (However, if you are counting on an ON
DELETE CASCADE foreign key to clean up references for you, obviously
this won't be a good way to go.)

            regards, tom lane