Hello Wietse,
ok, here is my problem in detail:
I pass mails to Postfix through smtpd. Postfix sends them out and returns
the queue_id. I save the queue_id to a database ("UPDATE table SET mail_id =
'...' WHERE id = ..."). in a table structured like this:
ID (INT, auto increment, indexed) - unique id for internal purpose
mail_id (VARCHAR) - the queue_id from Postfix
html (TEXT) - the html part of the message
text (TEXT) - the text part of the message
recipient (VARCHAR) - the email address of the recipient
status (VARCHAR) - the status of the email (can be sent or bounced,
when the message bounced, it saves the dsn and reason as well)
I parse the mail.log for bounces every hour. When I find a bounce in the log
I look up the mail_id in the db table and set the status to bounced ("UPDATE
table SET status = "bounced" WHERE mail_id = '...'").
So for every bounce I need two database queries:
1. The update to save the queue_id
2. The update to set the status to bounced
This is not efficient and performs badly when the table becomes bigger
because the second query takes a long time. Reasons:
1. Text compare in WHERE clause
2. no index on mail_id
I cannot set an index on the mail_id since that would slow down the
inserting process to much (believe me, I have tried that option).
So that was the problem.
And now here is what I thought would be an adequate solution:
Since I already have a unique id in the table I could use that instead of
the mail_id. It would eliminate query no. 1 and speed up query no. 2 by an
order-of-magnitude since the WHERE clause would only include one indexed
column (id).
So I thought I could find a way with Postfix to pass my internal table id to
the mail.log when a bounce is logged and then parse it after. That's why I
tried changing the queue_id to my internal id and when that didn't work
tried to set the MAIL FROM extension to the internal id but it is not logged
in the same line making the parsing unreliable (the mail from and the bounce
notification are connected through the queue_id but are logged on different
lines that could end up in different files when parsed every hour).
I hope the explanation of the problem helped to find a solution,
Best, Steffen
-----Original Message-----
From: owner-postfix-us...@postfix.org
[mailto:owner-postfix-us...@postfix.org] On Behalf Of Wietse Venema
Sent: Sonntag, 7. Oktober 2012 13:59
To: Postfix users
Subject: Re: AW: AW: How to change queue id?
Steffen Schebesta:
> Hello Witse, I really don't want to get on your nerves but as far as I
> understand using the standardized bounce messages from Postfix
Start explaining the problem. Stop talking about what you think are
solutions. Stop wasting everyone's time on this mailing list.
You have an application that generates email. You have an MTA that tries to
deliver that email. Now what is the problem? Removing obsolete addresses
from the list? Stop talking about solutions like changing the queue ID or
changing the Postfix logging.
Wietse
