ITADN

[Feature] Simplify the current state to just one table

#19Openthoven87 创建于 2024-07-24
T
thoven87commented
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 条评论