Flexible _private_tasks clean up function

[christophemacabiau]

Feature description

Can we add an administrative function allowing to clean up the tasks table with flexibility?

export type CleanupType = "INCLUDING" | "EXCLUDING";
cleanupTasks(options: {
    tasks?: string[];
    type?: CleanupType;
}): Promise<void>

Motivating example

With #412/#415 we are able to clean up the private tables.

GC_TASK_IDENTIFIERS deletes task identifiers that don't contain any jobs.

In some cases, a task doesn't have any jobs now, but will have jobs later.
If we delete the task, when new jobs are queued, the task will be re-created with a different id, and pre-existing workers won't process those jobs.

Supporting development

I

• am interested in building this feature myself
• am interested in collaborating on building this feature
• am willing to help testing this feature before it's released
• am willing to write a test-driven test suite for this feature (before it exists)
• am a Graphile sponsor ❤️
• have an active support or consultancy contract with Graphile

[benjie]

I see this as a bug in the cleanup function; specifically this code:

worker/src/cleanup.ts

Lines 39 to 48 in 2bafad3

	if (tasks.includes("GC_TASK_IDENTIFIERS")) {
	await client.query(
	`\
	delete from ${escapedWorkerSchema}._private_tasks tasks
	where tasks.id not in (
	select jobs.task_id
	from ${escapedWorkerSchema}._private_jobs jobs
	);`,
	);
	}

Should look more like:

    const knownTaskIdentifiers = [
      // Get task identifiers from resolved tasks
      // Add to this an optional list passed in via arguments
    ];
    if (tasks.includes("GC_TASK_IDENTIFIERS")) {
      await client.query(
        `\
delete from ${escapedWorkerSchema}._private_tasks tasks
where tasks.id not in (
  select jobs.task_id
  from ${escapedWorkerSchema}._private_jobs jobs
)
and tasks.name <> all($1::text[]);`,
       [knownTaskIdentifiers]
      );
    }

What do you think? If you agree, please feel free to go ahead and implement it.

Specifically I think we should change the signature of the function:

 export async function cleanup(
   compiledOptions: CompiledOptions,
-  tasks: CleanupTask[] = ["GC_JOB_QUEUES", "GC_TASK_IDENTIFIERS"],
+  taskOrOptions: CleanupTask[] | CleanupOptions = {},
 ) {
+  const options = Array.isArray(taskOrOptions) ? { tasks: taskOrOptions } : taskOrOptions;
+  const { tasks = ["GC_JOB_QUEUES", "GC_TASK_IDENTIFIERS"], additionalTaskNamesToKeep = [] } = options;

[benjie]

I'm going to do this myself since I'm doing other worker changes anyway. This has always been a documented concern, I'm going to leave the full warning in place:

Caution

It is currently unsafe to run this whilst any Graphile Worker instance is running since any task identifiers for which there are no jobs queued will be deleted; when another job with that identifier is queued a new unique identifier will be generated and that won't match the internal identifiers that the running workers have cached.

-- https://worker.graphile.org/docs/admin-functions#gc_task_identifiers

[christophemacabiau]

Thanks @benjie :-)