Download Browse

pg_task 3.0.0

This Release
pg_task 3.0.0
Date
Status
Stable
Other Releases
Abstract
PostgreSQL, Greenplum and Greengage job scheduler pg_task allows to execute any sql command at any specific time at background asynchronously
Description
PostgreSQL, Greenplum and Greengage job scheduler pg_task allows to execute any sql command at any specific time at background asynchronously
Released By
RekGRpth
License
MIT
Resources
Special Files
Tags

Extensions

pg_task 3.0.0

README

pg_task

PostgreSQL, Greenplum and Greengage job scheduler pg_task allows to execute any sql command at any specific time at background asynchronously.

It runs entirely inside the database as a set of background workers — no external daemon, no client library, nothing to babysit outside PostgreSQL itself. You enable it with a single GUC, and from then on scheduling a job is just an INSERT into a plain table: pg_task polls that table, runs input, and writes the result back into the same row (see Task state machine for exactly how).

Table of contents

Quick start

First, enable the extension by adding it to shared_preload_libraries and restarting PostgreSQL: conf shared_preload_libraries = 'pg_task' # add pg_task to shared_preload_libraries On startup, pg_task sets up everything it needs by itself — role, database, schema and the task table — using built-in defaults (database postgres, user postgres, schema public, table task; see Configuration (GUCs) to point it elsewhere, and Self-provisioning and the helper triggers for how).

Second, schedule work by inserting rows into the task table — one row is one job: sql INSERT INTO task (input) VALUES ('SELECT now()'); -- no plan/repeat: runs once, as soon as possible INSERT INTO task (plan, input) VALUES (now() + '5 min':INTERVAL, 'SELECT now()'); -- runs once, after a 5 minute delay (plan = planned time) INSERT INTO task (plan, input) VALUES ('2029-07-01 12:51:00', 'SELECT now()'); -- runs once, at that exact timestamp (plan = planned time) INSERT INTO task (repeat, input) VALUES ('5 min', 'SELECT now()'); -- runs, then reinserts itself to run again every 5 minutes (repeat = interval) INSERT INTO task (input) VALUES ('SELECT 1/0'); -- an error doesn't crash the worker: it's caught and written to error as text INSERT INTO task (group, max, input) VALUES ('group', 1, 'SELECT now()'); -- max = 1 lets one extra task of this group run concurrently with this one (2 at a time total) INSERT INTO task (group, max, input) VALUES ('group', 2, 'SELECT now()'); -- a higher max also jumps the queue ahead of lower-max tasks in the same group — it behaves like a priority, not just a concurrency cap INSERT INTO task (input, remote) VALUES ('SELECT now()', 'user=user host=host'); -- remote runs input on another database instead of the local one UPDATE task SET state = 'STOP' WHERE id = ...; -- cancels a running (state = WORK) task, local or remote; input's query gets cancelled and state stays STOP, not FAIL pg_task notices a new or changed row almost immediately — no polling delay to wait out (see Wake-up and crash recovery) — executes input, and writes the outcome straight back into that row: the result goes to output, any error to error, and state tracks progress along the way (PLAN → TAKE → WORK → DONE/FAIL). There’s no separate status API — just query the table: SELECT * FROM task WHERE id = ....

Build

pg_task is a standard PGXS extension, built against an installed PostgreSQL, Greenplum or Greengage server.

Requirements: matching -dev/-devel package with pg_config on PATH, a C compiler, make, curl and pcregrep.

make USE_PGXS=1 install

Before compiling, the build auto-generates postgres.c (and exec.c from it) by detecting the installed server’s flavor and version (postgres --version, pg_config --version/--gp_version) and downloading the matching src/backend/tcop/postgres.c from the corresponding upstream repository (postgres/postgres or GreengageDB/greengage) on GitHub.

If you already have the exact source tree the server was built from (e.g. a custom/unreleased build), you can skip the network fetch: place a symlink named postgres.c pointing at src/backend/tcop/postgres.c in that tree before running make — an existing postgres.c is used as-is.

Configuration (GUCs)

pg_task creates the following GUCs. Level lists where each one can be set; when a GUC is settable at more than one level, the most specific value wins — a per-session SET beats a per-role/database default, which beats the config file. Several task columns (see Task table below) default to the matching GUC’s current value at insert time, so setting the GUC once is often enough without repeating it on every row.

Name Type Default Level Description
pg_task.delete bool true config, database, user, session Auto delete task when both output and error are nulls
pg_task.drift bool false config, database, user, session Compute next repeat time by stop time instead by plan time
pg_task.header bool true config, database, user, session Show columns headers in output (only when the query returns at least one row and more than one column)
pg_task.save bool false config, database, user, session Save session state between tasks
pg_task.spi bool false config, database, user, session SPI (or local) execution? Also affects input containing multiple ;-separated statements: on PostgreSQL 10+, SPI mode runs each statement separately and appends all results, same as local mode; on pre-10 backends, SPI mode returns only the last statement’s result
pg_task.string bool true config, database, user, session Quote only strings
pg_conf.fetch int 10 config, database, superuser Fetch conf rows at once
pg_conf.max int max_worker_processes config Maximum task and work workers
pg_conf.restart int 60 config, database, superuser Restart conf interval, seconds
pg_task.count int 0 config, database, user, session Non-negative maximum count of tasks, are executed by current background worker process before exit
pg_task.fetch int 100 config, database, user Fetch task rows at once
pg_task.id bigint 0 session Current task id (for read only)
pg_task.limit int 1000 config, database, user Limit task rows at once
pg_task.max int 0 config, database, user, session Maximum count of additional concurrently executing tasks in group (total concurrency = max + 1), negative value means pause between tasks in milliseconds
pg_task.run int 2147483647 config, database, user, session Maximum count of concurrently executing tasks in work
pg_task.sleep int 1000 config, database, user Check tasks every sleep milliseconds
pg_work.fetch int 100 config, database, superuser Fetch work rows at once
pg_work.idle int 60 config, database, user Idle work count
pg_work.restart int 60 config, database, superuser Restart work interval, seconds
pg_task.active interval 1 hour config, database, user, session Positive period after plan time, when task is active for executing
pg_task.data text postgres config Database name for tasks table
pg_task.delimiter char \t config, database, user, session Results columns delimiter
pg_task.escape char config, database, user, session Results columns escape
pg_task.group text group config, database, user, session Task grouping by name
pg_task.json json [{“data”:“postgres”}] config Json configuration, available keys: data, reset, schema, table, sleep and user
pg_task.live interval 0 sec config, database, user, session Non-negative maximum time of live of current background worker process before exit
pg_task.null text \N config, database, user, session Null text value representation
pg_task.plan timestamptz statement_timestamp() config, database, user, session Default value for plan timestamp
pg_task.quote char config, database, user, session Results columns quote
pg_task.repeat interval 0 sec config, database, user, session Non-negative auto repeat tasks interval
pg_task.reset interval 1 hour config, database, user Interval of reset tasks
pg_task.schema text public config, database, user Schema name for tasks table
pg_task.table text task config, database, user Table name for tasks table
pg_task.timeout interval 0 sec config, database, user, session Non-negative allowed time for task run
pg_task.user text postgres config User name for tasks table

