Hello,
Here's a patch we've been running fully under pgsql for almost a
day, and all but a couple changes since last Friday, and have tested
a bit on mysql. It moves the create_unique_id() function to misc.c
and makes every place that saves a unique_id use it (which was
previously done inconsistently in various places). It also changes
all the use of the status field in messages table to be through the
following #defines:
#define STATUS_NEW 0
#define STATUS_SEEN 1
#define STATUS_DELETE 2
#define STATUS_PURGE 3
#define STATUS_UNUSED 4
#define STATUS_INSERT 5
#define STATUS_ERROR 6
There are a couple other misc. changes also, namely changes
the APOP "timestamp" in the pop3 greeting to not include the
system time and pid (though srand() is still seeded with those,
but better than giving them away in the clear), put getopt()
support in pop3d (and added -h help flag), and changed a few
places to remove messageblks before removing the message row,
so people adding constraints don't get errors (ie. if they don't
use ON DELETE CASCADE and remove the messages entry first).
This does not get rid of using an empty unique_id as a flag
that the message is being inserted - I plan on working on that
soon, and only use STATUS_INSERT status for that purpose.
Jesse
[.Note: it looks like dbmail-dev won't even allow a 94k message
through, so the patch is available for download instead of attached
to this message:
http://wedbmail.dsp-services.com/patches/dbmail-20030623-misc.patch ]
---- Original Message ----
From: Jesse Norell <dbmail-dev@dbmail.org>
To: dbmail-dev@dbmail.org
Subject: Re: [Dbmail-dev] message status 4
Sent: Mon, 23 Jun 2003 09:41:01 -0600 (MDT)
>
> 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 <dbmail-dev@dbmail.org>
> To: dbmail-dev@dbmail.org
> 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 <dbmail-dev@dbmail.org>
> > To: dbmail-dev@dbmail.org
> > 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
> > > Dbmail-dev@dbmail.org
> > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > >
> > -- End Original Message --
> >
> >
> > --
> > Jesse Norell
> > jesse (at) kci.net
> >
> >
> > _______________________________________________
> > Dbmail-dev mailing list
> > Dbmail-dev@dbmail.org
> > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> >
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
> -- End Original Message --
>
>
> --
> Jesse Norell
> jesse (at) kci.net
>
>
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
-- End Original Message --
--
Jesse Norell
jesse (at) kci.net
Here's a patch we've been running fully under pgsql for almost a
day, and all but a couple changes since last Friday, and have tested
a bit on mysql. It moves the create_unique_id() function to misc.c
and makes every place that saves a unique_id use it (which was
previously done inconsistently in various places). It also changes
all the use of the status field in messages table to be through the
following #defines:
#define STATUS_NEW 0
#define STATUS_SEEN 1
#define STATUS_DELETE 2
#define STATUS_PURGE 3
#define STATUS_UNUSED 4
#define STATUS_INSERT 5
#define STATUS_ERROR 6
There are a couple other misc. changes also, namely changes
the APOP "timestamp" in the pop3 greeting to not include the
system time and pid (though srand() is still seeded with those,
but better than giving them away in the clear), put getopt()
support in pop3d (and added -h help flag), and changed a few
places to remove messageblks before removing the message row,
so people adding constraints don't get errors (ie. if they don't
use ON DELETE CASCADE and remove the messages entry first).
This does not get rid of using an empty unique_id as a flag
that the message is being inserted - I plan on working on that
soon, and only use STATUS_INSERT status for that purpose.
Jesse
[.Note: it looks like dbmail-dev won't even allow a 94k message
through, so the patch is available for download instead of attached
to this message:
http://wedbmail.dsp-services.com/patches/dbmail-20030623-misc.patch ]
---- Original Message ----
From: Jesse Norell <dbmail-dev@dbmail.org>
To: dbmail-dev@dbmail.org
Subject: Re: [Dbmail-dev] message status 4
Sent: Mon, 23 Jun 2003 09:41:01 -0600 (MDT)
>
> 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 <dbmail-dev@dbmail.org>
> To: dbmail-dev@dbmail.org
> 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 <dbmail-dev@dbmail.org>
> > To: dbmail-dev@dbmail.org
> > 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
> > > Dbmail-dev@dbmail.org
> > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > >
> > -- End Original Message --
> >
> >
> > --
> > Jesse Norell
> > jesse (at) kci.net
> >
> >
> > _______________________________________________
> > Dbmail-dev mailing list
> > Dbmail-dev@dbmail.org
> > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> >
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
> -- End Original Message --
>
>
> --
> Jesse Norell
> jesse (at) kci.net
>
>
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
-- End Original Message --
--
Jesse Norell
jesse (at) kci.net