Having a task run once and only once.

[paulzakin]

Let’s say I have a task called foo. Is there any way to ensure that once foo has been executed one time successfully, that it will never be run again?

I think an analogous operation would be a database migration. For example, the framework (e.g. Django) checks the migrations table and only runs the migration 0002.py if it has no record of doing so.

[ewjoachim]

Procrastinate jobs are kept in a table, much like the migration table. Here's a short run-down of how it works, with links to the relevant code.

Note also that there are things explained on this page: https://procrastinate.readthedocs.io/en/stable/discussions.html

There is a table named "procrastinate_jobs":

procrastinate/procrastinate/sql/schema.sql

Line 26 in c6e3aee

CREATE TABLE procrastinate_jobs (

A task is associated with a status:

procrastinate/procrastinate/sql/schema.sql

Lines 7 to 12 in c6e3aee

	CREATE TYPE procrastinate_job_status AS ENUM (
	'todo', -- The job is queued
	'doing', -- The job has been fetched by a worker
	'succeeded', -- The job ended successfully
	'failed' -- The job ended with an error
	);

When you defer a job, you launch this query:
https://github.com/procrastinate-org/procrastinate/blob/main/procrastinate/sql/schema.sql#L85-L87

Which will always create the row with status todo.

In order for a job to be taken and executed, it always goes through procrastinate_fetch_job:

procrastinate/procrastinate/sql/schema.sql

Lines 155 to 179 in c6e3aee

	SELECT jobs.*
	FROM procrastinate_jobs AS jobs
	WHERE
	-- reject the job if its lock has earlier jobs
	NOT EXISTS (
	SELECT 1
	FROM procrastinate_jobs AS earlier_jobs
	WHERE
	jobs.lock IS NOT NULL
	AND earlier_jobs.lock = jobs.lock
	AND earlier_jobs.status IN ('todo', 'doing')
	AND earlier_jobs.id < jobs.id)
	AND jobs.status = 'todo'
	AND (target_queue_names IS NULL OR jobs.queue_name = ANY( target_queue_names ))
	AND (jobs.scheduled_at IS NULL OR jobs.scheduled_at <= now())
	ORDER BY jobs.id ASC LIMIT 1
	FOR UPDATE OF jobs SKIP LOCKED
	)
	UPDATE procrastinate_jobs
	SET status = 'doing'
	FROM candidate
	WHERE procrastinate_jobs.id = candidate.id
	RETURNING procrastinate_jobs.* INTO found_jobs;
	RETURN found_jobs;

Simplifying this query:

WITH job as (
    SELECT * from procrastinate_jobs
       WHERE status = 'todo' and {job not blocked by another condition}
       FOR UPDATE ORDER BY id LIMIT 1
)
UPDATE procrastinate_jobs 
    SET status = 'doing'
    WHERE id = job.id

The FOR UPDATE here means that when selecting the job we're about to start, we first put a lock on this row so that future queries will not be able to operate on this row. This ensures (as far as Postgres can attest) that only a single worker will get a given job.

If the task never finishes, Procrastinate itself will not declare that the task is lost. There's currently no mechanism inside procrastinate for setting stale tasks as error, but you can create yours. There is something that will retry tasks under certain conditions if you declare you want them to retry (by default, they don't) if they finish with an exception during execution.

Once a task finishes, we set its status to failed or succeeded. There's no inner mechanism that will automatically delete finished tasks for you but, once again, you can set one up.

Of course there are a lot of ways things can go wrong which would leave the system in a state where it's impossible to know if a task has been executed or not. The exactly-once problem is famously complex to solve if not downright impossible.

If you think this is not enough, and you have both suggestions on how to improve it and the time to make PRs, I'll gladly listen. So far, anything we've envisioned for going further would be etiher architecturally unsatisfactory, too complex, or too dependent on user-code (or better implemented by client-code itself)

[paulzakin]

Your advice and explanation is more than enough for me, makes total sense. What a detailed and extensive answer! Thank you such much for taking the time to write that up.