Task table

pg_task creates the task table (name and location configurable, see above) with the following columns. Most of them default to the corresponding GUC and can be overridden per row — so a task can, for example, use a longer timeout or a different group than the session default just by setting that column on insert.

Name Type Nullable? Default Description
id bigserial NOT NULL autoincrement Primary key
parent bigint NULL pg_task.id Parent task id (if exists, like foreign key to id, but without constraint, for performance)
plan timestamptz NOT NULL pg_task.plan Planned date and time of start
start timestamptz NULL Actual date and time of start
stop timestamptz NULL Actual date and time of stop
active interval NOT NULL pg_task.active Positive period after plan time, when task is active for executing
live interval NOT NULL pg_task.live Non-negative maximum time of live of current background worker process before exit
repeat interval NOT NULL pg_task.repeat Non-negative auto repeat tasks interval
timeout interval NOT NULL pg_task.timeout Non-negative allowed time for task run
count int NOT NULL pg_task.count Non-negative maximum count of tasks, are executed by current background worker process before exit
max int NOT NULL pg_task.max Maximum count of additional concurrently executing tasks in group (total concurrency = max + 1), negative value means pause between tasks in milliseconds
pid int NULL Id of process executing task
state enum state (PLAN, GONE, TAKE, WORK, DONE, FAIL, STOP) NOT NULL PLAN Task state
delete bool NOT NULL pg_task.delete Auto delete task when both output and error are nulls
drift bool NOT NULL pg_task.drift Compute next repeat time by stop time instead by plan time
header bool NOT NULL pg_task.header Show columns headers in output (only when the query returns at least one row and more than one column)
save bool NOT NULL pg_task.save Save session state between tasks
string bool NOT NULL pg_task.string Quote only strings
delimiter char NOT NULL pg_task.delimiter Results columns delimiter
escape char NOT NULL pg_task.escape Results columns escape
quote char NOT NULL pg_task.quote Results columns quote
data text NULL Some user data
error text NULL Catched error
group text NOT NULL pg_task.group Task grouping by name
input text NOT NULL Sql command(s) to execute
null text NOT NULL pg_task.null Null text value representation
output text NULL Received result(s)
remote text NULL Connect to remote database (if need)
user name NOT NULL current_user Role that inserted the task; input is executed as this role, and the column is immutable after insert

You may freely add your own columns to task and/or partition it — pg_task only ever touches the columns it created (see Self-provisioning and the helper triggers).

Running in multiple databases

By default pg_task runs a single scheduler, on the default database (postgres), as the default user (postgres), watching the default schema (public) and table (task), polling every default sleep interval.

To run more than one scheduler — e.g. one per application database, each with its own user/schema/table/poll interval — list them in pg_task.json, one object per scheduler; any key you omit falls back to its GUC default: conf pg_task.json = '[{"data":"database1"},{"data":"database2","user":"username2"},{"data":"database3","schema":"schema3"},{"data":"database4","table":"table4"},{"data":"database5","sleep":100}]' pg_task creates whichever of the referenced database, user, schema or table don’t already exist — you don’t need to provision them by hand first.

Architecture

pg_task has no pg_task--<version>.sql control script and is never activated with CREATE EXTENSION — everything it needs (role, database, schema, table, the state enum, indexes, defaults, constraints and two helper triggers) is created idempotently by the extension itself the first time it starts, by checking pg_catalog before every CREATE/ALTER and only touching what’s missing or mismatched. Rerunning it, or adding your own columns/partitions by hand, is safe — pg_task never drops or rewrites what it didn’t create itself.

Process hierarchy

The extension is split into four parts, each backed by its own source file and, except the first, its own background worker type:

  1. init registers the extension’s GUCs and, once, the single static background worker pg_conf (started under shared_preload_libraries).
  2. pg_conf (one process per postmaster; on Green(plum|gage), coordinator only) parses pg_task.json — together with any per-database/per-role overrides — every pg_conf.restart seconds, creates the referenced role/database if missing, and launches one dynamic background worker pg_work per {data, schema, table, user, sleep, ...} entry.
  3. pg_work (one process per such entry) is the scheduler proper: it provisions the schema/table on first connect (see below), then loops on a wait-event set — its latch plus the sockets of any open remote connections — periodically claiming due rows from the task table (respecting plan, active and per-group concurrency), expiring overdue non-repeating rows to GONE, and resetting rows orphaned by a crashed worker back to PLAN. Local tasks (no remote) are handed off to a child pg_task worker; remote tasks are driven directly by pg_work over an async, non-blocking libpq connection — no extra OS process per remote task.
  4. pg_task (one process per concurrently running local task) connects, executes input (locally or via SPI, see below), writes output/error, flips state to DONE/FAIL, schedules the next repeat occurrence if any, and exits — or, within count/live, picks up another task of the same group before exiting.

Parameters are passed down the hierarchy through dynamic shared memory allocated at worker startup, not through command-line arguments or files.

Task state machine

PLAN → TAKE → WORK → DONE | FAIL, or PLAN → GONE when a non-repeating task’s active window elapses before it’s picked up (typically: the server was down longer than active allows). STOP is a manual, terminal state you set yourself (UPDATE task SET state = 'STOP' WHERE id = ...); no code transitions a row into it automatically. Setting it on a PLAN row just keeps it from ever being claimed (it’s filtered out by state = 'PLAN' like any other non-PLAN row). Setting it on a WORK row actually cancels the running task: a trigger cancels the local backend directly, or, for a remote task, wakes the owning pg_work (the same advisory-lock mechanism as the wake-up trigger below) so it calls PQcancel() on that connection. Either way the cancelled query’s error is caught the normal way, but the row is left at STOP instead of being overwritten to FAIL, and — unlike a plain FAIL — its next repeat occurrence, if any, is not inserted. There’s still no way to cancel a task from SQL other than this — pg_cancel_backend(pid) directly works too, but leaves the row at FAIL since nothing marked it STOP first.

