CREATE TABLE IF NOT EXISTS {SCHEMA_NAME}.postjobs (
-- identifiers ------------------------------------------------------------------------------------------ -- These are set when enqueueing a workflow id BIGSERIAL PRIMARY KEY, -- process id parent_id BIGINT REFERENCES {SCHEMA_NAME}.postjobs ON DELETE CASCADE, -- parent process id full_id VARCHAR, -- full process id root_id BIGINT, -- root process id -- timestamps ------------------------------------------------------------------------------------------- -- managed automatically created_at timestamp NOT NULL DEFAULT (now() at time zone 'utc'), -- creation timestamp updated_at timestamp NOT NULL DEFAULT (now() at time zone 'utc'), -- update timestamp -- workflow specification ------------------------------------------------------------------------------- -- Set when enqueueing the workflow, readonly queue VARCHAR, -- queue name. (readonly) workflow VARCHAR NOT NULL, -- e.g. "MyJobModule" (readonly) workflow_method VARCHAR NOT NULL DEFAULT 'run', -- e.g. "run" (readonly) args JSONB, -- args timing_out_at timestamp, -- job times out after this timestamp max_attempts INTEGER NOT NULL DEFAULT 1, -- maximum attempts before failing -- process state ---------------------------------------------------------------------------------------- -- Managed automatically. -- The workflow version is pinned as soon as the workflow is running for the first time. -- It is readonly afterwards. Note that the default is not NULL, but '', because this allows -- to query via (workflow, workflow_version) IN (('Foo', ''), ('Foo', '1.0')) workflow_version VARCHAR NOT NULL DEFAULT '', -- e.g. "1.0" -- The workflow status, one of 'ready', 'sleep', 'failed', 'err', 'timeout', 'ok' status {SCHEMA_NAME}.statuses DEFAULT 'ready', -- Timestamp when consider running this the next time. next_run_at timestamp DEFAULT (now() at time zone 'utc'), -- Number of failed attempts so far. failed_attempts INTEGER NOT NULL DEFAULT 0, -- last_worker_session_id UUID NOT NULL REFERENCES {SCHEMA_NAME}.worker_sessions ON DELETE CASCADE, -- process result --------------------------------------------------------------------------------------- results JSONB, -- The process result, if any. Only valid when status == 'ok' error VARCHAR, -- The last error; usually set to the klass of the error. error_message VARCHAR, -- The human readable error message, for displaying purposes error_backtrace JSONB, -- additional error information, for debugging purposes -- custom fields ---------------------------------------------------------------------------------------- -- workflow_status VARCHAR, tags JSONB -- processing_client information ------------------------------------------------------------------------ -- This information is passed along from workers during processing. They are only valid -- when status == 'processing' -- -- Initially these columns didn't exist, and have been created via another migration -- (003b_processing_columns.sql). They are listed here for documentation purposes. -- processing_client varchar, -- host:port of client (taken from pg_stat_activity) -- processing_client_identifier varchar, -- free text info, set via set_client_identifier() -- processing_started_at timestamp -- when did processing start? -- processing_max_duration float -- maximum expected duration of processing. Afterwards the -- processing is considered failed for unknown reasons, and -- potentially restarted. -- process information ------------------------------------------------------------------------ -- -- Initially these columns didn't exist, and have been created via another migration. -- last_worker_session_id uuid NOT NULL, -- session_id of last worker that touched this job -- cron_interval integer, -- for cron jobs: cron_interval in seconds -- is_sticky boolean DEFAULT false NOT NULL, -- when set this job is supposed to be sticky -- sticky_host_id uuid -- once a sticky job starts running the job's host id -- is_greedy boolean DEFAULT false NOT NULL -- is this a greedy job? (i.e. sticky, and do not allow any other job in this worker.)
);
– [TODO] check indices CREATE INDEX IF NOT EXISTS postjobs_tags_idx
ON {SCHEMA_NAME}.postjobs USING GIN (tags jsonb_path_ops);
CREATE INDEX IF NOT EXISTS postjobs_parent_id_idx
ON {SCHEMA_NAME}.postjobs(parent_id);