Failed to update job when completed

[michellechsy]

Hi,

When my job finished execution and pgboss tried to update the job in DB table, it always fails with below message. When debug, I found the query used to update is like this (similar for failed job as well):

-- complete job
WITH results AS (
      UPDATE pgboss.job
      SET completedOn = now(),
        state = 'completed'
      WHERE id IN (SELECT UNNEST($1::uuid[]))
        AND state = 'active'
      RETURNING *
    )
    INSERT INTO pgboss.job (name, data)
    SELECT
      '__state__completed__' || name, 
      jsonb_build_object(
    'request', jsonb_build_object('id', id, 'name', name, 'data', data),
    'response', ($2::jsonb),
    'state', state,
    'retryCount', retryCount,
    'createdOn', createdOn,
    'startedOn', startedOn,
    'completedOn', completedOn,
    'failed', CASE WHEN state = 'completed' THEN false ELSE true END
  )
    FROM results
    WHERE NOT name LIKE '__state__completed__%'
    RETURNING 1;

The thing is the INSERT statement which only update name and data columns, while according to the table definition, id is not nullable. Please advise how I should do to make it successful. Thanks

[timgit]

Would you include the steps to reproduce this?

[michellechsy]

1. I defined some pub/sub methods with pgboss

   
   // named: emailqueue
   import PgBoss from 'pg-boss'
   import { logger, settings } from '@vault/core'

const options = { host: settings.db.host, database: settings.db.name, user: settings.db.username, password: settings.db.password, poolSize: settings.db.pgboss.poolSize, archiveCompletedJobsEvery: settings.db.pgboss.archiveInterval }

const boss = new PgBoss(options) boss.on('error', error => { logger.error([pgboss] Failed to handle emails: ${error}) })

const pubOptions = { retryLimit: 3, expireIn: '24:00:00' }

export const start = async () => { await boss.start() }

/**

• Email publisher
• @param {String} name - task name.
• @param {Object} data - message payload.
• @param {Object} options */ export const pub = async (name, data, options) => { return boss.publish({ name, data, options: { ...pubOptions, ...options } }) }

/**

• Event emitter with status supports.
• @param {String} name - task name.
• @param {Number} options
• @param {Function} handler - handler function return Promise. */ export const sub = async (name, options = {}, handler) => { return boss.subscribe(name, options, data => handler(data)) }

export const unsub = async name => { return boss.unsubscribe(name) }

2. add subscriber for the event:
```js
emailqueue.sub(emailTasks.TicketCreate, {}, EmailTask.sendVCAEmail)

3. pub event from business module

    emailqueue.pub(emailTasks.TicketCreate, {
       to: verifiers,
       config: Object.assign({}, emailConfig, metadata),
       body: Object.assign({ role: 'Verifiers' }, emailData)
     })

After code executed, I can see there's a record inserted into DB, but failed to process with above error

[timgit]

The schema defines the default value for the id column using the pgcrypto extension's gen_random_uuid(). It appears something is wrong with the default on the column. Perhaps changes were made to the database, because this is not a problem in the package itself.

Here's what the schema should look like: https://github.com/timgit/pg-boss/blob/master/src/plans.js#L90

If you have access to the db, you should be able to repro this with an insert directly to the pgboss.job table, excluding the id column to force the default to provide it.

[jure]

To add another data point to this, we've had a similar thing happen recently, with that same error, here it is for duckduckgoability purposes:

null value in column "id" violates not-null constraint

For some reason, the pgcrypto extension's functions were not visible in the database, i.e.

LINE 1: select gen_random_uuid()
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

But when trying to install it with CREATE EXTENSION IF NOT EXISTS pgcrypto; it said that the extension already exists. Logging out and back in to the session caused the function to be visible again and start working.

It looks like this could happen if the extension was created in a different schema (I guess pgboss?), and thus is not visible from the public schema, but it's definitely puzzling to say the least.

These could be related: https://dba.stackexchange.com/questions/135093/in-rds-digest-function-is-undefined-after-creating-pgcrypto-extension https://stackoverflow.com/questions/56311405/aws-rds-postgres-crypto-functions-doesnt-work-even-with-the-pgcrypto-extension