How can I delete related mails after a new retention rule has been created?

[larsen0815]

Let's say that I notice a huge amount of newsletter mails from multiple specific addresses that certainly don't need to be archived. I create a retention rule and further mails will be discarded. So far so good.

But what about the maybe tens of thousands of mails that have already been archived?
I don't need those, and they are filling up the database and disk space.

How can I retroactively run the retention rules?

I'm thinking of selecting one/many/all retention rules via the GUI, maybe select a time frame to consider, and then hit "run". Piler would take each mail from that time frame, and compare it against all selected retention rules, and if the rule applies, mark the mail for deletion. Maybe show the result as a list, before the user hits the "Delete" button to delete all those mails.

Piler could also show a dialog after creating a new retention rule: "Do you want to apply this new rule to all existing mails now?"

It doesn't seem possible at the moment, or is it? If not, is there another way to achieve this?

[jsuto]

In a nutshell you need to identify the metadata ids for all those emails, then set their retained column values to yesterday, and let the purge utility do its job. In case of newsletters perhaps you can identify the sender address (or maybe the message-id has a patterns) to help you identify them.

[larsen0815]

Thanks, this worked.
What is the reason for the metadata entry being set to deleted instead of deleting it from the table?

For other users:

1. Mark mails:

UPDATE metadata SET retained = 0 WHERE `from` = 'jondoe@example.com';

2. Wait for the cronjob or manually start the purge:

sudo -u piler /usr/libexec/piler/purge.sh

[jsuto]

The reason to keep the entries is to preserve the email history. You are free to remove these entries after the purge, it's your archive, whatever the regulations, compliance, etc. rules in your region allow you.