Jobs: with keep_completed, success and error records outlive the rows they describe #1577

Summary

With config.jobs.keep_completed = True, a table's jobs table keeps a success record for every computed key. When upstream rows are deleted and the cascade removes the computed rows, their success records stay. jobs.refresh() does not remove them until they are older than stale_timeout (3600 s by default). Passing stale_timeout=0 skips the cleanup entirely instead of forcing it. For up to an hour, then, status counts read from the jobs table overstate what the table holds. Records with status='error' for keys that no longer exist behave the same way.

Reproduction

DataJoint 2.3.5.dev2 (master), MySQL 8.0:

import datajoint as dj

dj.config.jobs.keep_completed = True
schema = dj.Schema("scratch_jobs")

@schema
class Raw(dj.Manual):
    definition = """
    raw_id : int32
    ---
    v : float32
    """

@schema
class Proc(dj.Computed):
    definition = """
    -> Raw
    ---
    w : float32
    """

    def make(self, key):
        self.insert1(dict(key, w=2 * (Raw & key).fetch1("v")))

def counts():
    j = Proc.jobs
    return {s: len(j & f"status='{s}'") for s in ("pending", "reserved", "success", "error")}

Raw.insert([dict(raw_id=i, v=float(i)) for i in range(3)])
Proc.populate(reserve_jobs=True)
print("after populate:", "rows", len(Proc()), "jobs", counts())
(Raw & "raw_id = 0").delete(prompt=False)
print("after deleting raw_id=0:", "rows", len(Proc()), "key_source", len(Proc.key_source), "jobs", counts())
print("refresh:", Proc.jobs.refresh())
print("refresh(stale_timeout=0):", Proc.jobs.refresh(stale_timeout=0), "jobs", counts())

Output:

after populate: rows 3 jobs {'pending': 0, 'reserved': 0, 'success': 3, 'error': 0}
after deleting raw_id=0: rows 2 key_source 2 jobs {'pending': 0, 'reserved': 0, 'success': 3, 'error': 0}
refresh: {'added': 0, 'removed': 0, 'orphaned': 0, 're_pended': 0}
refresh(stale_timeout=0): {'added': 0, 'removed': 0, 'orphaned': 0, 're_pended': 0} jobs {'pending': 0, 'reserved': 0, 'success': 3, 'error': 0}

Two rows exist and three successes are recorded. After a larger delete (removing a parameter set, say), anything that reports progress from the jobs table shows completed work that no longer exists, for an hour.

Where it comes from

Job.refresh() step 3 removes only records older than stale_timeout whose keys are no longer in key_source. The age condition makes sense for pending records, which can race a key_source that is still being filled, and the setting's description says so: "Seconds before pending job is checked for staleness". It is applied to every status except ignore, though, including success and error. And stale_timeout=0 means "skip this step", so there is no way to request the cleanup now.

Proposal

In refresh(), remove success and error records whose keys are in neither key_source nor the target, regardless of age. Keep the age condition for pending, which it was written for. Neither kind of record describes anything that exists, and neither is retried. Alternatively, make stale_timeout=0 mean "no grace period" rather than "skip", and give the skip its own value (None).