On Mon, 2007-09-10 at 22:16 +0200, Michael Monnerie wrote: > On Montag, 10. September 2007 20:25 Paul J Stevens wrote: > > SELECT COUNT(*) FROM dbmail_physmessage WHERE id NOT IN > > (SELECT physmessage_id FROM dbmail_messages) > > I'm surprised because I didn't believe that there were that many > unlinked physmessages, as I have a postgres rule that should prevent > that from happening: > > CREATE RULE drop_messages_with_mailbox AS ON DELETE TO dbmail_messages > DO DELETE FROM dbmail_physmessage WHERE ((dbmail_physmessage.id = > old.physmessage_id) AND (NOT (dbmail_physmessage.id IN (SELECT > dbmail_messages.physmessage_id FROM db > mail_messages))));
I'm no sql wizard, but wouldn't that be a pretty bad performance hit? (Every time a message is deleted it does that?) I believe the physmessageblks got left around when deleting a user (eg. it deleted the users' mailboxes and messages, but not physmessages); if the above is indeed a performance drag (but maybe unnoticed on a low-volume server?), maybe you could rewrite it to act ON DELETE of the mailbox, which should happen much less often? > There must be other places than dbmail_messages deleting that create an > unlinked physmessage, or a bug in postgres? Did you manually clean up the existing cruft at the time you added the above rule? -- Jesse Norell Kentec Communications, Inc. [EMAIL PROTECTED] _______________________________________________ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
