RE: AW: AW: How to change queue id?
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
Re: AW: AW: How to change queue id?
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
Re: AW: AW: How to change queue id?
Steffen Schebesta: 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 parse the mail.log for bounces every hour. When I find a bounce in the log You are re-inventing an old wheel: it's called list washing. This functionality is present in modern mailing list managers. One popular solution is to encode your unique ID in the SMTP MAIL FROM address, as an address extension. The unique ID includes the recipient, and perhaps information about the message such as a sequence number. Postfix reports all errors to the SMTP MAIL FROM address, as do many other RFC-compliant MTAs. This not only works for mail that bounces while Postfix delivers it (the status=bounced is logged on your server), this also works for mail that bounces AFTER Postfix delivers it (the status=bounced is logged on some REMOTE server). Wietse
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
Re: AW: AW: How to change queue id?
Steffen Schebesta: Using Wietse's first approach (adding a custom id to the MAIL FROM address as an extension) I have tried to output the sender's address in the same line of the mail.log as the bounce message. To this end, YOU specify the sender address AT MAIL SUBMISSION TIME, instead of tinkering with Postfix source which is not supported. Wietse
Re: AW: AW: How to change queue id?
Am 06.10.2012 um 23:22 schrieb Wietse Venema wie...@porcupine.org: Steffen Schebesta: Using Wietse's first approach (adding a custom id to the MAIL FROM address as an extension) I have tried to output the sender's address in the same line of the mail.log as the bounce message. To this end, YOU specify the sender address AT MAIL SUBMISSION TIME, instead of tinkering with Postfix source which is not supported. Steffen Schebesta: Sorry that I didn't make it clear. Of course I set the sender at submission time. All I want is to output the sender now when the bounce happens in the log together with the recipient address. This is unnecessary. And I repeat that tinkering with Postfix source code is not supported. Postfix returns a standardized bounce message with the old queue id AND the bounced recipient AND the sender-address-with-your-identier and the Message-ID and more. You don't need to parse logfiles. Wietse
