Hello, Having run into a "postgres filled all disk space" issue this weekend, we're looking at trimming indexes and things down. It looks like almost everywhere both the status # and the unique_id are checked to see if a message is being inserted or not, and hence almost all of the indexes on messages include unique_id. We have 300k messages, and using 32 char (md5 hash) unique_id's, in 8 indexs - that's right at 100MB of disk space for that column alone (and I would guess it has significant implications on memory usage too, but don't really know). So - is there any reason to not just rewrite everything to use the status flag? Ie. STATUS_INSERT, value of 5, means message is being inserted - then we can drop out all the checks for "and unique_id != ''" (in nearly every query made).
Anyone want to do that, and save me the time? :) Also, any comments on Aaron's LMTP patch? I've not looked at it myself. But if I change a lot of this, it'll just make a lot of work for him to get his patch caught up - if his patch gets applied to cvs, it'll make a lot more work for what I'm doing... and Ryan Butler is kind of waiting to see where things settle on that lmtp patch before he re-writes his status patch w/ more functionality. Jesse ---- Original Message ---- From: Aaron Stone <[email protected]> To: [email protected] Subject: RE: [Dbmail-dev] message status 4 Sent: Wed, 18 Jun 2003 12:27:45 -0700 (PDT) Actually, if you take a look at my lmtp/sorting patch, I split off a couple of these shared functions into smaller files, including create_unique_id(). I hope that Roel can review and comment on that patch soon (hint hint ;-) because I think it sets the code in the right direction for enhancing the delivery pipeline. You do seem to be correct that the status field isn't being used consistently, and what's worse, there are "magic numbers" scattered throughout the code. These should all be changed to defined values, such as... #define STATUS_ACTIVE 0 #define STATUS_INSERT 5 #define STATUS_DELETE 2 #define STATUS_EXPUNGE 3 Aaron On Wed, 18 Jun 2003, Jesse Norell wrote: > > Hello, > > Back to work, after a few days recovering from a > little trencher incident... > > As for the idea presented here, using a status id for > "not yet complete" messages - the pop3 server does this > already, using status 5, but imap does not - it instead > uses an empty unique_id (ie. '') to handle the same thing, > from what I've seen. I don't see any problems (eg. pop3 > session disreguards messages with unique_id=''), but it > would be a bit more efficient and less confusing to be > consistent in what a partially inserted message looks like. > > I plan on making a small util.c with a create_unique_id > function, as it needs to be included in more than just > dbmail-smtp. Seems overkill to have a dedicated file, and > there's no such "misc. utility functions" file yet, so... > > > > ---- Original Message ---- > From: Jesse Norell <[email protected]> > To: [email protected] > Subject: [Dbmail-dev] message status 4 > Sent: Tue, 10 Jun 2003 09:07:06 -0600 (MDT) > > > > > Hello, > > > > In implimenting a unique_id fix, in several issues in the past, and > > in message insertion in general, it seems appropriate to have a > > message status that basically just means 'not yet processed/complete,' > > for which I propose using message status = 4 (which isn't used > > anywhere, right?). I believe all the existing code should handle > > that without change (ie. if status is 4, it won't show in any > > mailboxes, get cleaned by maintenance, etc.), so there should be no > > migration problems unless someone uses status 4 locally (which a > > trivial db update can fix). > > > > This would make things like the "message inserted, but not yet > > filtered" race condition Aaron was going to have to address a while > > back easy - just insert all messages with status = 4, do whatever > > processing, and update to 2 when the message is fully processed. > > It would fix a race condition in the current insertion code where a > > message actually changes unique_id and another where the messages > > table entry exists, but messageblk entries do not yet (ie. if > > messages entry was inserted, then a user checks POP3 before > > messageblk is inserted, it'll hang OE waiting for data, till a > > timeout period, then error). > > > > Sound good / any objections (particularly from IC&S)? > > > > Jesse > > > > > > -- > > Jesse Norell > > jesse (at) kci.net > > > > > > _______________________________________________ > > Dbmail-dev mailing list > > [email protected] > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > > -- End Original Message -- > > > -- > Jesse Norell > jesse (at) kci.net > > > _______________________________________________ > Dbmail-dev mailing list > [email protected] > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > _______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev -- End Original Message -- -- Jesse Norell jesse (at) kci.net
