/pg_background

pg_background

Primary LanguageCGNU General Public License v3.0GPL-3.0

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

  1. Prerequisites:

    • PostgreSQL version >= 9.5
    • Ensure pg_config is in your PATH

  2. Build and Install:

    make
sudo make install

  3. 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