Thread: Message-ID as unique key?
Hi, I am futzing around with Andrew Stuarts "Catchmail" program that stores emails into a postgresql database. I want to avoid inserting the same email more than once... (pieces of the email actually get emplaced into several tables). Is the "Message-ID" header field a globally unique identifer? I eventually want to have a cron job process my inbox and don't want successive cron tasks to keep re-entering the same email :) Jerry
No, it's not a global unique identifier. In fact you cannot even be sure it will always be there in all mails, depending on your mail processing chain. Most of the email clients will add one, and most of the mail transfer agents will also add one if missing, but there's no general rule of how to create the mail id. Usually it will be unique for the specific instance of the user/transfer agent which generates it, as it serves for exact this purpose, to identify the mail instance on the agent instance, but nothing more. So: don't use it as a unique identifier. Cheers, Csaba. On Tue, 2004-10-12 at 17:01, Jerry LeVan wrote: > Hi, > I am futzing around with Andrew Stuarts "Catchmail" program > that stores emails into a postgresql database. > > I want to avoid inserting the same email more than once... > (pieces of the email actually get emplaced into several > tables). > > Is the "Message-ID" header field a globally unique identifer? > > I eventually want to have a cron job process my inbox and don't > want successive cron tasks to keep re-entering the same email :) > > Jerry > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On Tue, 2004-10-12 at 17:01, Jerry LeVan wrote: > Hi, > I am futzing around with Andrew Stuarts "Catchmail" program > that stores emails into a postgresql database. > > I want to avoid inserting the same email more than once... > (pieces of the email actually get emplaced into several > tables). > > Is the "Message-ID" header field a globally unique identifer? Its supposed to be that, yes. Unless you meet a broken client or MTA. > I eventually want to have a cron job process my inbox and don't > want successive cron tasks to keep re-entering the same email :) Why not a script which receives the mail in behalf of the user? Time to run some spam checkers too. Regards Tino
Jerry LeVan <jerry.levan@eku.edu> writes: > Hi, > I am futzing around with Andrew Stuarts "Catchmail" program > that stores emails into a postgresql database. > > I want to avoid inserting the same email more than once... > (pieces of the email actually get emplaced into several > tables). > > Is the "Message-ID" header field a globally unique identifer? It is intended to be, but since it is generated by either the MUA or the first MTA that sees the message, there is a lot of scope for broken software to screw things up. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
On Tue, 12 Oct 2004 11:01:08 -0400, Jerry LeVan <jerry.levan@eku.edu> wrote:
> Hi,
> I am futzing around with Andrew Stuarts "Catchmail" program
> that stores emails into a postgresql database.
>
> I want to avoid inserting the same email more than once...
> (pieces of the email actually get emplaced into several
> tables).
>
> Is the "Message-ID" header field a globally unique identifer?
>
> I eventually want to have a cron job process my inbox and don't
> want successive cron tasks to keep re-entering the same email :)
In terms of Internet mail? Answer is... almost.
The idea is that each mail has an unique Message-ID, but there are
cases when few "different" mails get same Message-ID. Such can be
the case with mailing lists, like the one you are reading right now.
Suppose you are "crosssending" a message, telling:
To: pgsql-general@postgresql.org
Cc: linux-kernel@kernel.org
the message will arrive here and a copy will be sent to each mailing list.
Then these twin messages will be processed by mailing list software,
subjects will have [something] prepended in case of pgsql-general,
the linux-kernel will have custom "signature" at the end of a message,
pgsql-general will have "TIPS" as a signature.
Then suppose you are subscribed to both lists. You will receive both
messages (which look slightly different) but with same Message-ID.
Oh, and if you store a "Sent-mail" in same/similar fold^H^H^H^Htable,
be warned that when this message comes back from pgsql-general or
most any other mailing list it will have the same Message-ID.
So... I think you might want to discard messages with duplicate
Message-IDs (loosing one of lkml- or pgsql-general- list, whichever
comes later), but you should do it silently. Mail should not be rejected
or you're risking getting bounced of the mailing list.
HTH, HAND,
Dawid
> I am futzing around with Andrew Stuarts "Catchmail" program > that stores emails into a postgresql database. > > I want to avoid inserting the same email more than once... > (pieces of the email actually get emplaced into several > tables). > > Is the "Message-ID" header field a globally unique identifer? I think you're looking for RFC 2822 (http://www.faqs.org/rfcs/rfc2822.html). I seem to recall that one of the rfc's listeda time limit of two years for uniqueness, though I'm at a loss to find which one at the moment. Pertinent sections: 3.6.4. Identification fields Though optional, every message SHOULD have a "Message-ID:" field. <snip> The message identifier (msg-id) itself MUST be a globally unique identifier for a message. The generator of the message identifier MUST guarantee that the msg-id is unique. There are several algorithms that can be used to accomplish this. Since the msg-id has a similar syntax to angle-addr (identical except that comments and folding white space are not allowed), a good method is to put the domain name (or a domain literal IP address) of the host on which the message identifier was created on the right hand side of the "@", and put a combination of the current absolute date and time along with some other currently unique (perhaps sequential) identifier available on the system (for example, a process id number) on the left hand side. Using a date on the left hand side and a domain name or domain literal on the right hand side makes it possible to guarantee uniqueness since no two hosts use the same domain name or IP address at the same time. Though other algorithms will work, it is RECOMMENDED that the right hand side contain some domain identifier (either of the host itself or otherwise) such that the generator of the message identifier can guarantee the uniqueness of the left hand side within the scope of that domain. Regards, Bruce Ritchie
Well, this is for my personal mail...I think I will probably give it a try. The program can log errors so in the few cases that might occur I think I can "manufacture" my own message id. I guess that the bottom line is that if it exists it is unique... Jerry On Oct 12, 2004, at 11:25 AM, Doug McNaught wrote: > Jerry LeVan <jerry.levan@eku.edu> writes: > >> Hi, >> I am futzing around with Andrew Stuarts "Catchmail" program >> that stores emails into a postgresql database. >> >> I want to avoid inserting the same email more than once... >> (pieces of the email actually get emplaced into several >> tables). >> >> Is the "Message-ID" header field a globally unique identifer? > > It is intended to be, but since it is generated by either the MUA or > the first MTA that sees the message, there is a lot of scope for > broken software to screw things up. > > -Doug > -- > Let us cross over the river, and rest under the shade of the trees. > --T. J. Jackson, 1863 >
On Tue, Oct 12, 2004 at 11:01:08AM -0400, Jerry LeVan wrote: > Hi, > I am futzing around with Andrew Stuarts "Catchmail" program > that stores emails into a postgresql database. > > I want to avoid inserting the same email more than once... > (pieces of the email actually get emplaced into several > tables). > > Is the "Message-ID" header field a globally unique identifer? Not a postgresql related issue, but, yes Message-ID: is, by definition, a globally unique identifier. If there are two messages with the same Message-ID then the sender is asserting that those two messages are identical. See RFC 2822 section 3.6.4. You will sometimes see a message generated without a Message-ID at all, but that will usually have had a Message-ID added by some MTA along the delivery route. If your MX doesn't add Message-IDs when missing then you may well see incoming email without Message-IDs (mostly spam). In practice there are varying levels of competence in implementation of Message-ID generation, so you'll very rarely see syntactically incorrect Message-IDs that may, in theory, clash. > I eventually want to have a cron job process my inbox and don't > want successive cron tasks to keep re-entering the same email :) I wouldn't try and use Message-ID as a primary key, though. Give yourself a serial field. I don't use Message-ID at all in my postgresql-based mailstore. Instead I use a maildir style spool directory for incoming mail and the processes that import those spooled messages into the mailstore use standard maildir techniques for locking the message on disk, writing it to the DB, moving it atomically from the new/ to the cur/ directory, then commiting the database write. I've pumped millions of emails through this in production with no problems. Cheers, Steve
Jerry LeVan writes > I eventually want to have a cron job process my inbox and don't > want successive cron tasks to keep re-entering the same email :) That's the hard way to do it, it's easier to route messages to individual files. BTW I'm doing just that in a GPL'ed project, see the URL in my sig if you're interested. It also comes with a GUI to access the mail in the database. -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Tue, 2004-10-12 at 17:11, Csaba Nagy wrote: > No, it's not a global unique identifier. [snip] Hey, you know what ? Good that I read this thread :D I'm in the process of writing a Java SMTP server, and had no clue about this rule... although I have read a few times the relevant RFC. It's true that currently I don't touch the mail, I just use internal IDs which I don't care if they are not globally unique, but if those IDs would have made it to the final product in some mails, there would have been some chance for collisions... as some other poster said, there is a good chance of non-conforming MTAs to be out there. Cheers, Csaba.
On Tue, Oct 12, 2004 at 08:50:30AM -0700, Steve Atkins wrote: > > Is the "Message-ID" header field a globally unique identifer? > > Not a postgresql related issue, but, yes Message-ID: is, by > definition, a globally unique identifier. If there are two > messages with the same Message-ID then the sender is asserting > that those two messages are identical. See RFC 2822 section 3.6.4. Except that they usually recommend adding the domain as part of the message-id. This is where your problem comes in. A mail server hiding behind a NAT firewall that's a relay and doesn't receive incoming mail directly, is not going to have a domain. So you'll get things like localhost.localdomain or something completely fake. Indeed, two messages in this thread have this. Secondly, clients creating their own message-ids are not always that great. For example, Outlook, uses the computer name as the domain. They're not likely to be unique worldwide either. You'd still have to be pretty unlucky to get two the same though. It's possible Microsoft mashes the ethernet MAC ID in there too. The answer is, it very very close to unique, but not really guarenteed. All mail systems I'm aware of generate their own ID's anyway, look through the received headers... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Csaba Nagy <nagy@ecircle-ag.com> writes: > So: don't use it as a unique identifier. > > On Tue, 2004-10-12 at 17:01, Jerry LeVan wrote: > > > > Is the "Message-ID" header field a globally unique identifer? > > [Isn't it awfully confusing to put your answers before the thing you're responding to?] Let me suggest a higher-level view of your design decision here. You shouldn't use the message-id as a unique identifier on the general principle that it is untrusted network data. You can never trust entities outside your control to behave the way you expect, even if there's an authority to back you up. They could be deficient or even hostile. Even if you don't trust the message-id generated by external users to provide any particular semantics it can still be useful. You can allow clients to request a particular message by message-id for example. Just make sure you apply the regular security checks and don't assume that because they know the message-id they must have access to the message. And make sure your regression tests include testing the case of having thousands of messages with identical message-ids, even though that should never arise in practice if everything works the way it's supposed to. -- greg