Using a specific client for addGraphileJobs library function.

[hillac]

Summary

Hi, just wondering if there is a way to add a job using a pre-existing client using the js library rather than the sql interface? I want to add jobs inside my own transactions. I've resorted to making my own addGraphileJob and addGraphileJobs that are otherwise the same as the originals, but the first arg is a pg PoolClient, which is used to make the query. I feel like I must be missing something because transactional job queuing is a huge benefit of using a postgres job queue.

Thanks.

Additional context

[benjie]

There's not currently an official API for that since whenever I've needed it I'm already in an SQL context so I just call the SQL function... But I'm not opposed to adding one. Maybe add it to WorkerUtils e.g. workerUtils.addJobWithClient(client, ...options) though that does require a pool to be available. If you only want it to work with the client then it's going to be a bit tougher as the methods required to produce compiledSharedOptions expect a pool, and those are needed to produce the escapedWorkerSchema/etc. Check out src/helpers.ts

[hillac]

So I'd need to make a new makeAddJobUtils (or something) that doesn't need a pool to instantiate, just schema name? Then addJobWithClient could be exposed from both workerUtils and addJobUtils?

I'm happy if it's just exposed from workerUtils, since I have a pool anyway, but it makes sense you should be able to access it without needing a pool.

[benjie]

Putting addJobWithClient on worker utils makes sense to me, ensures you can still do regular things like migrate whilst still allowing for transactional addJob 👍

[ab-pm]

whenever I've needed it I'm already in an SQL context so I just call the SQL function...

The advantage of a library helper would be the TypeScript typings.

Putting addJobWithClient on worker utils makes sense to me

Hm, I would prefer if (additionally) there was a way to synchronously instantiate an addJobWithClient function; makeWorkerUtils() or run() only return a promise for the utils.

If you only want it to work with the client then it's going to be a bit tougher as the methods required to produce compiledSharedOptions expect a pool

I'm probably missing something, but afaict processSharedOptions does not seem to require a pool-or-connectionstring?

[davbrito]

This would be a great feature to have, as certain jobs should be directly bound to an atomic unit of business logic.

For example:

function createComment(authorId: string, postId: string, text: string) {
  await transactionManager.runInTransaction(async () => {
     const comment = await commentsRepo.insert(authorId, postId, text);

     const interestedIds = await postRepo.getInterestedUsers(postId);

     await utils.addJob("send_push", {
        to: interestedIds,
        title: "New Comment in Post",
        url: `/posts/${postId}`
     }, { client: transactionManager.getTransactionClient() })
  })
}

This way, the job creation is tied to the success of the entire transaction.

[benjie]

As a workaround for now you can do something like:

await transactionManager.getTransactionClient().query(
  'select graphile_worker.add_job($1, $2)',
  [
    'send_push',
    JSON.stringify({
      to: interestedIds,
      title: "New Comment in Post",
      url: `/posts/${postId}`
   })
  ]
)