Confusing behaviour: jobs being archived before startAfter date

[nicoburns]

Hi there,

So this is partially my own fault as I'm using a custom function to insert jobs into the job table and clearly didn't pay much attention to the keepuntil column . However, this has led to the confusing behaviour of jobs scheduled into the mid/far future (anything beyond the archiving period) being silently dropped, which was a tricky one to debug as I could see the scheduled jobs being created correctly, they were just never executed!

I was wondering whether we could remove this footgun by doing something like:

1. Using MAX(keepuntil, startafter) for the purposes of archiving
2. OR putting a trigger on the table that sets keepuntil to startAfter + some interval for inserts that have a startafter and don't have a keepuntil set.
3. OR just document it as part of the docs on startafter?

[timgit]

I would recommend you do option 4: keepuntil = startAfter + interval 'desired retention'. This is how the plans.insertJob() function works internally for deferred jobs:

 CASE
          WHEN right(keepUntilValue, 1) = 'Z' THEN CAST(keepUntilValue as timestamp with time zone)
          ELSE startAfter + CAST(COALESCE(keepUntilValue,'0') as interval)
          END as keepUntil