[Feature] Simplify the current state to just one table
I was thinking of the following schema
```SQL
CREATE TABLE hummingbird.job_queue (
id uuid,
job_name VARCHAR(255) NOT NULL,
payload bytea NOT NULL,
priority INTEGER DEFAULT 100,
status SMALLINT NOT NULL,
error_count INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
started_at TIMESTAMP WITH TIME ZONE,
updated_at TIMESTAMP WITH TIME ZONE,
completed_at TIMESTAMP WITH TIME ZONE
CONSTRAINT _hb_pg_job_queue_pkey PRIMARY KEY (priority, created_at, id) PRIMARY KEY
);
```
This proposal will simplify the current two step approach that exists.
Instead of inserting in the jobs table and then move a job to the job_queue table, we can insert onset and update the job query to the following
```SQL
UPDATE
_hb_pg_job_queue
SET status = \(Status.processing),
started_at = \(Date.now)
WHERE id IN(
SELECT
id
FROM _hb_pg_job_queue task_queue
WHERE status IN (\(Status.pending)) OR completed_at IS NULL
ORDER BY task_queue.created_at ASC
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING id, payload
```
On failure, the error_count can be incremented of which can be used for exponential backoff retries.
Moreover, this affords a user to retain logs/job information in the table via config `shouldDeleteJobAfterCompletion: Bool`
1 条评论