SQL Paritioning, and expiring "in the future"....

[Mark Martinec]

Bill,

> I want to give my users the ability to set a custom retention period for
> quarantined mail.  Some will want it for 2 weeks, some for 4, some for a
> year.
> 
> But of course, that makes using partitions more difficult, right? Because I
> can't just drop the partition for data that's x weeks old.
> 
> So I was thinking of something, and I can't figure out if I'm missing
> something here.  What if I store each message into a partition that is
> based on the week it is to expire.  In other words, the partition isn't
> chosen based on today's date, but on the future date of when it should
> expire....
> 
> Right now we are in ISO week 12.  If I had a customer with a 2 week
> retention period, I could store the message in the partition for ISO week
> 14.  If it was for a customer with a 4 week retention period, I could store
> it in the partition for ISO week 16.  Or course, I wrap around at the end
> of the year.
> 
> And then, instead of expiring past dates, at the end of each week, I expire
> that current week.  At the end of week 12, I expire week 12.  That way, I'm
> deleting messages when they are supposed to expire.
> 
> Anybody see any issues with this?  Too many partitions, perhaps?  Poor
> database performance because of messages being written to all sorts of
> different partitions all the time?  Any mysql experts want to weigh in?

I think it would work.

You could use something like the following in amavisd.conf,
assuming you would add a field 'retention' into a policy SQL table,
where the field would be a numeric retention time interval in seconds,
or a NULL for a built-in default.


BEGIN { import Amavis::Util qw(min max) }

$partition_tag = sub {
  my($msginfo) = @_;
  my $ret_dflt = 2 * 7*24*3600;  # two weeks by default
  # retention time interval in seconds, max across all recepients of a message
  my $retention =
    max( map { my($ret,$mk) = Amavis::Lookup::lookup2(0, $_->recip_addr,
                                [ q_sql_n('retention'), $ret_dflt ],
                                Label => 'retention');
               $ret;
             } @{$msginfo->per_recip_data});
  $retention = min(90*24*3600, max(24*3600, $retention)); # clip to 1..90 days
  sprintf("%02d", iso8601_week($msginfo->rx_time + $retention));
};



Mark