private tables cleaning

[christophemacabiau]

Summary

Is there a way to clean the private tables ?
We need to create queues during the lifetime of our application. When a new queue is created, an older queue becomes useless. Is there a way to remove it from the _private_tasks table?

Additional context

We need to target some consumers for some jobs, and the forbidden flags mechanism is not very practical (we would need the allowed flags version). The best way we have found is to create queues listened only by the target consumers (which are temporary).

[benjie]

Interesting; _private_tasks was never expected to have more than a few hundred records in it but you're saying that you name your tasks based on a prefix/suffix that relates to the consumer? I can see how that would work, nice 👍

As for cleaning it, anything in the private tables that you do is done at your own risk and may change in a minor update, however it should be safe for you to delete any entries from _private_tasks for tasks for which there are no jobs.

delete from graphile_worker._private_tasks tasks
where not exists (
  select 1
  from graphile_worker._private_jobs jobs
  where jobs.task_id = tasks.id
);

We should definitely add a "garbage collect" method to the public interface to do this kind of tidying 👍

[christophemacabiau]

Do you think it's something you could add to the public API?
If so, do you want a PR?

[benjie]

Yes, that sounds reasonable. For now let's make it a library only method (i.e. don't put it in a migration, no database function) as this gives us more flexibility to iterate it. If you want, you could also hook it up to the CLI via e.g. graphile-worker --cleanup or similar. Mark it as /** @experimental */

Something like:

// Please don't use enums here... I hate enums in TypeScript.
type CleanupTask =
  | "GC_TASK_IDENTIFIERS"
  | "GC_JOB_QUEUES"
  | "DELETE_PERMAFAILED_JOBS";

interface WorkerUtils {
  /** @experimental */
  cleanup(options: {
    tasks?: CleanupTask[], // default: ["GC_TASK_IDENTIFIERS", "GC_JOB_QUEUES"]
  } = {}): Promise<void>
}

DELETE_PERMAFAILED_JOBS:

delete from graphile_worker._private_jobs jobs
where attempts = max_attempts
and locked_at is null;

GC_TASK_IDENTIFIERS:

delete from graphile_worker._private_tasks tasks
where not exists (
  select 1
  from graphile_worker._private_jobs jobs
  where jobs.task_id = tasks.id
);

GC_JOB_QUEUES:

delete from graphile_worker._private_job_queues job_queues
where locked_at is null and not exists (
  select 1
  from graphile_worker._private_jobs jobs
  where jobs.job_queue_id = job_queues.id
);

If enabled, be sure to run DELETE_PERMAFAILED_JOBS prior to the others since they may depend on its result.

[christophemacabiau]

Thanks for the information. I will look at this on Monday..

[benjie]

Implemented in #415

[benjie]

Released in v0.16.2

If you felt like extending this functionality further, really we shouldn't try and delete task identifiers that we know we're using - so exclude those that the current worker should understand.