2 Replies Latest reply on May 7, 2006 8:51 PM by Andrew Oliver

    Documentation oversite (or maintenance that is required for

    Andrew Oliver Master

      I never got around to documenting this for 1.0M4, but there is some maintenance that you should do in a script on a semi-regular basis.

      For PostgreSQL:

      delete from pg_largeobject where loid not in (select bodyid from messagedata)
      delete from storeitemmetadata where pid not in (select loid from pg_largeobject)

      Should be done as either postgres or a user with "CREATEUSER"

      For DBs similar to MySQL or HQL (using the paged store -- tablenames may not be exact in case/existence of _'s):

      delete from paged_store where id not in (select m.bodyid from messagedata m)

      delete from paged_store_pages where blobid not in (select b.id from paged_store_pages b

      delete from storeitemmetadata s where s.pid not in (select b.id from paged_store b)

      For DBs similar to Oracle using JDBC3 (table names may not be exact):

      delete from blob_mail_store where id not in (select m.bodyid from messagedata m)

      delete from storeitemmetadata s where s.pid not in (select b.id from blob_mail_store b

      The issue is that we don't expunge large objects regularly. I intended this for performance but I've not yet decided whether the tradeoffs on the various dbs of doing this irregularly (or in a script or something) outweigh the benefits. For the next release we'll likely make this self executing as part of the expunge/close/dropcarrier or pop/quit/dropcarrier... Figuring their overhead should be moderately low and at an "unimportant" point from a performance perspective.

      I'll document all this in the wiki when I get time or around the 1.0M5 release.

      Thanks,

      Andy