Well either I did something wrong or I actually had so many sequentially numbered messages to delete. Originally when I ran your query the output just gave me every id in order so I though it had to be wrong.
I just checked now on my PostgreSQL 8.0.3 and here is what I got: (My query) dbmail=# SELECT p.id, m.physmessage_id dbmail-# FROM dbmail_physmessage p dbmail-# LEFT JOIN dbmail_messages m dbmail-# ON p.id = m.physmessage_id dbmail-# WHERE m.physmessage_id IS NULL; id | physmessage_id ----+---------------- (0 rows) (Paul's query) dbmail=# SELECT id dbmail-# FROM dbmail_physmessage p dbmail-# LEFT JOIN dbmail_messages m dbmail-# ON p.id = m.physmessage_id dbmail-# WHERE m.physmessage_id IS NULL; id ---- (0 rows) So they returned the same results. I think my original query must not have returned the ID's in order, but because you were selecting only the ID they came back in order. Now I have to think about how to delete a user in the future. If I didn't want to use the dbmail-users program, is it valid for me to or did I miss a step? 1) select all the users mailboxes. 2) select all the physmessage_id's for that mailbox in dbmail_messages. 3) delete all the physmessage_id's from #2 4) Then I should be able to delete the user and have everything deleted by the cascade. Also, I currently use auto vacuum, and thanks to Larry to answering my question about the vacuum full to release the FSM. I'm not in any need for the disk space so I'll leave it, but I epxect that my DB growth should be zero for awhile. 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 18, 2006 4:31 PM To: DBMail mailinglist Subject: Re: [Dbmail] Delete accounts 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 _______________________________________________ Dbmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
