Re: massive memory allocation until machine crashes

On Wed, Feb 21, 2007 at 08:35:40PM +0100, Alexander Elgert wrote:
> Yes, there are five FOREIGN keys in this table:

<snip>

There's your problem. You've got a trigger set to run after every
delete, and you've got them set to wait until the end of the
transaction. So postgres has to delete all the tuples while
maintaining a list of the deleted tuples so that at the end it can run
the trigger a few million times.

Possibly something like (not sure about this):

SET ALL CONSTRAINTS IMMEDIATE;

or some such may avoid the memory usage and run the trigger straight
away.

> Foreign-key constraints:
> "visit_cont_mech" FOREIGN KEY (contact_mech_id) REFERENCES
> contact_mech(contact_mech_id) DEFERRABLE INITIALLY DEFERRED
> "visit_party" FOREIGN KEY (party_id) REFERENCES party(party_id)
> DEFERRABLE INITIALLY DEFERRED
> "visit_role_type" FOREIGN KEY (role_type_id) REFERENCES
> role_type(role_type_id) DEFERRABLE INITIALLY DEFERRED
> "visit_user_agnt" FOREIGN KEY (user_agent_id) REFERENCES
> user_agent(user_agent_id) DEFERRABLE INITIALLY DEFERRED
> "visit_party_role" FOREIGN KEY (party_id, role_type_id) REFERENCES
> party_role(party_id, role_type_id) DEFERRABLE INITIALLY DEFERRED

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.