Foreign key constraint error when deleting queue

[OyugoObonyo]

I am using pgBoss v10 on node version 22 and postgresql 16. I have tried deleting a queue existing in my DB by invoking the .deleteQueue("myQueueName") method. However, the queue isn't being deleted and I get this error:

Uncaught:
error: update or delete on table "queue" violates foreign key constraint "q_fkey" on table "j40563acd0ea36d7a20c7d0e7f76db3b9ed117babebab6a4149a0781a"

 length: 654,
  severity: 'ERROR',
  code: '23503',
  detail: 'Key (name)=(test-trial) is still referenced from table "j40563acd0ea36d7a20c7d0e7f76db3b9ed117babebab6a4149a0781a".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'SQL statement "WITH deleted as (\n' +
    '        DELETE FROM pgboss.queue\n' +
    '        WHERE name = queue_name\n' +
    '        RETURNING partition_name\n' +
    '      )\n' +
    '      SELECT partition_name from deleted"\n' +
    'PL/pgSQL function pgboss.delete_queue(text) line 5 at SQL statement',
  schema: 'pgboss',
  table: 'j40563acd0ea36d7a20c7d0e7f76db3b9ed117babebab6a4149a0781a',
  column: undefined,
  dataType: undefined,
  constraint: 'q_fkey',
  file: 'ri_triggers.c',
  line: '2621',
  routine: 'ri_ReportViolation'

Am I strictly limited to manually deleting queues from my DB instead of invoking the deleteQueue API?

[OyugoObonyo]

Update: I've since been able to delete the queue by first deleting all the queue's jobs before deleting the queue itself. I've now tried setting up a queue with the same name and it has very unpredictable behaviour: sometimes it works, sometimes it doesn't. On times it doesn't work, I get the error like so:

error: relation "je95d8092e95eab390e11d733b07e00753bff292667445dbb4f0fe298" already exists

I've tried manually deleting all the referenced partitions like so:

DELETE FROM pg_inherits
USING pg_class parent, pg_class child
WHERE pg_inherits.inhparent = parent.oid
AND pg_inherits.inhrelid = child.oid
AND parent.relname = 'job'
AND child.relname <> 'j3f168501ed9816b51a9f5765e0742e1eb034ab6bf72c9ae3f3a975e3';

I've been able to successfully delete the partitions but somehow the relation already exists error keeps on showing up when I try creating a queue with the same name to one that I already deleted?

2 questions @timgit :

1. How can I rectify this behaviour?
2. Is there a more graceful way to handle queue deletions?

[timgit]

There is a test in the suite that does exactly this. It deletes and re-creates a queue. Was this perhaps a unique failure use case?

[OyugoObonyo]

Update: Must've been an issue on my side because I tried creating queues concurrently:

  private async persistDefinedQueues(definedQueues: Queue[]): Promise<void> {
    try {
      const storedQueues = await this.pgBoss.getQueues();
      const storedQueuesNames = new Set(
        storedQueues.map((storedQueue) => storedQueue.name),
      );
      const createQueuePromises = definedQueues
        .filter((definedQueue) => !storedQueuesNames.has(definedQueue.name))
        .map((definedQueue) => {
          const { name, options } = definedQueue;
          console.log(`Creating queue with name: ${name}`);
          return this.pgBoss.createQueue(name, options as PgBoss.Queue);
        });
      console.log('CREATED QUEUE PROMISES:: ', createQueuePromises);
      await Promise.all(createQueuePromises);

A refactor to a more one-by-one queue creation seems to have resolve the issue:

  private async persistDefinedQueues(definedQueues: Queue[]): Promise<void> {
    try {
      const persistedQueues = await this.pgBoss.getQueues();
      const persistedQueuesNames = new Set(
        persistedQueues.map((persistedQueue) => persistedQueue.name),
      );
      const unpersistedQueues = definedQueues.filter(
        (definedQueue) => !persistedQueuesNames.has(definedQueue.name),
      );
      for (const queue of unpersistedQueues) {
        const { name, options } = queue;
        await this.pgBoss.createQueue(name, options as PgBoss.Queue);
      }

Anywhere in the docs I can find situations in which i might run into a deadlock error?

[pthieu]

I'm getting the same error:

error: update or delete on table "queue" violates foreign key constraint "q_fkey" on table "je2156a92fb04e8edbbceaed4be640e9428dc958620c708ce7f41b6d2"
    at /Users/pthieu/www/p-stack/node_modules/.pnpm/[email protected][email protected]/node_modules/pg-pool/index.js:45:11
    at processTicksAndRejections (node:internal/process/task_queues:105:5)
    at Db.executeSql (/Users/pthieu/www/p-stack/node_modules/.pnpm/[email protected]/node_modules/pg-boss/src/db.js:42:14)
    at Manager.deleteQueue (/Users/pthieu/www/p-stack/node_modules/.pnpm/[email protected]/node_modules/pg-boss/src/manager.js:604:7) {
  length: 657,
  severity: 'ERROR',
  code: '23503',
  detail: 'Key (name)=(general-queue) is still referenced from table "je2156a92fb04e8edbbceaed4be640e9428dc958620c708ce7f41b6d2".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'SQL statement "WITH deleted as (\n' +
    '        DELETE FROM pgboss.queue\n' +
    '        WHERE name = queue_name\n' +
    '        RETURNING partition_name\n' +
    '      )\n' +
    '      SELECT partition_name from deleted"\n' +
    'PL/pgSQL function pgboss.delete_queue(text) line 5 at SQL statement',
  schema: 'pgboss',
  table: 'je2156a92fb04e8edbbceaed4be640e9428dc958620c708ce7f41b6d2',
  column: undefined,
  dataType: undefined,
  constraint: 'q_fkey',
  file: 'ri_triggers.c',
  line: '2609',
  routine: 'ri_ReportViolation'
}

as mentioned above, deleting all the rows in the partition table allowed the .deleteQueue to invoke successfully

[timgit]

Both the race conditions for create/delete and the use case of auto-dropping jobs during queue deletion will be included in the upcoming v11 release