On Mon, Oct 08, 2012 at 10:25:20AM +0200, Steffen Schebesta wrote:
> ok, here is my problem in detail:
>
> I pass mails to Postfix through smtpd.
Stop here. What are you trying to do? What sort of mail? This sounds
like bulk email. Is it?
> 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
So the actual, real-world problem and goal: "I am sending bulk email,
and I need an automated means to track bounces for list maintenance"
(or list washing, as the case may be.)
Am I close?
If so, you are in luck: this is a solved problem.
http://www.postfix.org/VERP_README.html
Google the list archives for examples. "VERP" is your new search
term.
> 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,
Why do you find it so difficult to describe the actual problem and
goal? It took a whole, long discussion here, and we still have not
gotten that!
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]] 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.
--
http://rob0.nodns4.us/ -- system administration and consulting
Offlist GMX mail is seen only if "/dev/rob0" is in the Subject:
