Opened 12 years ago

Closed 9 years ago

#496 closed enhancement (fixed)

Add date-based expiry to the AWL table via maiadbtool's --prune-awl option

Reported by: rjl Owned by: rjl
Priority: normal Milestone: post-1.0.3 triage
Component: Perl scripts Version: 1.0.2
Severity: normal Keywords: maiadbtool.pl awl expiry
Cc:

Description

SpamAssassin's AWL table does not have a built-in expiry mechanism, so it will grow without bound. The --prune-awl feature of maiadbtool.pl will expire entries that have fewer than a given number of occurrences, which helps to weed out the one-off entries that take up space, but it would be more effective to add an age-based expiry criterion.

An auto-updating timestamp column can be added to the AWL table, such that timestamps are automatically assigned to new records and existing records have their timestamps updated whenever other columns are changed (e.g. with an "ON UPDATE" clause for MySQL, or a "TRIGGER" for PostgreSQL). This requires no changes to SpamAssassin, as the timestamps are managed entirely by the database.

From there, maiadbtool.pl should only need to look for another (optional) parameter to --prune-awl, specifying the minimum age (in days) of records to delete.

Change History (5)

comment:1 Changed 12 years ago by rjl

  • Status changed from new to assigned

For MySQL:

 ALTER TABLE awl
 ADD COLUMN lastupdate
 TIMESTAMP NOT NULL
 DEFAULT CURRENT_TIMESTAMP
 ON UPDATE CURRENT_TIMESTAMP;

comment:2 Changed 12 years ago by rjl

For PostgreSQL:

 ALTER TABLE awl
 ADD COLUMN lastupdate
 TIMESTAMP NOT NULL;

 CREATE FUNCTION awl_stamp() RETURNS trigger AS $awl_stamp$
   BEGIN
     NEW.lastupdate := current_timestamp;
     RETURN NEW;
   END;
 $awl_stamp$ LANGUAGE plpgsql;

 CREATE TRIGGER awl_stamp BEFORE INSERT OR UPDATE ON awl
   FOR EACH ROW EXECUTE PROCEDURE awl_stamp();

comment:3 Changed 10 years ago by mortonda@…

  • Milestone changed from 1.0.3 to post-1.0.3 triage

I'm uncomfortable making this change so close to a release when it might have a performance impact...

comment:4 Changed 9 years ago by rjl@…

  • Resolution set to fixed
  • Status changed from assigned to testing

Implemented in [1533].

comment:5 Changed 9 years ago by rjl@…

  • Status changed from testing to closed
Note: See TracTickets for help on using tickets.