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: 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. -- http://rob0.nodns4.us/ -- system administration and consulting Offlist GMX mail is seen only if "/dev/rob0" is in the Subject: