pg_background

pig build pkg pg_background;# build deb

pig repo add pgsql -u # add both repo and update cache

pig install pg_background;# install via package name, for the active PG version  pig install pg_background -v 18; # install for PG 18 pig install pg_background -v 17; # install for PG 17 pig install pg_background -v 16; # install for PG 16 pig install pg_background -v 15; # install for PG 15 pig install pg_background -v 14; # install for PG 14

CREATE EXTENSION pg_background;

CREATE EXTENSION pg_background;  -- Launch a background job SELECT * FROM pg_background_launch_v2(  'SELECT count(*) FROM large_table' ) AS handle; -- pid | cookie -- -------+------------------- -- 12345 | 1234567890123456  -- Retrieve results (one-time consumption) SELECT * FROM pg_background_result_v2(12345, 1234567890123456) AS (count BIGINT);  -- Fire-and-forget (no result needed) SELECT * FROM pg_background_submit_v2(  'INSERT INTO audit_log (ts, event) VALUES (now(), ''system_check'')' ) AS handle;

-- Cancel a running job SELECT pg_background_cancel_v2(pid, cookie);  -- Wait for completion SELECT pg_background_wait_v2(pid, cookie);  -- Wait with timeout (returns true if completed) SELECT pg_background_wait_v2_timeout(pid, cookie, 5000);  -- List active workers SELECT * FROM pg_background_list_v2() AS (  pid int4, cookie int8, launched_at timestamptz,  user_id oid, queue_size int4, state text,  sql_preview text, last_error text, consumed bool );

-- From within worker SQL SELECT pg_background_progress(50, 'Halfway done');  -- From launcher (check progress) SELECT * FROM pg_background_get_progress_v2(pid, cookie);

SELECT pg_background_launch('VACUUM VERBOSE my_table') AS pid \gset SELECT * FROM pg_background_result(:pid) AS (result TEXT); SELECT pg_background_detach(:pid);