This diagram is enforced, not just documented: a BEFORE UPDATE OF "state" trigger (task_state) allows exactly PLAN → {TAKE, GONE, STOP}, TAKE → {WORK, PLAN, DONE, FAIL}, WORK → {DONE, FAIL, PLAN, STOP} and nothing else — including no transition at all out of DONE, FAIL, GONE or STOP, or out of any state you might add to the enum yourself (see Patterns). Worth knowing before reaching for a custom state of your own: once a row is in one, there’s no trigger-sanctioned way to move it back out.

PLAN → TAKE is a single UPDATE ... SKIP LOCKED that also counts current concurrency for the task’s group/remote hash — via session-level advisory locks visible in pg_locks — against max. So max behaves less like a hard cap feeding one shared queue and more like a priority: a group with a higher max picks up its own next tasks sooner, independently of other groups. A negative max instead schedules a pause: on completion, the other PLAN rows of the same group get their plan pushed forward by |max| milliseconds. pg_task.run (config/database/user only — unlike max, there’s no per-row override) caps how many tasks this one pg_work may have in flight at once across all groups combined, on top of whatever max allows any individual group — the one concurrency limiter in pg_task that isn’t per-group.

WORK → DONE/FAIL is a single UPDATE ... RETURNING that, in the same round trip, decides whether to delete the row (delete, when both output and error are null), whether to insert the next repeat occurrence (computed from the original plan or from the actual finish time, depending on drift), whether the same worker process may pick up another task of the group without exiting (within count/live), and whether to reschedule the rest of the group (negative max).

timeout bounds how long input itself may run — locally via a timeout event in the pg_task worker’s loop, remotely via SET SESSION statement_timeout sent ahead of input. live/count instead bound the executor process, not the task: how many tasks in a row, or how long, one pg_task worker lives before being recycled.

Wake-up and crash recovery

Instead of LISTEN/NOTIFY, pg_task wakes idle workers with session-level advisory locks plus pg_cancel_backend(). Each pg_work process holds an advisory lock tagged with its group’s hash for as long as it’s alive; the AFTER INSERT OR DELETE OR UPDATE OF plan trigger (see below) looks up the holder of that lock in pg_locks and cancels it directly. pg_work installs its own SIGINT handler — instead of the default query-cancel one — that just sets the latch, so the wait-event loop returns immediately instead of waiting out the rest of pg_task.sleep. The STOP-on-a-remote-task trigger (see Task state machine) reuses this exact same wake-up: once woken, pg_work checks, at most once per pg_task.sleep, whether any of its currently active remote tasks now has state = 'STOP' and cancels that connection with PQcancel().

A second, per-task advisory lock (tagged by the task’s own id) is used to detect a crashed executor: every pg_task.reset interval, pg_work looks for rows still in TAKE/WORK whose id-tagged lock nobody currently holds, and resets them to PLAN. That’s the crash-recovery mechanism.

When there’s genuinely nothing to do, pg_work doesn’t poll in a tight loop: it computes, in one query, the soonest moment something will actually need attention — the closer of the next active/timeout deadline among running tasks and the next PLAN task’s plan — and sleeps exactly until then. pg_task.sleep is a floor on responsiveness for a busy queue, not a fixed polling interval.

Self-provisioning and the helper triggers

On first connect, pg_work walks through a series of idempotent SELECT EXISTS ... checks against pg_catalog and issues the matching CREATE/ALTER only for what’s missing: schema, the state enum, the table (with all columns, current_setting('pg_task.…')-backed defaults, NOT NULL/CHECK constraints), and indexes — including a functional index on the hash of group/remote that the concurrency accounting above relies on — plus one trigger function per column it created, 25 in total. Three of them do something beyond plain immutability and are worth calling out individually:

  • the user-immutability trigger (BEFORE INSERT OR UPDATE OF "user") forces NEW."user" to current_user on insert unless the inserting role is a member of the claimed role, and rejects any later change — this is what makes the user column trustworthy for the Security considerations below.
  • the wake-up trigger (AFTER INSERT OR DELETE OR UPDATE OF plan) is the mechanism described above; it does not use NOTIFY.
  • the STOP trigger (AFTER UPDATE OF "state") is what makes setting state = 'STOP' on a WORK row actually cancel it, as described in Task state machine above.

The other 22 are plain BEFORE UPDATE OF "<column>" guards, one per remaining column, and fall into three groups:

  • state is governed by the transition-validation trigger described in Task state machine above, rather than by immutability.
  • group, remote and parent are immutable unconditionally, from the moment of insert — a task’s routing and ancestry can’t be edited after the fact, only fixed by inserting a new row instead (see Patterns).
  • every other self-provisioned column — plan, active, live, repeat, timeout, count, max, delete, drift, header, save, string, delimiter, escape, quote, input, null, data — is immutable only once the task has left PLAN: freely editable on a still-queued task (e.g. UPDATE task SET input = ... WHERE state = 'PLAN'), frozen the instant it’s dispatched.

None of this applies to columns you add yourself — pg_task only ever generates a trigger for a column it created (see Patterns).

Three ways to run input

There are three distinct execution paths, not two — which one applies is decided first by whether remote is set, and only then, for the non-remote case, by pg_task.spi:

  • local (no remote, default spi = off) dispatches input through exec_simple_query() in the pg_task worker’s own backend — the same function extracted from the matching version’s postgres.c into exec.c at build time (see Build above) — i.e. the same multi-statement dispatcher PostgreSQL uses for a real client connection: full DDL, multiple ;-separated statements, implicit transaction handling. Its result stream is captured by swapping in a custom DestReceiver that formats each row (honoring the task’s delimiter/quote/escape/null/string) straight into output, turning command-completion tags (UPDATE 3, …) into output lines too.
  • SPI (no remote, spi = on) calls SPI_execute() directly in the same backend, instead of exec_simple_query() — faster, but strictly narrower (see the table below).
  • remote (remote is set) bypasses both of the above entirely: pg_task.spi is not even read in this path. pg_work doesn’t spawn a pg_task worker at all — it opens the connection itself, asynchronously and non-blocking (PQconnectStartParams + PQsetnonblocking), and adds its socket to its own wait-event set, so dozens of concurrent remote tasks cost no extra OS process beyond pg_work itself. Once connected it sends a preamble (SET SESSION for the relevant pg_task.* parameters and statement_timeout) followed by input, and input is dispatched by the remote server’s own query processor over the wire, exactly as if a regular client had sent it — pg_task’s local DestReceiver/SPI code never runs. The streamed result is formatted into output/error the same way as in local mode, statement by statement. Once input finishes, pg_work sends COMMIT (closing whatever transaction input left open) and then DISCARD ALL unless save asks to keep the session for the next task of the group — before the connection is either reused or closed.

