Niblett, David A wrote: > 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.
Thats weird. On what database vendor/version did my query *not* work? I've tested mysql-4.0, mysql-4.1, postgresql-8.0, and sqlite3. I was actually planning to use this approach to re-do dbmail-util all over in this respect. > > 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? In 2.0 dbmail-util did this because of myisam support. In 2.1 integrity checks that can be done in the database are deprecated in dbmail-util. dbmail-util should/will focus only on those aspects that cannot (yet) be done in the database, but even those are expected to disappear when triggers and procedures become the baseline. > > 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. Exactly: imap copy from one mailbox to another increases the number of references to a physmessage. It does /not/ replicate the messageblks. The same goes for insertion involving multiple recipients, but only per singular insertion. No attempt is made (yet) to fingerprint individual message(part)s. > > 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. use auto-vacuum. > Thanks for the help as always Paul. you're welcome. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl
