Michael Monnerie wrote: > > DELETE FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id > FROM dbmail_messages);
This should be added to the integrity check while running dbmail-util -t. > --> DELETE 94966 > > So 95k messages were still there, nearly 3GB of wasted space! > > I looked into the description of dbmail_messages: > »dbmail_messages_physmessage_id_fkey« FOREIGN KEY (physmessage_id) > REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE > > But that constraint doesn't fire when you delete a mailbox, right? It > would only delete a message when you delete a physmessage, which is not > what happens when you delete a user. correct. > > That's why I looked into triggers, and found postgreSQL to have rules: > CREATE RULE drop_messages_with_mailbox AS ON DELETE TO dbmail_messages > DO DELETE FROM dbmail_physmessage WHERE id = OLD.physmessage_id; Evil dude. That's a simple foreign key constraint masking as a rule. There may very well be other messages that use the very same physmessage. There is no 1-1 relation between a message and the physmessage it points to. We do need a rule to take care of basic stuff like this. Try this (untested): CREATE PROCEDURAL LANGUAGE plpgsql; create function physmessage_gc() returns "trigger" as $$ begin delete from dbmail_physmessage where OLD.physmessage_id not in (select physmessage_id from dbmail_messages); return OLD; end; $$ language plpgsql; create trigger message_delete after delete on dbmail_messages for each row execute procedure physmessage_gc(); I'll be adding more stuff like that to the trunk code as we move toward 2.3.0. With mysql-5.0/postgresql-8.x/sqlite-3 we can start using rich triggers and procedures to take care of all the basic stuff that is still done with dbmail-util. _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