In every case the final state transition (WORK → DONE/FAIL) is written locally via SPI, regardless of which of the three paths actually ran input.

Each path has its own restrictions on what input can contain:

local SPI remote
Several ;-separated statements all results appended to output all results appended to output (PostgreSQL 10+, split via RawStmt.stmt_location); only the last statement’s result on pre-10 backends all results appended to output
COPY ... FROM STDIN / ... TO STDOUT / COPY BOTH rejected outright (COPY … is not supported) rejected outright (SPI_ERROR_COPY) FROM STDIN/BOTH rejected (pg_task has no data to stream in); TO STDOUT is supported and streamed straight into output
COPY ... TO/FROM a server-side file or PROGRAM allowed, if the role has the privilege rejected (SPI rejects any COPY) allowed, if the role has the privilege — runs on the remote server
Explicit BEGIN/COMMIT/ROLLBACK in input allowed (a transaction left open at the end is closed automatically) rejected (SPI_ERROR_TRANSACTION) allowed (it’s a real client session on the far side)
DDL allowed allowed (as an SPI utility statement) allowed

Signals

SIGHUP — in pg_conf, pg_work and pg_task alike — reloads postgresql.conf and re-runs the relevant config/task check, so most GUCs and pg_task.json can be changed without a server restart. SIGTERM is handled the standard background-worker way; on the way out, each level releases its advisory locks, and pg_work additionally closes any open remote connections cleanly instead of dropping them.

Patterns

pg_task is intentionally minimal — no built-in retry, backoff or conditional repeat. Both of the recipes below build the missing behavior entirely in SQL, on top of mechanics already described above: the atomicity of running input (see Three ways to run input), and the fact that pg_task only ever touches the columns it created (see Self-provisioning and the helper triggers), so your own columns and triggers on task compose freely with it. No core code changes needed for either.

Retry until success

A repeating task (repeat > 0) that should actually run once, and only keep repeating while it keeps failing: make the last statement of input cancel the row’s own repeat, referencing the running task’s own id via pg_task.id (see Configuration (GUCs)):

INSERT INTO task (input, repeat) VALUES ($$
    -- do the real work; an unhandled error here aborts everything below too
    INSERT INTO some_table (...) VALUES (...);

    -- reached, and committed, only if everything above succeeded
    UPDATE task SET repeat = '0 sec' WHERE id = current_setting('pg_task.id')::bigint;
$$, '1 min');

This works because input runs as one atomic unit — the local dispatcher treats a ;-separated input sent in one go as a single implicit transaction (see Three ways to run input), and SPI mode wraps it in one subtransaction — while the state/repeat-scheduling update (task_done()/task_insert() in task.c) always runs afterwards, in its own transaction, and reads whatever repeat value actually ended up committed on the row:

  • on success, the UPDATE ... repeat = '0 sec' commits together with the real work, so by the time pg_task decides whether to schedule the next occurrence, repeat is already 0 sec — no further occurrence is inserted, and the task effectively ran once.
  • on failure, the whole input — including that trailing UPDATE — rolls back together, so repeat is left exactly as it was; pg_task sees repeat > 0 and inserts the next occurrence as usual, so the task keeps retrying at that interval until it finally succeeds.

The one thing to avoid: don’t wrap the real work in its own EXCEPTION WHEN OTHERS handler that swallows the error — that would make a failed run look successful to pg_task (and to the UPDATE that cancels repeat).

Retry with exponential backoff

For a bounded number of retries with a growing delay between attempts (rather than “forever, at a fixed interval”), add your own bookkeeping columns and an AFTER UPDATE trigger that re-inserts a FAILed task with a computed plan:

ALTER TABLE task ADD COLUMN retry           int      NOT NULL DEFAULT 0;
ALTER TABLE task ADD COLUMN retry_max       int      NOT NULL DEFAULT 0;
ALTER TABLE task ADD COLUMN retry_interval  interval NOT NULL DEFAULT '1 min';

CREATE OR REPLACE FUNCTION task_retry() RETURNS trigger AS $f$
DECLARE
    columns text;
BEGIN
    IF NEW.retry >= NEW.retry_max THEN
        RETURN NEW;
    END IF;
    SELECT string_agg(quote_ident(attname), ', ' ORDER BY attnum) INTO columns
    FROM pg_attribute
    WHERE attrelid = TG_RELID AND attnum > 0 AND NOT attisdropped
      AND attname NOT IN ('id', 'plan', 'parent', 'start', 'stop', 'pid', 'state', 'error', 'output', 'retry');
    EXECUTE format(
        'INSERT INTO %I (parent, plan, retry, %s) SELECT id, statement_timestamp() + retry_interval * power(2, retry), retry + 1, %s FROM %I WHERE id = $1',
        TG_TABLE_NAME, columns, columns, TG_TABLE_NAME
    ) USING NEW.id;
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER task_retry_trigger AFTER UPDATE OF state ON task
FOR EACH ROW WHEN (NEW.state = 'FAIL') EXECUTE FUNCTION task_retry();

The column list is looked up dynamically (the same trick pg_task itself uses in task_columns() to clone a row for repeat), so the trigger keeps working as you add more columns of your own. parent is set to the id of the attempt that just failed, so the whole retry chain stays visible through parent. retry_interval * power(2, retry) doubles the delay each attempt (1 min, 2 min, 4 min, …); once retry reaches retry_max no new row is inserted and the last attempt is left at FAIL. FAIL rows are never auto-deleted by pg_task.delete, since that only fires when both output and error are null — a FAIL row always has error set — so the trigger always gets to see it.

Conditional launch on a parent task (“on success” / “on failure”)

