Mandatory createQueue leads to duplicate key constraint violation

[rkperes]

I've just started using pg-boss, so pardon if I'm missing something basic. I started already with the latest version v10.0.1. Since v10.0.0, we need to call createQueue before creating any jobs (even workers, AFAIU). I'm starting my workers from Next.js instrumentation.ts, as also seen in https://github.com/mjftw/next-boss-example. While that works, I'm always getting logs for the duplicate constraint violation errors (after a first server start, where the create generates the first entries):

``` error: duplicate key value violates unique constraint "queue_pkey" at /Users/rkperes/src/factree-shop/node_modules/pg-pool/index.js:45:11 at process.processTicksAndRejections (node:internal/process/task_queues:95:5) at async Db.executeSql (webpack-internal:///(instrument)/./node_modules/pg-boss/src/db.js:42:14) at async Manager.createQueue (webpack-internal:///(instrument)/./node_modules/pg-boss/src/manager.js:550:5) { length: 836, severity: 'ERROR', code: '23505', detail: 'Key (name)=(say-hello) already exists.', hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: 'SQL statement "INSERT INTO pgboss.queue (\n' + ' name,\n' + ' policy,\n' + ' retry_limit,\n' + ' retry_delay,\n' + ' retry_backoff,\n' + ' expire_seconds,\n' + ' retention_minutes,\n' + ' dead_letter,\n' + ' partition_name\n' + ' )\n' + ' VALUES (\n' + ' queue_name,\n' + " options->>'policy',\n" + " (options->>'retryLimit')::int,\n" + " (options->>'retryDelay')::int,\n" + " (options->>'retryBackoff')::bool,\n" + " (options->>'expireInSeconds')::int,\n" + " (options->>'retentionMinutes')::int,\n" + " options->>'deadLetter',\n" + ' table_name\n' + ' )"\n' + 'PL/pgSQL function pgboss.create_queue(text,json) line 6 at SQL statement', schema: 'pgboss', table: 'queue', column: undefined, dataType: undefined, constraint: 'queue_pkey', file: 'nbtinsert.c', line: '664', routine: '_bt_check_unique' } ```

~Additionally, I couldn't catch the error to handle it and call an update instead.~ (Edit: I wasn't properly handling the promise. Nonetheless, it'd be nice to be able to easily handle the upserts suggested below.)

Is there a general guideline on how to approach the createQueue calls? Or could we consider an upsert API that'd guarantee the queue is either created or updated with the given policies?

[CapitanFindusFI]

@rkperes I was facing the same since I am using pg-boss in my tests too so I need to create queues and then purge them and eventually delete them. I'm fine with purge instead of deleting but for creation I worked around the issue with duplicate keys by doing the following

const currentQueue = await boss.getQueue(queueName)
if(!currentQueue){
  await boss.createQueue(queueName);
}

[timgit]

I did the same thing in the pg-boss test suite as well

[noorvir]

Ran into the same issue. While it's easy to fix, it feels wrong to have to use it, unless I misunderstanding how to structure pg-boss apps.

IIUC you declare the queues (createQueue) and their handlers (boss.work) at runtime. If your worker restarts, on a new deploy for eg, createQueue should be a no-op.

[timgit]

I was on the fence about failing an existing queue, but you guys won me over. :)