Periodic tasks stop running after restart when defer record has NULL job_id

[tcortega]

Periodic tasks stop being deferred after a worker restart when the procrastinate_periodic_defers table contains records with job_id=NULL. This can happen in two scenarios:

1. Worker is stopped/killed before the deferred job is created (timestamp recorded, but job creation interrupted)
2. Using delete_jobs="successful" which deletes completed jobs, leaving orphaned defer records

On worker restart, these orphaned records block new periodic task deferrals because the worker thinks those periods were already handled.

Steps to Reproduce

1. Configure a periodic task:

import procrastinate
import asyncio

app = procrastinate.App(connector=procrastinate.PsycopgConnector())

@app.periodic(cron="*/30 * * * * *")  # Every 30 seconds
@app.task(queue="default")
async def my_periodic_task(timestamp: int):
    print(f"Running at {timestamp}")
    await asyncio.sleep(30) # simulate long living task

2. Start the worker:

await app.run_worker_async()

3. Stop the worker (Ctrl+C or kill) at any point - doesn't need to wait for task completion
4. Check the database:

SELECT * FROM procrastinate_periodic_defers;
-- Shows record with job_id=NULL

5. Restart the worker
6. The periodic task never runs again (until the next cron period passes)

Expected Behavior

Periodic tasks should continue to be deferred after worker restart. Records with job_id=NULL should be treated as incomplete deferrals and re-processed.

Actual Behavior

The worker sees the existing defer_timestamp in procrastinate_periodic_defers and assumes that period was already handled. Since job_id is NULL, there's no actual job in the queue, but the worker doesn't check for this.

Root Cause

The procrastinate_periodic_defers table tracks when periodic tasks were last deferred via defer_timestamp. The deferral process appears to be:

1. Record timestamp in procrastinate_periodic_defers
2. Create job in procrastinate_jobs
3. Update job_id in the defer record

If the worker is interrupted between steps 1 and 2 (or step 3), or if the job is later deleted, the defer record remains with job_id=NULL.

On worker startup, the periodic deferral logic checks if now > defer_timestamp + interval to decide whether to defer. However, it doesn't account for orphaned records where job_id is NULL or the referenced job no longer exists.

Workaround

Clean up orphaned records on application startup:

async with app.connector.pool.connection() as conn:
    async with conn.cursor() as cur:
        await cur.execute("""
            DELETE FROM procrastinate_periodic_defers
            WHERE job_id IS NULL
               OR job_id NOT IN (SELECT id FROM procrastinate_jobs)
        """)

Environment

• Procrastinate version: 3.x
• Python version: 3.14
• PostgreSQL version: 18