parent (see Task table) records ancestry but by itself doesn’t hold a child back from running — it’s picked up as soon as its own plan is due, regardless of the parent. To actually gate a child on how its parent finished, without waiting on a brand-new state (self-provisioning only ever adds enum values it knows about, and the built-in BEFORE UPDATE OF "state" transition-validation trigger accepts no transition at all out of a state it doesn’t recognize — you’d paint yourself into a corner trying to move a custom “waiting” state back to PLAN), hold the child in PLAN with plan pushed out to infinity instead, and only bring plan back down once the parent settles:

ALTER TABLE task ADD COLUMN depend text NOT NULL DEFAULT 'always' CHECK (depend IN ('success', 'failure', 'always'));

CREATE OR REPLACE FUNCTION task_depend_insert() RETURNS trigger AS $f$
DECLARE
    parent_state state;
BEGIN
    IF NEW.parent IS NULL THEN RETURN NEW; END IF;
    SELECT state INTO parent_state FROM task WHERE id = NEW.parent;
    IF parent_state IS NULL OR parent_state NOT IN ('DONE', 'FAIL') THEN
        NEW.plan := 'infinity';                  -- parent hasn't settled yet — wait
    ELSIF NOT ((parent_state = 'DONE' AND NEW.depend IN ('success', 'always'))
            OR (parent_state = 'FAIL' AND NEW.depend IN ('failure', 'always'))) THEN
        NEW.state := 'STOP';                      -- parent already settled the other way — never run
    END IF;
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;
CREATE TRIGGER task_depend_insert BEFORE INSERT ON task
FOR EACH ROW EXECUTE FUNCTION task_depend_insert();

CREATE OR REPLACE FUNCTION task_depend_update() RETURNS trigger AS $f$
BEGIN
    UPDATE task SET plan = statement_timestamp()
    WHERE parent = NEW.id AND state = 'PLAN' AND plan = 'infinity'
      AND ((NEW.state = 'DONE' AND depend IN ('success', 'always'))
        OR (NEW.state = 'FAIL' AND depend IN ('failure', 'always')));
    UPDATE task SET state = 'STOP'
    WHERE parent = NEW.id AND state = 'PLAN' AND plan = 'infinity'
      AND NOT ((NEW.state = 'DONE' AND depend IN ('success', 'always'))
            OR (NEW.state = 'FAIL' AND depend IN ('failure', 'always')));
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;
CREATE TRIGGER task_depend_update AFTER UPDATE OF state ON task
FOR EACH ROW WHEN (NEW.state IN ('DONE', 'FAIL')) EXECUTE FUNCTION task_depend_update();

This relies on two more of pg_task’s own self-provisioned triggers, beyond the three named in Self-provisioning and the helper triggers above: plan is only frozen after a task leaves PLAN (BEFORE UPDATE OF "plan", conditional on OLD.state <> 'PLAN'), so a still-PLAN child’s plan remains freely updatable — and updating it fires the existing wake-up trigger for free, so a released child is picked up immediately rather than on the next poll. plan = 'infinity' also keeps the child out of reach of the active-window GONE transition (plan + active stays infinity too), so it can wait indefinitely without expiring. Moving an unsatisfied child straight to STOP is the one transition the built-in state-machine trigger allows out of PLAN besides TAKE/GONE.

This only expresses a single upstream dependency (one depend per parent, matching the column’s own cardinality), not a multi-parent join.

Cron-like scheduling

repeat is a fixed interval — it can’t express “every weekday at 9am” or “the 1st of the month”. Rather than a fixed step, compute the next occurrence yourself from a 5-field cron expression (min hour dom month dow) and drive plan with it directly, leaving repeat at its default 0 sec so the built-in auto-repeat stays out of the way:

CREATE OR REPLACE FUNCTION cron_field(spec text, lo int, hi int) RETURNS int[] AS $f$
DECLARE
    part text; rng text; step int; a int; b int; result int[] := '{}';
BEGIN
    FOREACH part IN ARRAY string_to_array(spec, ',') LOOP
        IF part LIKE '%/%' THEN
            rng := split_part(part, '/', 1); step := split_part(part, '/', 2)::int;
        ELSE
            rng := part; step := 1;
        END IF;
        IF rng = '*' THEN a := lo; b := hi;
        ELSIF rng LIKE '%-%' THEN a := split_part(rng, '-', 1)::int; b := split_part(rng, '-', 2)::int;
        ELSE a := rng::int; b := a;
        END IF;
        IF a < lo OR b > hi THEN RAISE EXCEPTION 'cron field value out of range [%,%]: %', lo, hi, part; END IF;
        SELECT result || array_agg(x) FROM generate_series(a, b, step) x INTO result;
    END LOOP;
    RETURN ARRAY(SELECT DISTINCT x FROM unnest(result) x ORDER BY x);
END;
$f$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION cron_next(expr text, from_ts timestamptz DEFAULT now()) RETURNS timestamptz AS $f$
DECLARE
    fields text[] := regexp_split_to_array(btrim(expr), '\s+');
    min_set int[]; hour_set int[]; dom_set int[]; mon_set int[]; dow_set int[];
    dom_restricted boolean; dow_restricted boolean;
    d date; h int; mi int; day_ok boolean;
BEGIN
    IF array_length(fields, 1) <> 5 THEN
        RAISE EXCEPTION 'cron expression must have 5 fields (min hour dom month dow): %', expr;
    END IF;
    min_set  := cron_field(fields[1], 0, 59);
    hour_set := cron_field(fields[2], 0, 23);
    dom_set  := cron_field(fields[3], 1, 31);
    mon_set  := cron_field(fields[4], 1, 12);
    dow_set  := cron_field(fields[5], 0, 6);
    dom_restricted := fields[3] <> '*';
    dow_restricted := fields[5] <> '*';
    d := date_trunc('minute', from_ts)::date;
    FOR i IN 0 .. 1465 LOOP -- ~4 years, enough to always catch a Feb-29-only schedule
        day_ok := mon_set @> ARRAY[EXTRACT(month FROM d)::int] AND (
            CASE WHEN dom_restricted AND dow_restricted -- vixie-cron semantics: dom/dow combine with OR when both are restricted
                 THEN dom_set @> ARRAY[EXTRACT(day FROM d)::int] OR dow_set @> ARRAY[EXTRACT(dow FROM d)::int]
                 ELSE dom_set @> ARRAY[EXTRACT(day FROM d)::int] AND dow_set @> ARRAY[EXTRACT(dow FROM d)::int]
            END);
        IF day_ok THEN
            FOREACH h IN ARRAY hour_set LOOP
                FOREACH mi IN ARRAY min_set LOOP
                    IF (d + h * interval '1 hour' + mi * interval '1 min') > from_ts THEN
                        RETURN d + h * interval '1 hour' + mi * interval '1 min';
                    END IF;
                END LOOP;
            END LOOP;
        END IF;
        d := d + 1;
    END LOOP;
    RAISE EXCEPTION 'no matching time found for cron expression % within search horizon', expr;
