On Thu, 30 Sep 2004 20:46:48 -0700 (PDT), Sumbry][ <[EMAIL PROTECTED]> wrote: > > 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:
<snip SQL session> > 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. > IMHO, I don't think we can add a foreign key in the physmessage table. When a message is deleted, the physmessage should *not always* be deleted. If there are two messages with the same physmessage, things would break! The first thing that came to mind was using a trigger which checks, on every deletion of a message, if the associated physmessage is still associated with other messages. If not, it should be deleted. Of course, there's no such thing as triggers in MySQL.. *sigh* The code in db_delete_message() does the check I mentioned above. However, this function is not called when the database does the deletion on its own (of course). > 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. You're completely right. This should be a function in dbmail-utils. Ilja