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@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.