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

Reply via email to