Differences

This shows you the differences between two versions of the page.

--- schema_0_9_10_upgrade_notes [2008/11/02 23:46] – created daniel
+++ schema_0_9_10_upgrade_notes [2008/11/03 22:00] – daniel
@@ Line 77: / Line 77: @@
 Note that ''raw_mail'' contain references to large objects, which are not automatically purged on delete. [[http://www.postgresql.org/docs/8.3/static/vacuumlo.html|vacuumlo]] can be used to purge large objects that are no longer referenced.
+=== mail_tags ===
+Check entries in ''mail_tags'' that refer to deleted messages
+<code sql>
+SELECT h.mail_id FROM mail_tags h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;
+</code>
+Delete entries from ''mail_tags'' that refer to deleted messages
+<code sql>
+DELETE FROM mail_tags WHERE mail_id IN
+ (SELECT h.mail_id FROM notes h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);
+</code>
+Check entries in ''mail_tags'' that refer to deleted tags
+<code sql>
+SELECT h.mail_id FROM mail_tags h LEFT JOIN tags t ON h.tag=t.tag_id WHERE t.tag_id IS NULL;
+</code>
+Delete entries from ''mail_tags'' that refer to deleted tags
+<code sql>
+DELETE FROM mail_tags WHERE mail_id IN
+ (SELECT h.mail_id FROM mail_tags h LEFT JOIN tags t ON h.tag=t.tag_id WHERE t.tag_id IS NULL);
+</code>
+=== mail ===
+Remove references to non-existing mailboxes (does not delete messages)
+<code sql>
+UPDATE MAIL set mbox_id=NULL WHERE mail_id IN
+  (SELECT mail_id FROM mail m LEFT JOIN mailboxes mx ON m.mbox_id=mx.mbox_id WHERE mx.mbox_is IS NULL);
+</code>
+=== tags_words ===
+Delete entries from ''tags_words'' that refer to deleted tags
+<code sql>
+DELETE FROM tags_words WHERE tag_id IN
+ (SELECT h.tag_id FROM tags_words h LEFT JOIN tags t ON h.tag_id=t.tag_id WHERE t.tag_id IS NULL);
+</code>
+Delete entries from ''tags_words'' that refer to deleted words
+<code sql>
+DELETE FROM tags_words WHERE word_id IN
+ (SELECT h.word_id FROM tags_words h LEFT JOIN words w ON h.word_id=w.word_id WHERE w.word_id IS NULL);
+</code>
+=== inverted_word_index ===
+Delete entries from ''inverted_word_index'' that refer to deleted words
+<code sql>
+DELETE FROM inverted_word_index WHERE word_id IN
+ (SELECT h.word_id FROM inverted_word_index h LEFT JOIN words w ON h.word_id=w.word_id WHERE w.word_id IS NULL);
+</code>