END;
$f$ LANGUAGE plpgsql STABLE;

ALTER TABLE task ADD COLUMN cron text; -- NULL for ordinary, non-cron tasks

CREATE OR REPLACE FUNCTION task_cron() RETURNS trigger AS $f$
DECLARE
    columns text;
BEGIN
    IF NEW.cron IS NULL THEN RETURN NEW; END IF;
    SELECT string_agg(quote_ident(attname), ', ' ORDER BY attnum) INTO columns
    FROM pg_attribute
    WHERE attrelid = TG_RELID AND attnum > 0 AND NOT attisdropped
      AND attname NOT IN ('id', 'plan', 'parent', 'start', 'stop', 'pid', 'state', 'error', 'output');
    EXECUTE format(
        'INSERT INTO %I (parent, plan, %s) SELECT id, cron_next(cron, statement_timestamp()), %s FROM %I WHERE id = $1',
        TG_TABLE_NAME, columns, columns, TG_TABLE_NAME
    ) USING NEW.id;
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER task_cron_trigger AFTER UPDATE OF state ON task
FOR EACH ROW WHEN (NEW.state IN ('DONE', 'FAIL')) EXECUTE FUNCTION task_cron();

cron_field() expands one field (*, a-b, */n, a-b/n, comma-separated lists of any of those) into a sorted array of matching values; cron_next() walks forward day by day (bounded to roughly four years, long enough to land on a Feb-29-only schedule) and, on a day that matches month/day-of-month/day-of-week, returns the first hour:minute in the field sets that’s after from_ts. Both are ordinary IMMUTABLE/STABLE SQL functions, so you can also call cron_next() directly to sanity-check an expression before using it. task_cron() reuses the same dynamic column-clone trick as the retry trigger above; only rows with cron IS NOT NULL are affected, so it’s safe to add alongside tasks that use plain repeat.

This parser only covers the classic 5-field syntax (numeric fields, *, ranges, steps, lists) — no named days/months (MON, JAN), no L/W/#, no seconds field. Extend cron_field()/cron_next() if you need those.

Notifications on completion

Rather than polling task, have it push: pg_notify() for listeners inside the same database, or a webhook (via the pg_curl extension) for anything outside it — both as a plain AFTER UPDATE OF state trigger, no core changes needed.

NOTIFY, no extra dependencies:

CREATE OR REPLACE FUNCTION task_notify() RETURNS trigger AS $f$
BEGIN
    PERFORM pg_notify('task_done', json_build_object(
        'id', NEW.id, 'group', NEW."group", 'state', NEW.state,
        'output', NEW.output, 'error', NEW.error
    )::text);
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER task_notify_trigger AFTER UPDATE OF state ON task
FOR EACH ROW WHEN (NEW.state IN ('DONE', 'FAIL')) EXECUTE FUNCTION task_notify();

Webhook, opt-in per row via its own hook column (NULL — no call), via pg_curl:

ALTER TABLE task ADD COLUMN hook text;

CREATE EXTENSION IF NOT EXISTS pg_curl;

CREATE OR REPLACE FUNCTION task_hook() RETURNS trigger AS $f$
BEGIN
    IF NEW.hook IS NULL THEN RETURN NEW; END IF;
    BEGIN
        PERFORM curl_easy_reset();
        PERFORM curl_easy_setopt_url(NEW.hook);
        PERFORM curl_easy_setopt_post(1);
        PERFORM curl_header_append('Content-Type', 'application/json');
        PERFORM curl_easy_setopt_postfields(convert_to(json_build_object(
            'id', NEW.id, 'group', NEW."group", 'state', NEW.state,
            'output', NEW.output, 'error', NEW.error
        )::text, 'UTF8'));
        PERFORM curl_easy_perform(timeout_ms => 5000);
    EXCEPTION WHEN OTHERS THEN NULL; -- a broken/slow endpoint must not fail the task itself
    END;
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER task_hook_trigger AFTER UPDATE OF state ON task
FOR EACH ROW WHEN (NEW.state IN ('DONE', 'FAIL')) EXECUTE FUNCTION task_hook();

Tested against a real local listener: both DONE and FAIL rows produced a correctly-formed JSON POST. The one thing to get right: this trigger runs synchronously, inside the same transaction that task_done()/task_error() (task.c) use to record the result — a webhook call that hangs or errors would otherwise hang or fail that write too, which is why the call is wrapped in its own EXCEPTION WHEN OTHERS and given an explicit timeout_ms rather than left to block indefinitely.

History/archive instead of a bare delete

Rather than pg_task.delete sending finished rows into nowhere, copy them into a partitioned archive table on the way out — an AFTER DELETE trigger, so it uniformly covers both task_delete()’s own auto-delete (delete = true with output/error both null) and any periodic cleanup you run yourself (e.g. a repeating pg_task job whose input is DELETE FROM task WHERE state IN ('DONE', 'GONE') AND plan < now() - interval '7 days'):

CREATE TABLE task_archive (LIKE task INCLUDING DEFAULTS, PRIMARY KEY (id, stop))
    PARTITION BY RANGE (stop);
CREATE TABLE task_archive_default PARTITION OF task_archive DEFAULT;
-- plus dated partitions, by hand or on a schedule -- see Automatic partitioning below

CREATE OR REPLACE FUNCTION task_archive() RETURNS trigger AS $f$
BEGIN
    BEGIN
        INSERT INTO task_archive SELECT OLD.*;
    EXCEPTION WHEN OTHERS THEN
        RAISE WARNING 'task_archive: failed to archive id = %: %', OLD.id, SQLERRM;
    END;
    RETURN OLD;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER task_archive_trigger AFTER DELETE ON task
FOR EACH ROW EXECUTE FUNCTION task_archive();

