Awesome
pg_background: Run PostgreSQL Commands in Background Workers
This extension allows you to execute arbitrary SQL commands in background worker processes within PostgreSQL (version >= 9.5). It provides a convenient way to offload long-running tasks, perform operations asynchronously, and implement autonomous transactions.
Features
- Execute any SQL command in a background worker.
- Retrieve the result of the background command.
- Detach background workers to run independently.
- Enhanced error handling and command result reporting.
- Built-in functions for managing privileges.
Installation
-
Prerequisites:
- PostgreSQL version >= 9.5
- Ensure
pg_config
is in yourPATH
-
Build and Install:
make sudo make install
-
Enable the Extension:
psql -h your_server -p 5432 -d your_database -c "CREATE EXTENSION pg_background;"
Usage
SQL API:
pg_background_launch(sql_command TEXT, queue_size INTEGER DEFAULT 65536):
Executes sql_command
in a background worker. queue_size
determines the message queue size (default: 65536). Returns the background worker's process ID.
pg_background_result(pid INTEGER):
Retrieves the result of the command executed by the background worker with process ID pid
.
pg_background_detach(pid INTEGER):
Detaches the background worker with process ID pid
, allowing it to run independently.
Examples
-- Run VACUUM in the background
SELECT pg_background_launch('VACUUM VERBOSE public.your_table');
-- Retrieve the result
SELECT pg_background_result(12345); -- Replace 12345 with the actual pid
-- Run a command and wait for the result
SELECT pg_background_result(pg_background_launch('SELECT count(*) FROM your_table'));
Privilege Management
For security, grant privileges to a dedicated role:
-- Create a role
CREATE ROLE pgbackground_role;
-- Grant privileges using the built-in function
SELECT grant_pg_background_privileges('pgbackground_role', TRUE);
-- Revoke privileges
SELECT revoke_pg_background_privileges('pgbackground_role', TRUE);
Use Cases
Background Tasks: Offload long-running tasks like VACUUM, ANALYZE, or CREATE INDEX CONCURRENTLY to background workers.
Autonomous Transactions: Implement autonomous transactions more effectively than with dblink.
Procedural Languages: Execute commands from procedural languages like PL/pgSQL without blocking.
More examples:
SELECT pg_background_launch('vacuum verbose public.sales');
pg_background_launch
----------------------
11088
(1 row)
SELECT * FROM pg_background_result(11088) as (result text);
INFO: vacuuming "public.sales"
INFO: index "sales_pkey" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sales": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_1866942"
INFO: index "pg_toast_1866942_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1866942": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
result
--------
VACUUM
(1 row)
If user wants to execute the command wait for result, then they can use following example:
SELECT * FROM pg_background_result(pg_background_launch('vacuum verbose public.sales')) as (result TEXT);
INFO: vacuuming "public.sales"
INFO: index "sales_pkey" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sales": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_1866942"
INFO: index "pg_toast_1866942_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1866942": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
result
--------
VACUUM
(1 row)
Granting/Revoking permissions
CREATE ROLE pgbackground_role;
CREATE ROLE
SELECT grant_pg_background_privileges(user_name => 'pgbackground_role', print_commands => true);
INFO: Executed command: GRANT EXECUTE ON FUNCTION pg_background_launch(pg_catalog.text, pg_catalog.int4) TO pgbackground_role
INFO: Executed command: GRANT EXECUTE ON FUNCTION pg_background_result(pg_catalog.int4) TO pgbackground_role
INFO: Executed command: GRANT EXECUTE ON FUNCTION pg_background_detach(pg_catalog.int4) TO pgbackground_role
┌────────────────────────────────┐
│ grant_pg_background_privileges │
├────────────────────────────────┤
│ t │
└────────────────────────────────┘
(1 row)
If you want to revoke permission from a specific role, the following function can be used:
SELECT revoke_pg_background_privileges(user_name => 'pgbackground_role', print_commands => true);
INFO: Executed command: REVOKE EXECUTE ON FUNCTION pg_background_launch(pg_catalog.text, pg_catalog.int4) FROM pgbackground_role
INFO: Executed command: REVOKE EXECUTE ON FUNCTION pg_background_result(pg_catalog.int4) FROM pgbackground_role
INFO: Executed command: REVOKE EXECUTE ON FUNCTION pg_background_detach(pg_catalog.int4) FROM pgbackground_role
┌─────────────────────────────────┐
│ revoke_pg_background_privileges │
├─────────────────────────────────┤
│ t │
└─────────────────────────────────┘
(1 row)
License
GNU General Public License v3.0
Author Information
Authors:
- Vibhor Kumar
- @a-mckinley
- @rjuju
- @svorcmar
- @egor-rogov
- @RekGRpth
- @Hiroaki-Kubota