63 lines
2.7 KiB
MySQL
63 lines
2.7 KiB
MySQL
|
ALTER TABLE jobs
|
||
|
|
ADD COLUMN retry_not_before TIMESTAMPTZ,
|
||
|
|
ADD COLUMN coordination_cleaned_at TIMESTAMPTZ,
|
||
|
|
ADD COLUMN recovery_checked_at TIMESTAMPTZ;
|
||
|
|
|
||
|
|
ALTER TABLE attempts
|
||
|
|
DROP CONSTRAINT attempts_state_timestamps_check,
|
||
|
|
ADD COLUMN worker_down_at TIMESTAMPTZ,
|
||
|
|
ADD CONSTRAINT attempts_state_timestamps_check CHECK (
|
||
|
|
(state = 'authorized' AND executing_at IS NULL AND reaped_at IS NULL
|
||
|
|
AND stopped_at IS NULL AND committed_at IS NULL)
|
||
|
|
OR (state = 'executing' AND executing_at IS NOT NULL AND reaped_at IS NULL
|
||
|
|
AND stopped_at IS NULL AND committed_at IS NULL)
|
||
|
|
OR (state = 'reaped' AND executing_at IS NULL AND reaped_at IS NOT NULL
|
||
|
|
AND stopped_at IS NULL AND committed_at IS NULL)
|
||
|
|
OR (state = 'stopped' AND reaped_at IS NULL
|
||
|
|
AND stopped_at IS NOT NULL AND committed_at IS NULL)
|
||
|
|
OR (state = 'committed' AND executing_at IS NOT NULL AND reaped_at IS NULL
|
||
|
|
AND stopped_at IS NULL AND committed_at IS NOT NULL)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE attempt_staging_cleanup (
|
||
|
|
cleanup_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
|
|
attempt_id TEXT NOT NULL REFERENCES attempts (attempt_id),
|
||
|
|
kind TEXT NOT NULL CHECK (kind IN ('object', 'multipart')),
|
||
|
|
target_store TEXT NOT NULL,
|
||
|
|
bucket TEXT NOT NULL,
|
||
|
|
object_key TEXT NOT NULL,
|
||
|
|
version_id TEXT,
|
||
|
|
upload_id TEXT,
|
||
|
|
state TEXT NOT NULL DEFAULT 'pending'
|
||
|
|
CHECK (state IN ('pending', 'claimed', 'complete')),
|
||
|
|
claim_token TEXT,
|
||
|
|
claimed_until TIMESTAMPTZ,
|
||
|
|
failure_count BIGINT NOT NULL DEFAULT 0 CHECK (failure_count >= 0),
|
||
|
|
last_error TEXT,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL,
|
||
|
|
completed_at TIMESTAMPTZ,
|
||
|
|
CONSTRAINT attempt_staging_cleanup_identity_shape_check CHECK (
|
||
|
|
(kind = 'object' AND upload_id IS NULL)
|
||
|
|
OR (kind = 'multipart' AND version_id IS NULL AND upload_id IS NOT NULL)
|
||
|
|
),
|
||
|
|
CONSTRAINT attempt_staging_cleanup_claim_check CHECK (
|
||
|
|
(state = 'pending' AND claim_token IS NULL AND claimed_until IS NULL
|
||
|
|
AND completed_at IS NULL)
|
||
|
|
OR (state = 'claimed' AND claim_token IS NOT NULL AND claimed_until IS NOT NULL
|
||
|
|
AND completed_at IS NULL)
|
||
|
|
OR (state = 'complete' AND claim_token IS NULL AND claimed_until IS NULL
|
||
|
|
AND completed_at IS NOT NULL)
|
||
|
|
),
|
||
|
|
UNIQUE NULLS NOT DISTINCT (
|
||
|
|
attempt_id, kind, target_store, bucket, object_key, version_id, upload_id
|
||
|
|
)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX attempt_staging_cleanup_claimable_idx
|
||
|
|
ON attempt_staging_cleanup (cleanup_id)
|
||
|
|
WHERE state IN ('pending', 'claimed');
|
||
|
|
|
||
|
|
CREATE INDEX jobs_recovery_idx
|
||
|
|
ON jobs (updated_at, job_id)
|
||
|
|
WHERE phase NOT IN ('succeeded', 'failed', 'cancelled')
|
||
|
|
OR coordination_cleaned_at IS NULL;
|