duplicate error on staging deployment

[aslushnikov]

Hey,

We're running pg-boss version 10.1.1. Our staging environment started throwing the following error, and job processing is essentially stopped:

error: duplicate key value violates unique constraint "j5e16baf3740f072e4a3906bbd3baa43b2d2407332bc9573904fae9d3_i3"
    at /Users/aslushnikov/flakiness/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Db.executeSql (/Users/aslushnikov/flakiness/node_modules/pg-boss/src/db.js:42:14)
    at async Boss.expire (/Users/aslushnikov/flakiness/node_modules/pg-boss/src/boss.js:164:5)
    at async Boss.maintain (/Users/aslushnikov/flakiness/node_modules/pg-boss/src/boss.js:118:22)
    at async Boss.onSupervise (/Users/aslushnikov/flakiness/node_modules/pg-boss/src/boss.js:105:24) {
  length: 477,
  severity: 'ERROR',
  code: '23505',
  detail: "Key (name, state, COALESCE(singleton_key, ''::text))=(STATS_BUILDER, retry, data/project-11990fb3-4c90-4843-b01e-0ad28c18c147/monthly-stats/2024/sep.json.br) already exists.",
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'pgboss',
  table: 'j5e16baf3740f072e4a3906bbd3baa43b2d2407332bc9573904fae9d3',
  column: undefined,
  dataType: undefined,
  constraint: 'j5e16baf3740f072e4a3906bbd3baa43b2d2407332bc9573904fae9d3_i3',
  file: 'nbtinsert.c',
  line: '671',
  routine: '_bt_check_unique'
}

We didn't do anything abnormal other than stopping/starting the staging service multiple times (as usual), so this might be some issue with pg-boss.

I'd be happy to provide any information to help narrow this down.

Thanks, Andrey & Co

[timgit]

This looks like a stately policy queue. Is that right? I will try and write up a repro test in the suite

[aslushnikov]

Yes, it is a stately policy queue.

[aslushnikov]

Thank you for the quick fix!