On Samstag, 23. Juni 2007 Michael Monnerie wrote: > DELETE FROM dbmail_physmessage WHERE id NOT IN > (SELECT physmessage_id FROM dbmail_messages); > > it took 808609.896 ms to run, still quite long, but much faster than > the previous statement :-). The VACUUM ANALYZE afterwards shrinked a > lot of things down.
dbmail doesn't delete old physmessages when a user is deleted. Is it true that when deleting a user with "dbmail-users", it looks into deleting physmessages with mailboxes? Or does it leave the same mess as when you just DELETE FROM dbmail_users where userid = 'olduser'; ? I deleted a customer, but found that all his messages were stil there. So I did the same thing as some days ago: DELETE FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id FROM dbmail_messages); --> 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. 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; Will that rule do the correct thing? mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0676/846 914 666 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F 1C6F E6B0 // Keyserver: www.keyserver.net Key-ID: 1C6FE6B0
signature.asc
Description: This is a digitally signed message part.
_______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
