Create a default JobQueue Implementation using Postgres

[fabrizzio-dotCMS]

Parent Issue

#29474

Task

As a developer, I need to create a default implementation of the JobQueue interface using PostgreSQL. This implementation should handle the core functionalities of enqueuing jobs, retrieving job status, canceling jobs, and retrying jobs. The job details, including parameters and results, should be stored in PostgreSQL using JSONB fields. This will serve as the backbone of the job management system, ensuring that jobs are reliably managed and tracked within the database.

Consider the tech details shared in this video about https://learn.microsoft.com/en-us/shows/citus-con-postgres-2022/queues-in-postgresql
and https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/
about SELECT FOR UPDATE and SKIP LOCKED

Proposed Objective

Core Features

Proposed Priority

Priority 3 - Average

Acceptance Criteria

1. Schema Design: Define the PostgreSQL schema to store job details.
   1.1 A table of jobs should be created with columns for job ID, status, parameters, results, progress, and execution node.

2. Enqueue Job: Implement the method to insert a new job into the jobs table.
   2.1. The job should be assigned a unique ID and initial status.
   2.2 Parameters should be stored in a JSON field.

3. Retrieve Job Status: Implement the method to retrieve the status of a job from the jobs table.
   3.1 The status should include progress and the node executing the job.

4. Cancel Job: Implement the method to update the status of a job to "canceled" in the jobs table.
   4.1. Ensure that running jobs can be identified and canceled appropriately.

5. Retry Job: Implement the method to reset the status of a failed job to "pending" and re-enqueue it.
   5.1. Ensure the job is processed again from the beginning.

6. Error Handling: Ensure that all database operations handle errors gracefully and log appropriate messages.

7. Documentation: Provide documentation on how the PostgresJobQueue implementation interacts with the PostgreSQL database.

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

No response

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response

[nollymar]

This change will be tested after finishing this implementation