Well I completed my delete and all went well, though I'm expecting a call about users who lost all their Email.
I had some differences from your code Paul. I had to use: INSERT INTO tmp (id, physmessage_id) SELECT p.id, m.physmessage_id FROM dbmail_physmessage p LEFT JOIN dbmail_messages m ON p.id = m.physmessage_id WHERE m.physmessage_id IS NULL; The difference being that my left join found EVERY message if I didn't include the physmessage_id as part of the output. Not really sure why, but I just made the table with 2 columns and dropped all the records in there. Am I right in my understanding that because of the ON CASCADE DELETE relation between dbmail_physmessage and dbmail_messageblks, that I can't have inconsistency between those two tables? What might be a good way to check? Does dbmail-util check that? Can someone explain why my row count in dbmail_messages and dbmail_physmessage might be off a bit? I'm guessing that I could have a single physmessage that is referenced by multiple message entries. I thought from past reading that dbmail made a copy of each message for each recipient. If not, then that's great. Last question, I would have to do a vacuum full (postgres) to reclaim the disk space of all those messages correct? I'm still trying to really understand the vacuum process in postgres. It seems so odd. Thanks for the help as always Paul. -- David A. Niblett | email: [EMAIL PROTECTED] Network Administrator | Phone: (352) 334-3400 Gainesville Regional Utilities | Web: http://www.gru.net/ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul J Stevens Sent: Friday, July 14, 2006 6:55 AM To: DBMail mailinglist Subject: Re: [Dbmail] Delete accounts Just tried this myself. Took a loooong time to run, but appears to have cleaned out about 1/3 of my storage. My innodb status suddenly shows 10G space! Nice. And it didn't lock the tables most of the time so mail services were not interrupted. Paul J Stevens wrote: > Looks ok to me, but why write a special script? You can do: > > CREATE TABLE tmp (id bigint(21) not null primary key); > INSERT INTO tmp (id) SELECT id FROM dbmail_physmessage p > LEFT JOIN dbmail_messages m ON p.id = m.physmessage_id > WHERE m.physmessage_id IS NULL); > DELETE FROM dbmail_physmessage WHERE id IN (SELECT id FROM tmp); DROP > TABLE tmp; > > This will delete all physmessages that do not have a message > associated with them, taking with them all related messageblks if you > have the constraints setup correctly. > > > > Niblett, David A wrote: >> So before I do something monumentally stupid. Paul can you tell me >> if this is correct for finding my unconnected physmessages. >> >> SELECT dbmail_physmessage.id, dbmail_messages.physmessage_id >> FROM dbmail_physmessage >> LEFT JOIN dbmail_messages >> ON dbmail_physmessage.id = dbmail_messages.physmessage_id >> WHERE dbmail_messages.physmessage_id IS NULL >> >> My plan is to dump the ID's to a file and then use a simple program >> to work through them and delete them. >> >> If my calculations are correct I have about 600k messages that are >> not attached to a mailbox any more. That's about 1/3 of my Email >> store and over a year of time. >> >> Thanks. >> >> -- >> David A. Niblett | email: [EMAIL PROTECTED] >> Network Administrator | Phone: (352) 334-3400 >> Gainesville Regional Utilities | Web: http://www.gru.net/ >> >> >> -----Original Message----- >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On >> Behalf Of Paul J Stevens >> Sent: Tuesday, July 11, 2006 3:37 AM >> To: DBMail mailinglist >> Subject: Re: [Dbmail] Delete accounts >> >> >> >> >> Niblett, David A wrote: >>> Sorry, I didn't remember, that I did create a foreign key constraint >>> on a new column in dbmail_aliases connecting it back to user_idnr. >>> >>> By adding that column, I was able to retain complete functionality. >>> Now I admit that my internal requirement is that you can't have an >>> dbmail_alias entry if you don't have a dbmail_users entry. I made a >>> placeholder user and tie all my standalone aliases to it. I suppose I >>> could have used user_idnr #1 (the internal user), but that doesn't >>> seem like a good idea. >>> >>> As for the messages, I think that is also removed (this is v2.0.10). >>> >>> CREATE TABLE dbmail_messages ( >>> ... >>> mailbox_idnr INT8 REFERENCES dbmail_mailboxes(mailbox_idnr) >>> ON DELETE CASCADE, >>> physmessage_id INT8 REFERENCES dbmail_physmessage(id) >>> ON DELETE CASCADE, >>> ... >> David, >> >> You're reading this the wrong way. The statement above says that if >> you delete a physmessage all associated messages are deleted. >> Deleting messages does *not* affect related physmessages. >> >>> Since the dbmail_messages keys to the dbmail_mailboxes, and to >>> dbmail_physmessages (same with dbmail_messageblks), I believe that it >>> will cascade all the way down. >> No it doesn't. And that's a known bug (#305). >> >>> When I delete a user, I never see any orphaned blocks when I run >>> dbmail-util. >> Because it turns out dbmail-util does not check for them properly; >> contrary to my earlier statement. >> > -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
