On 10/8/2012 3:25 AM, Steffen Schebesta wrote:
> Hello Wietse,
>
> ok, here is my problem in detail:
This isn't the answer to the question Wietse, and myself, asked you.
You've failed to understand the nature of our question 3 times now.
"10,000 ft view" means you're in an airplane at altitude 10,000 feet
looking down on the problem on the ground. All you can see is the
outline, the overview, the "big picture". You can't see any details.
We want the "big picture". Why did you create the database and what is
its purpose?
Possible answers: save the whales, win the lotto, etc
Big picture. Not details.
--
Stan
> 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
>
