Re: [Dbmail-dev] dbmail-pop3d and order
> You can do one of: > > * put everything POP3 related within a single transaction > * save all the uidl values by querying the database once Iirc, dbmail uses this approach within a session. > * order using a sequence (like idnr) This is what Aaron's working on to make it more consistent between sessions (I think). However, it may be better to order by message_idnr alone, as ordering by status,message_idnr (as mentioned in another message) could still have some messages jumping around between pop3 sessions. -- Jesse Norell Kentec Communications, Inc.
Re: [Dbmail-dev] dbmail-pop3d and order
Just kidding, I broke my subversion client's SSL linkage. Will try to get this committed tomorrow. On Mon, 2006-09-18 at 14:40 -0700, Aaron Stone wrote: > Done. > > On Mon, 2006-09-18 at 16:14 -0400, Geo Carncross wrote: > > > Put message_idnr ASC first. Don't order by status; Consider what happens > > if another client changes the status after UIDL but before TOP? > > > > If they exclusively use POP3 clients, they'll see it "ordered by status" > > anyway (because new messages will be at the bottom). > > > ___ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] dbmail-pop3d and order
Done. On Mon, 2006-09-18 at 16:14 -0400, Geo Carncross wrote: > Put message_idnr ASC first. Don't order by status; Consider what happens > if another client changes the status after UIDL but before TOP? > > If they exclusively use POP3 clients, they'll see it "ordered by status" > anyway (because new messages will be at the bottom).
Re: [Dbmail-dev] dbmail-pop3d and order
On Mon, 2006-09-18 at 11:34 -0700, Aaron Stone wrote: > On Mon, 2006-09-18 at 14:01 -0400, Geo Carncross wrote: > > > > SELECT pm.messagesize, msg.message_idnr, msg.status, msg.unique_id > > > FROM dbmail_messages msg, dbmail_physmessage pm WHERE msg.mailbox_idnr = > > > '81' > > > AND msg.status < '2' AND msg.physmessage_id = pm.id order by status ASC > > In SVN, the query code now reads: > > snprintf(query, DEF_QUERYSIZE, > "SELECT pm.messagesize, msg.message_idnr, msg.status, " > "msg.unique_id FROM %smessages msg, %sphysmessage pm " > "WHERE msg.mailbox_idnr = '%llu' " > "AND msg.status < '%d' " > "AND msg.physmessage_id = pm.id " > "ORDER BY status, message_idnr ASC",DBPFX,DBPFX, > inbox_mailbox_idnr, MESSAGE_STATUS_DELETE); > > Please let me know if I've added message_idnr in a logical way. I could > set myself up for POP3 and mess with it, but I'm hoping that the squeeky > wheel will test the changes ;-) Put message_idnr ASC first. Don't order by status; Consider what happens if another client changes the status after UIDL but before TOP? If they exclusively use POP3 clients, they'll see it "ordered by status" anyway (because new messages will be at the bottom). -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
Re: [Dbmail-dev] dbmail-pop3d and order
On Mon, 2006-09-18 at 14:01 -0400, Geo Carncross wrote: > > SELECT pm.messagesize, msg.message_idnr, msg.status, msg.unique_id > > FROM dbmail_messages msg, dbmail_physmessage pm WHERE msg.mailbox_idnr = > > '81' > > AND msg.status < '2' AND msg.physmessage_id = pm.id order by status ASC In SVN, the query code now reads: snprintf(query, DEF_QUERYSIZE, "SELECT pm.messagesize, msg.message_idnr, msg.status, " "msg.unique_id FROM %smessages msg, %sphysmessage pm " "WHERE msg.mailbox_idnr = '%llu' " "AND msg.status < '%d' " "AND msg.physmessage_id = pm.id " "ORDER BY status, message_idnr ASC",DBPFX,DBPFX, inbox_mailbox_idnr, MESSAGE_STATUS_DELETE); Please let me know if I've added message_idnr in a logical way. I could set myself up for POP3 and mess with it, but I'm hoping that the squeeky wheel will test the changes ;-) > * order using a sequence (like idnr) This is how I want to resolve it. Aaron
Re: [Dbmail-dev] dbmail-pop3d and order
On Thu, 2006-09-14 at 01:31 -0400, Morty wrote: > On Tue, Sep 12, 2006 at 08:39:12AM -0600, Jesse Norell wrote: > > That's most likely the order in which the database returns the > > message id's .. eg. see what something like "select message_idnr > > from dbmail_messages where mailbox_idnr = " returns, it's > > probably the order you're seeing. You can turn logging up to level > > 5 to see the exact queries being run. As Mark pointed out, I > > don't think they're required to be in any order, and as such they > > likely aren't sorted (which would slightly improve performance), > > though doing so would be easy. > > For a LIST command, the query is: > > SELECT pm.messagesize, msg.message_idnr, msg.status, msg.unique_id > FROM dbmail_messages msg, dbmail_physmessage pm WHERE msg.mailbox_idnr = '81' > AND msg.status < '2' AND msg.physmessage_id = pm.id order by status ASC > > Looks like they are sorted, but by status. So the sort performance > hit is already there, it's just sorting by something > counter-intuitive. > > The RFC may not specify order, but received order seems to be the > de-facto implementation choice. Probably because POP3 became popular > in the Unix mailspool world, where email is naturally in received > order. No it doesn't specify order; but it _does_ require that message numbers remain immutable over the coarse of a session. Consider what happens if the command sequence goes: UIDL TOP 10 0 and message number 10 suddenly referred to a different message between UIDL and TOP. You can do one of: * put everything POP3 related within a single transaction * save all the uidl values by querying the database once * order using a sequence (like idnr) -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
Re: [Dbmail-dev] dbmail-pop3d and order
On Wed, Sep 13, 2006, Morty <[EMAIL PROTECTED]> said: > On Tue, Sep 12, 2006 at 08:39:12AM -0600, Jesse Norell wrote: >> That's most likely the order in which the database returns the >> message id's .. eg. see what something like "select message_idnr >> from dbmail_messages where mailbox_idnr = " returns, it's >> probably the order you're seeing. You can turn logging up to level >> 5 to see the exact queries being run. As Mark pointed out, I >> don't think they're required to be in any order, and as such they >> likely aren't sorted (which would slightly improve performance), >> though doing so would be easy. > > For a LIST command, the query is: > > SELECT pm.messagesize, msg.message_idnr, msg.status, msg.unique_id > FROM dbmail_messages msg, dbmail_physmessage pm WHERE msg.mailbox_idnr = '81' > AND msg.status < '2' AND msg.physmessage_id = pm.id order by status ASC > > Looks like they are sorted, but by status. So the sort performance > hit is already there, it's just sorting by something > counter-intuitive. > > The RFC may not specify order, but received order seems to be the > de-facto implementation choice. Probably because POP3 became popular > in the Unix mailspool world, where email is naturally in received > order. I've already changed the query in SVN to ORDER BY status, message_idnr. Please see if it gives the results you are expecting. Aaron
Re: [Dbmail-dev] dbmail-pop3d and order
On Tue, Sep 12, 2006 at 08:39:12AM -0600, Jesse Norell wrote: > That's most likely the order in which the database returns the > message id's .. eg. see what something like "select message_idnr > from dbmail_messages where mailbox_idnr = " returns, it's > probably the order you're seeing. You can turn logging up to level > 5 to see the exact queries being run. As Mark pointed out, I > don't think they're required to be in any order, and as such they > likely aren't sorted (which would slightly improve performance), > though doing so would be easy. For a LIST command, the query is: SELECT pm.messagesize, msg.message_idnr, msg.status, msg.unique_id FROM dbmail_messages msg, dbmail_physmessage pm WHERE msg.mailbox_idnr = '81' AND msg.status < '2' AND msg.physmessage_id = pm.id order by status ASC Looks like they are sorted, but by status. So the sort performance hit is already there, it's just sorting by something counter-intuitive. The RFC may not specify order, but received order seems to be the de-facto implementation choice. Probably because POP3 became popular in the Unix mailspool world, where email is naturally in received order. - Morty
Re: [Dbmail-dev] dbmail-pop3d and order
On Tue, Sep 12, 2006 at 11:26:42AM +0200, Marc Dirix wrote: > I don't think message ordering is provided in RFC? I also don't > think it is good policy to rely on message ordering. Every message > has it unique id, if you want to point out messages use it. I don't know if it's in the RFC, but the POP3 implementation I have touched ordered messages in approximately the order that they were received. POP3 clients typically preserve this order to present to users. Users expect this to work. - Morty
Re: [Dbmail-dev] dbmail-pop3d and order
I've added to the ORDER BY clause of the query in question. See if your messages appear in a more consistent order now. I think it is a good idea to return the messages in their id number order for consistency. On Tue, 2006-09-12 at 08:39 -0600, Jesse Norell wrote: > That's most likely the order in which the database returns the > message id's .. eg. see what something like "select message_idnr > from dbmail_messages where mailbox_idnr = " returns, it's > probably the order you're seeing. You can turn logging up to level > 5 to see the exact queries being run. As Mark pointed out, I > don't think they're required to be in any order, and as such they > likely aren't sorted (which would slightly improve performance), > though doing so would be easy. > > > > On Mon, Sep 11, 2006 at 03:42:56PM -0700, Aaron Stone wrote: > >> On Mon, 2006-09-11 at 18:17 -0400, [EMAIL PROTECTED] wrote: > >> > Using dbmail-pop3d, I've been having problems where email shows up > >> > out-of-sequence. I.e. I send an email with subject 1, wait for it to > >> > enter dbamil via LMTP; send an email with subject 2, and wait again; > >> > send an email with subject 3, and wait again; and then connect via > >> > POP3. The order for the three emails is not always consistent. > >> > > >> > Anyone seen this? > >> > >> Sure, it's just a matter of how/when the MTA does its delivery. > >> Transferring mail is certainly not a deterministic process ;-) > > > > It's not the MTA. I'm watching the logs, and only sending one email > > at a time. I'm waiting for each message to get completely through the > > system before sending the next one, i.e. messages like so: > > > > dbmail/lmtpd[24920]: sort.c, sort_and_deliver: message id=405091, size=854 > > is inserted > > > > I test POP3 by manually telneting to localhost 110 and using user, > > pass, list, retr, and dele commands. Starting with an empty mailbox > > and sending a few emails in relatively rapid sequence, my emails are > > showing up in reverse order -- first the most recent, then the next > > older, etc. If I don't delete messages after I read them, then the > > messages from the prior session are showing up first and then the new > > ones, again with the older messages first within each group. If I > > wait awhile, they show up in a different order, sometimes sorted as I > > would expect them. What's happening is confusing. > > > > - Morty > > > >
Re: [Dbmail-dev] dbmail-pop3d and order
That's most likely the order in which the database returns the message id's .. eg. see what something like "select message_idnr from dbmail_messages where mailbox_idnr = " returns, it's probably the order you're seeing. You can turn logging up to level 5 to see the exact queries being run. As Mark pointed out, I don't think they're required to be in any order, and as such they likely aren't sorted (which would slightly improve performance), though doing so would be easy. > On Mon, Sep 11, 2006 at 03:42:56PM -0700, Aaron Stone wrote: >> On Mon, 2006-09-11 at 18:17 -0400, [EMAIL PROTECTED] wrote: >> > Using dbmail-pop3d, I've been having problems where email shows up >> > out-of-sequence. I.e. I send an email with subject 1, wait for it to >> > enter dbamil via LMTP; send an email with subject 2, and wait again; >> > send an email with subject 3, and wait again; and then connect via >> > POP3. The order for the three emails is not always consistent. >> > >> > Anyone seen this? >> >> Sure, it's just a matter of how/when the MTA does its delivery. >> Transferring mail is certainly not a deterministic process ;-) > > It's not the MTA. I'm watching the logs, and only sending one email > at a time. I'm waiting for each message to get completely through the > system before sending the next one, i.e. messages like so: > > dbmail/lmtpd[24920]: sort.c, sort_and_deliver: message id=405091, size=854 > is inserted > > I test POP3 by manually telneting to localhost 110 and using user, > pass, list, retr, and dele commands. Starting with an empty mailbox > and sending a few emails in relatively rapid sequence, my emails are > showing up in reverse order -- first the most recent, then the next > older, etc. If I don't delete messages after I read them, then the > messages from the prior session are showing up first and then the new > ones, again with the older messages first within each group. If I > wait awhile, they show up in a different order, sometimes sorted as I > would expect them. What's happening is confusing. > > - Morty > -- Jesse Norell Kentec Communications, Inc.
Re: [Dbmail-dev] dbmail-pop3d and order
I don't think message ordering is provided in RFC? I also don't think it is good policy to rely on message ordering. Every message has it unique id, if you want to point out messages use it.
Re: [Dbmail-dev] dbmail-pop3d and order
On Mon, Sep 11, 2006 at 03:42:56PM -0700, Aaron Stone wrote: > On Mon, 2006-09-11 at 18:17 -0400, [EMAIL PROTECTED] wrote: > > Using dbmail-pop3d, I've been having problems where email shows up > > out-of-sequence. I.e. I send an email with subject 1, wait for it to > > enter dbamil via LMTP; send an email with subject 2, and wait again; > > send an email with subject 3, and wait again; and then connect via > > POP3. The order for the three emails is not always consistent. > > > > Anyone seen this? > > Sure, it's just a matter of how/when the MTA does its delivery. > Transferring mail is certainly not a deterministic process ;-) It's not the MTA. I'm watching the logs, and only sending one email at a time. I'm waiting for each message to get completely through the system before sending the next one, i.e. messages like so: dbmail/lmtpd[24920]: sort.c, sort_and_deliver: message id=405091, size=854 is inserted I test POP3 by manually telneting to localhost 110 and using user, pass, list, retr, and dele commands. Starting with an empty mailbox and sending a few emails in relatively rapid sequence, my emails are showing up in reverse order -- first the most recent, then the next older, etc. If I don't delete messages after I read them, then the messages from the prior session are showing up first and then the new ones, again with the older messages first within each group. If I wait awhile, they show up in a different order, sometimes sorted as I would expect them. What's happening is confusing. - Morty
Re: [Dbmail-dev] dbmail-pop3d and order
On Mon, 2006-09-11 at 18:17 -0400, [EMAIL PROTECTED] wrote: > Using dbmail-pop3d, I've been having problems where email shows up > out-of-sequence. I.e. I send an email with subject 1, wait for it to > enter dbamil via LMTP; send an email with subject 2, and wait again; > send an email with subject 3, and wait again; and then connect via > POP3. The order for the three emails is not always consistent. > > Anyone seen this? Sure, it's just a matter of how/when the MTA does its delivery. Transferring mail is certainly not a deterministic process ;-) Aaron
[Dbmail-dev] dbmail-pop3d and order
Using dbmail-pop3d, I've been having problems where email shows up out-of-sequence. I.e. I send an email with subject 1, wait for it to enter dbamil via LMTP; send an email with subject 2, and wait again; send an email with subject 3, and wait again; and then connect via POP3. The order for the three emails is not always consistent. Anyone seen this? - Morty
