Message-ID as unique key?

[Jerry LeVan]

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Csaba Nagy]

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tino Wildenhain]

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Doug McNaught]

Jerry LeVan <je*********@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Dawid Kuroczko]

On Tue, 12 Oct 2004 11:01:08 -0400, Jerry LeVan <je*********@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: pg***********@postgresql.org
Cc: li**********@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Jerry LeVan]

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 <je*********@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Steve Atkins]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Daniel Verite]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Csaba Nagy]

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Martijn van Oosterhout]

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 <kl*****@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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBbAccY5Twig3Ge+YRAnbPAKDQqvl2q1YEuRsgi3RFY+ 5i3QR6dwCgpSDL
XogzUbnIARx82/f4kYITmWQ=
=qMPb
-----END PGP SIGNATURE-----

[Greg Stark]

Csaba Nagy <na**@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly