Purging Old Rows from an External Table on a Schedule

A retention job that deletes aged rows from an application database belongs in a Channel Scheduled Script rather than in message processing. The script runs on its own CRON schedule, holds no message, and reports what it did to the channel log.

Delete in batches. A single unbounded DELETE against a large table holds locks for the length of the statement and can block the application that owns the table. The loop below deletes a fixed number of rows per statement and stops when a statement affects fewer rows than the batch size, which means the last batch has been reached.

function purgeOldStudyLookupRecords(dbName, daysOld) {
    var batchSize = 5000;
    var totalDeleted = 0;
    var keepGoing = true;

    while (keepGoing) {
        var pQuery = qie.getParameterizedQuery(
            'DELETE TOP (@batchSize) FROM dbo.STUDY_LOOKUP ' +
            'WHERE CREATED_DATE < DATEADD(day, -@daysOld, GETDATE())');
        pQuery.setInt('@batchSize', batchSize);
        pQuery.setInt('@daysOld', daysOld);

        var rowsAffected = pQuery.doUpdateQuery(dbName, false);
        totalDeleted += rowsAffected;

        if (rowsAffected < batchSize) {
            keepGoing = false;
        } else {
            qie.pause(200);
        }
    }

    qie.info('Purged ' + totalDeleted + ' STUDY_LOOKUP rows older than ' + daysOld + ' days.');
}

purgeOldStudyLookupRecords('StudyDb', 15);

doUpdateQuery takes the database connection name and an optional read-only flag, and returns the number of rows affected. qie.pause takes a pause in milliseconds and gives the database a moment between batches. Set the schedule on the Scheduled Script's CRON String: 0 0 1 * * * runs the job at 1:00 AM daily. See CRON String Format for the six-field syntax.

The statement above is SQL Server syntax. DELETE TOP and DATEADD have no direct equivalent on MySQL or MariaDB, which use DELETE ... LIMIT and DATE_SUB instead.

Testing the Query Without Deleting

Confirm the cutoff selects the rows you expect before the job deletes anything. Run the same WHERE clause as a count:

var pQuery = qie.getParameterizedQuery(
    'SELECT COUNT(*) FROM dbo.STUDY_LOOKUP ' +
    'WHERE CREATED_DATE < DATEADD(day, -@daysOld, GETDATE())');
pQuery.setInt('@daysOld', 15);

qie.info('Dry run: ' + pQuery.doConditionQuery('StudyDb') + ' rows would be deleted.');

Match the call to the statement. doConditionQuery returns the first column of the first row, which is what a COUNT(*) produces. Running a SELECT through doUpdateQuery fails with the driver error A result set was generated for update. See Database Result Node Paths for what each call returns when nothing matches.

Foreign Key Constraints

A parent row cannot be deleted while a child table still references it. The database rejects the whole batch:

The DELETE statement conflicted with the REFERENCE constraint "fk_patient_order_patient_id".

Exclude the referenced rows rather than catching the error. The purge then removes everything it is allowed to remove and leaves the rest:

DELETE TOP (@batchSize)
FROM dbo.patient
WHERE created_timestamp < DATEADD(day, -@daysOld, GETDATE())
  AND NOT EXISTS (
      SELECT 1 FROM dbo.patient_order
      WHERE dbo.patient_order.patient_id = dbo.patient.patient_id);

Where the retention policy requires the child rows to go as well, delete from the child table first and the parent table second, in two separate statements.

A caught constraint error does not let the loop continue

Wrapping the batch in try and catch without a NOT EXISTS filter produces an infinite loop. The rejected batch deletes nothing, so the next pass selects the same rows and fails the same way. Either filter the blocked rows out of the statement, or stop the loop in the catch block.