Two things confirmed by testing this end to end: a partitioned table’s unique/primary key must include the partition key, so this is PRIMARY KEY (id, stop), not just (id) — LIKE task INCLUDING ALL would carry over task’s own (id) primary key and fail with PRIMARY KEY constraint ... must include all partitioning columns. And the DEFAULT partition and the EXCEPTION WHEN OTHERS are both load-bearing, not redundant: like the webhook trigger above, this one runs inside the same transaction task_done()/task_error() use to delete the row, so an archive insert failing outright (no partition covers this stop, some constraint, …) would otherwise take that deletion — and the task’s own bookkeeping — down with it. With the DEFAULT partition in place a row with no dedicated partition still lands somewhere; with neither, the deletion still succeeds and only a WARNING is logged, verified by deliberately deleting both safety nets and confirming the row failed to archive but DELETE FROM task still returned normally.

Automatic partitioning via a repeating task

Rather than reaching for an extension like pg_partman to keep a partitioned table (e.g. task_archive above) supplied with future partitions, pg_task can do it itself — a repeating task is exactly a scheduler already living inside the database:

CREATE OR REPLACE FUNCTION task_archive_partition(lead interval DEFAULT '1 month') RETURNS void AS $f$
DECLARE
    from_ts timestamptz := date_trunc('month', now());
    to_ts timestamptz := date_trunc('month', now() + lead) + '1 month';
BEGIN
    WHILE from_ts < to_ts LOOP
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF task_archive FOR VALUES FROM (%L) TO (%L)',
            'task_archive_' || to_char(from_ts, 'YYYY_MM'), from_ts, from_ts + '1 month'
        );
        from_ts := from_ts + '1 month';
    END LOOP;
END;
$f$ LANGUAGE plpgsql;

INSERT INTO task (repeat, input) VALUES ('1 day', $$SELECT task_archive_partition()$$);

The repeating task keeps one month of lead time stocked up ahead of the current date; CREATE TABLE IF NOT EXISTS makes each daily run a no-op once that month’s partition already exists, so a missed or doubled-up firing is harmless. Widen lead or shrink the repeat interval for finer-grained (weekly/daily) partitioning.

Structured tags/metadata

data already exists for free-form user data, but filtering or grouping tasks by it means parsing whatever ad hoc format ended up in there. For external tooling (monitoring, orchestration, multi-tenant setups) that wants to query tasks by arbitrary, evolving labels without a schema migration for every new one, a jsonb column with a GIN index does the job — plain DDL, pg_task never touches a column it didn’t create itself:

ALTER TABLE task ADD COLUMN meta jsonb NOT NULL DEFAULT '{}';
CREATE INDEX task_meta_gin ON task USING gin (meta jsonb_path_ops);

INSERT INTO task (input, meta) VALUES ($$SELECT 1$$, '{"tenant": "acme", "job_type": "report"}');

SELECT * FROM task WHERE meta @> '{"tenant": "acme"}';

No trigger needed — meta is just carried along like any other column you add, jsonb_path_ops keeps the index small and fast for the @> containment queries this is normally used for. Reach for a proper typed column instead if you find yourself always filtering on the same one or two keys; jsonb earns its keep for labels whose shape you don’t want to commit to up front.

Idempotency key

Nothing stops the same logical job from being inserted twice — a retrying caller, an at-least-once event delivery, a cron trigger firing twice on a clock skew. A unique key, checked while the row is still “live”, turns a duplicate INSERT into a no-op instead of a duplicate run:

ALTER TABLE task ADD COLUMN idem_key text;

CREATE UNIQUE INDEX task_idem_key_uq ON task (idem_key)
    WHERE idem_key IS NOT NULL AND state IN ('PLAN', 'TAKE', 'WORK');

INSERT INTO task (input, idem_key) VALUES ($$...$$, 'daily-report-2026-09-17')
    ON CONFLICT (idem_key) WHERE idem_key IS NOT NULL AND state IN ('PLAN', 'TAKE', 'WORK') DO NOTHING;

The index has to be partial, and specifically scoped to PLAN/TAKE/WORK, not unconditional: a task that already reached DONE/FAIL/GONE/STOP has settled, so the same idem_key should be insertable again for the next occurrence of that logical job. An unconditional unique index would instead permanently block any future insert of that key the moment the first one finishes.

OpenTelemetry trace propagation

To see a chain of parent/child tasks as a single trace in Jaeger/Tempo/etc. rather than as unrelated rows, carry a trace context through the same parent link pg_task already tracks:

ALTER TABLE task ADD COLUMN trace_id text;
ALTER TABLE task ADD COLUMN span_id text;

CREATE OR REPLACE FUNCTION task_trace_insert() RETURNS trigger AS $f$
BEGIN
    IF NEW.parent IS NOT NULL AND NEW.trace_id IS NULL THEN
        SELECT trace_id INTO NEW.trace_id FROM task WHERE id = NEW.parent;
    END IF;
    IF NEW.trace_id IS NOT NULL THEN
        NEW.span_id := encode(gen_random_bytes(8), 'hex');
    END IF;
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER task_trace_insert_trigger BEFORE INSERT ON task
FOR EACH ROW EXECUTE FUNCTION task_trace_insert();

This only populates the columns; actually exporting them as OTLP spans needs a process outside pg_task itself, since none of the three execution paths (see Three ways to run input) can make an arbitrary HTTP/gRPC call — remote only speaks the PostgreSQL protocol. A periodic pg_task job (or an external poller) reading newly-DONE/FAIL rows and pushing them to a collector, e.g. via pg_curl against an OTLP/HTTP endpoint the same way the webhook pattern above posts JSON, is one way to close that gap.

Dry-run validation

Before letting a batch of externally-generated tasks anywhere near the real queue, validate input without letting its effects stick — actually run it, then unconditionally roll back, inside a BEFORE INSERT trigger:

ALTER TABLE task ADD COLUMN dry_run bool NOT NULL DEFAULT false;

CREATE OR REPLACE FUNCTION task_dry_run() RETURNS trigger AS $f$
BEGIN
    IF NOT NEW.dry_run THEN RETURN NEW; END IF;
    BEGIN
        EXECUTE NEW.input;
        RAISE EXCEPTION 'task_dry_run: rollback sentinel';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLERRM <> 'task_dry_run: rollback sentinel' THEN
                NEW.error := SQLERRM;
            END IF;
    END;
    NEW.state := 'STOP';
    RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER task_dry_run_trigger BEFORE INSERT ON task
FOR EACH ROW WHEN (NEW.dry_run) EXECUTE FUNCTION task_dry_run();

