Previously, I figured that because of postgres cascading deletes and the fact that dbmail used foreign keys and cascading deletes, if I removed an account from dbmail, everything associated w/that account would get removed as well.
However, that is not the case. I've gone through several CVS upgrades and revisions, and could not figure out why I had more than 80,000 messageblk entries. Upon further examination: select count(*) from dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id FROM dbmail_messages); count ------- 41999 select count(*) from dbmail_physmessage; ------- 42537 select count(*) from dbmail_messages; count ------- 538 Surprise, if I run the first query and replace select with delete, it will remove all the offending orphan rows and leave me w/exactly 538 entries which is exactly what I should have. On further examination, it looks like we should add a forieng key constraint on dbmail_physmessage, HOWEVER because it's already NOT NULL and a serial column and I am not at all sure how the delivery chain works (what tables get added too in what order) I don't want to add a cascading delete to this table w/o someone more knowledgable viewing the consequences. either way, this should definetely be added to dbmail-utils to remove orphaned dbmail_physmessage entries. As it works now, removing the entry from dbmail_physmessage will also remove any connected links in dbmail_messageblks. (If you're curious about the high numbers, we used to run a free POP/IMAP/Forwarding service. We recently got rid of all the mailboxes and converted everyone to forwarding accounts - w/gmail giving out 1 gig accounts we decided to just let people forward to an account w/them, or yahoo, or another isp - you get the point. All the old accounts got nuked, and htis is what we were left with). ----- "Good? You're not good. You just know how to hide, how to lie." - Tony Montana [EMAIL PROTECTED]
