warkanum
4c8e1066d4
Replace the ad-hoc tables/procedures install layout with versioned, ordered SQL migrations tracked in broker_schema_migrations. Add optional least-privilege role provisioning (--with-roles), multi-tenant row-level security, lease-based job claiming with stale-lease recovery, and job dependencies -- both by job id and by fan-in job group. Add Docker/Compose support for running the broker and its test suite.
42 lines
2.8 KiB
SQL
42 lines
2.8 KiB
SQL
-- Adds job groups: every job belongs to a job_group (defaults to its own
|
|
-- job_name when not given explicitly, set by broker_add_job). A dependency
|
|
-- can now target a whole group instead of a single job id -- the dependent
|
|
-- job is claimable once every job tagged with that group has completed
|
|
-- (complete_status = 2); already-completed group members simply drop out of
|
|
-- the gating check, they don't need to have existed at any particular time.
|
|
-- The existing id-based dependency (broker_job_dependency.depends_on_job_id)
|
|
-- is kept as-is; each dependency row targets exactly one of an id or a group.
|
|
|
|
ALTER TABLE broker.broker_jobs ADD COLUMN IF NOT EXISTS job_group TEXT;
|
|
UPDATE broker.broker_jobs SET job_group = job_name WHERE job_group IS NULL;
|
|
ALTER TABLE broker.broker_jobs ALTER COLUMN job_group SET NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_broker_jobs_group
|
|
ON broker.broker_jobs (tenant_id, job_group, complete_status);
|
|
|
|
ALTER TABLE broker.broker_job_dependency DROP CONSTRAINT IF EXISTS broker_job_dependency_pkey;
|
|
ALTER TABLE broker.broker_job_dependency ALTER COLUMN depends_on_job_id DROP NOT NULL;
|
|
ALTER TABLE broker.broker_job_dependency ADD COLUMN IF NOT EXISTS depends_on_group TEXT;
|
|
ALTER TABLE broker.broker_job_dependency ADD COLUMN IF NOT EXISTS id_broker_job_dependency BIGSERIAL;
|
|
ALTER TABLE broker.broker_job_dependency ADD PRIMARY KEY (id_broker_job_dependency);
|
|
|
|
ALTER TABLE broker.broker_job_dependency DROP CONSTRAINT IF EXISTS broker_job_dependency_one_target;
|
|
ALTER TABLE broker.broker_job_dependency ADD CONSTRAINT broker_job_dependency_one_target CHECK (
|
|
(depends_on_job_id IS NOT NULL AND depends_on_group IS NULL) OR
|
|
(depends_on_job_id IS NULL AND depends_on_group IS NOT NULL)
|
|
);
|
|
|
|
-- Plain (non-partial) unique constraint, matching the old PK's guarantee --
|
|
-- NULLs in depends_on_job_id (the group-dependency rows) are never
|
|
-- considered equal by a standard unique constraint, so this only constrains
|
|
-- id-based rows, and keeps "ON CONFLICT (job_id, depends_on_job_id)" (no
|
|
-- predicate needed) working for existing callers.
|
|
ALTER TABLE broker.broker_job_dependency DROP CONSTRAINT IF EXISTS broker_job_dependency_unique_id;
|
|
ALTER TABLE broker.broker_job_dependency ADD CONSTRAINT broker_job_dependency_unique_id
|
|
UNIQUE (job_id, depends_on_job_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_broker_job_dependency_unique_group
|
|
ON broker.broker_job_dependency (job_id, depends_on_group) WHERE depends_on_group IS NOT NULL;
|
|
|
|
COMMENT ON COLUMN broker.broker_jobs.job_group IS 'Group tag for this job; defaults to job_name. Other jobs can depend on the whole group.';
|
|
COMMENT ON COLUMN broker.broker_job_dependency.depends_on_group IS 'Alternative to depends_on_job_id: job_id is not claimable until every job with job_group = depends_on_group has completed';
|