The BEGIN ... EXCEPTION ... END block is an implicit savepoint, so raising the sentinel exception right after EXECUTE unconditionally discards whatever input just did, real side effects included — this is closer to actually validating the statement than EXPLAIN would be (which rejects DDL and most utility statements outright), at the cost of genuinely executing input once. This does not reproduce all three execution paths equally: PL/pgSQL’s EXECUTE runs through SPI regardless of the task’s own spi setting, so a dry_run task using COPY ... TO/FROM a file or program — allowed for a real spi = off run — is rejected here (SPI_ERROR_COPY) even though it would have succeeded for real. Treat a clean dry run as “no syntax/permission/missing-object errors caught this way”, not as a full guarantee.

Capabilities and limitations

Capabilities: - run arbitrary SQL as soon as possible, at a specific time (plan), or repeatedly every N (repeat, counted from the original plan or from the actual finish time via drift); - catch execution errors without taking down a worker — the error text goes to error, the result to output; - run tasks concurrently with per-group concurrency control (group + max), or, with a negative max, pace them with a fixed delay instead — a built-in rate limiter; - run input on the local database or on an arbitrary remote one (remote), over a non-blocking connection that doesn’t block the scheduler from polling everything else; - run in several databases, schemas, tables and as several roles at once, via pg_task.json; - for non-remote tasks, run input either through the same dispatcher a real client connection uses (full DDL/COPY/multi-statement, spi = off) or through SPI (spi = on, faster and narrower) — see Three ways to run input above for how remote tasks differ from both; - reuse one session across several consecutive tasks of a group (pg_task.save), keeping temp tables/prepared statements/settings between runs; - bound a task’s own run time (timeout) independently of how long the executing process itself lives (live, count); - react to a new or changed task almost immediately rather than only on the next poll, thanks to the advisory-lock/pg_cancel_backend wake-up described in Architecture above; - cancel a running task from SQL (UPDATE task SET state = 'STOP' WHERE id = ...), local or remote, leaving it distinguishably at STOP rather than FAIL, and without scheduling its next repeat occurrence — see Task state machine; - recover from a worker crashing mid-task — orphaned TAKE/WORK rows are reset to PLAN every pg_task.reset; - format results (delimiter, quoting, escaping, NULL representation, headers); - run every task as the role that actually inserted it, not as the scheduler’s own connecting role (see Security considerations below).

GUCs, and most task columns, cascade through up to five levels — the config file, then per-database, per-role and per-session overrides, and finally the value on the row itself — the most specific one wins.

Limitations: - if the executor crashes in the narrow window between STOP being set and the cancellation actually completing, the row can be left at STOP with stop/error never filled in — work_reset deliberately leaves STOP rows alone (unlike TAKE/WORK) so a cancelled task isn’t accidentally resurrected back to PLAN; - no built-in dashboard or UI — monitoring is SELECT * FROM task and, as needed, pg_locks/pg_stat_activity; - tasks only run where the table can be written to — a read-only replica can’t run a scheduler against it; - no coordination across independent clusters — if the same task table or the same remote target is reachable from more than one place at once, consistency is on you; the advisory locks only protect against races inside one server instance; - building requires network access to GitHub, or a pre-existing source tree (see Build above) — the extension pulls part of the matching version’s real postgres.c at build time rather than just linking against an installed server; - only the server branches/versions actually exercised by CI are supported — a brand-new major version may need a follow-up patch before the code extracted from its postgres.c matches again; - with the default spi = off, a task has exactly the privileges of a normal client session for its role, including COPY ... TO/FROM PROGRAM if the role can do that — this follows directly from running input as a real client would, not from a bug; if task authors aren’t fully trusted, use spi = on or restrict the role’s privileges (see Security considerations below).

Security considerations

pg_task executes the raw SQL text stored in task.input as the role recorded in task.user. That column defaults to current_user at insert time, is force-overwritten to current_user by a BEFORE INSERT trigger regardless of what value the client supplies, and is immutable afterwards (a BEFORE UPDATE OF "user" trigger rejects any change). Since task.user is trustworthy, execution switches to it (SET ROLE) only for the duration of running input, then reverts to the worker’s own connecting role (configured via pg_task.user, default: postgres) before any further housekeeping (recording state/output/error, scheduling repeats, etc.).

This means a task only ever runs with the privileges of whoever actually inserted it, provided the connecting role (pg_task.user) is not itself granted broad membership in every role — see below.

Things to keep in mind:

  • pg_task.user no longer needs to be a superuser, or even privileged, to let different authors run tasks with their own rights — SET ROLE only requires plain membership (or, on PostgreSQL 16+, membership WITH SET TRUE) rather than superuser. Grant pg_task.user membership only in the specific roles it should be allowed to execute tasks as: GRANT author_role TO pg_task_user WITH INHERIT FALSE, SET TRUE;. A role with INSERT on task but no such grant to pg_task.user cannot get its tasks executed with anyone else’s privileges — the SET ROLE inside the worker simply fails and the task ends up FAIL.
  • If you do keep pg_task.user privileged (e.g. postgres), it is still capable of running as any role on the server — restrict who can INSERT into task accordingly (REVOKE INSERT ON task FROM PUBLIC and grant it selectively, or only allow inserts through a SECURITY DEFINER wrapper function). The user column protects against forged identity, not against a task simply running with its author’s genuinely-granted privileges.
  • pg_task.spi = off (the default) executes input as a normal top-level statement, so anything the effective role (task.user) is allowed to do is possible — including COPY ... TO/FROM a server-side file or program if that role has the required privilege. pg_task.spi = on runs input through SPI instead, where COPY in any form is rejected outright (SPI_ERROR_COPY), which removes that specific class of vector at the cost of SPI’s other limitations (no explicit transaction control in input, only the last statement’s result on pre-PostgreSQL 10 backends, etc.).
  • The remote column’s password requirement now reflects the actual task author. When a task sets remote, pg_task refuses to connect without a password unless task.user is a superuser (work.c’s work_remote). Once connected, execution on the remote server is governed entirely by the credentials in the remote connection string — task.user/SET ROLE have no effect there, since the remote server has its own, independent role catalog. Don’t put passwordless/trust-authenticated connection strings within reach of task authors you don’t fully trust.
  • Review which extensions/functions any role granted to pg_task.user can execute (dblink, adminpack, large-object functions, pg_read_file, etc.) and revoke what isn’t needed.
  • Consider auditing (e.g. pgaudit) or alerting on task.input containing sensitive constructs (COPY ... PROGRAM, CREATE FUNCTION ... LANGUAGE c, ALTER SYSTEM, dblink, …) if task authorship is not fully trusted.