You signed in with another tab or window.Reload to refresh your session.You signed out in another tab or window.Reload to refresh your session.You switched accounts on another tab or window.Reload to refresh your session.Dismiss alert
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.
Executessql_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 IDpid.
pg_background_detach(pid INTEGER):Detaches the background worker with process IDpid, allowing it to run independently.
Examples
-- Run VACUUM in the backgroundSELECT pg_background_launch('VACUUM VERBOSE public.your_table');-- Retrieve the resultSELECT*FROM pg_background_result(12345) foo(resultTEXT);-- Replace 12345 with the actual pid-- Run a command and wait for the resultSELECT*FROM pg_background_result(pg_background_launch('SELECT count(*) FROM your_table'))AS foo(countBIGINT);
Privilege Management
For security, grant privileges to a dedicated role:
-- Create a roleCREATE ROLE pgbackground_role;-- Grant privileges using the built-in functionSELECT grant_pg_background_privileges('pgbackground_role', TRUE);-- Revoke privilegesSELECT 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 (resulttext);INFO: vacuuming"public.sales"INFO: index"sales_pkey" now contains0 row versionsin1 pagesDETAIL:0 index row versions were removed.0 index pages have been deleted,0 are currently reusable.CPU0.00s/0.00u sec elapsed0.00 sec.INFO:"sales": found0 removable,0 nonremovable row versionsin0 out of0 pagesDETAIL:0 dead row versions cannot be removed yet.There were0 unused item pointers.Skipped0 pages due to buffer pins.0 pages are entirely empty.CPU0.00s/0.00u sec elapsed0.00 sec.INFO: vacuuming"pg_toast.pg_toast_1866942"INFO: index"pg_toast_1866942_index" now contains0 row versionsin1 pagesDETAIL:0 index row versions were removed.0 index pages have been deleted,0 are currently reusable.CPU0.00s/0.00u sec elapsed0.00 sec.INFO:"pg_toast_1866942": found0 removable,0 nonremovable row versionsin0 out of0 pagesDETAIL:0 dead row versions cannot be removed yet.There were0 unused item pointers.Skipped0 pages due to buffer pins.0 pages are entirely empty.CPU0.00s/0.00u sec elapsed0.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 (resultTEXT);INFO: vacuuming"public.sales"INFO: index"sales_pkey" now contains0 row versionsin1 pagesDETAIL:0 index row versions were removed.0 index pages have been deleted,0 are currently reusable.CPU0.00s/0.00u sec elapsed0.00 sec.INFO:"sales": found0 removable,0 nonremovable row versionsin0 out of0 pagesDETAIL:0 dead row versions cannot be removed yet.There were0 unused item pointers.Skipped0 pages due to buffer pins.0 pages are entirely empty.CPU0.00s/0.00u sec elapsed0.00 sec.INFO: vacuuming"pg_toast.pg_toast_1866942"INFO: index"pg_toast_1866942_index" now contains0 row versionsin1 pagesDETAIL:0 index row versions were removed.0 index pages have been deleted,0 are currently reusable.CPU0.00s/0.00u sec elapsed0.00 sec.INFO:"pg_toast_1866942": found0 removable,0 nonremovable row versionsin0 out of0 pagesDETAIL:0 dead row versions cannot be removed yet.There were0 unused item pointers.Skipped0 pages due to buffer pins.0 pages are entirely empty.CPU0.00s/0.00u sec elapsed0.00 sec. result-------- VACUUM(1 row)
Granting/Revoking permissions
CREATE ROLE pgbackground_role;CREATE ROLESELECT grant_pg_background_privileges(user_name=>'pgbackground_role', print_commands=> true);INFO: Executed command:GRANT EXECUTEON FUNCTION pg_background_launch(pg_catalog.text,pg_catalog.int4) TO pgbackground_roleINFO: Executed command:GRANT EXECUTEON FUNCTION pg_background_result(pg_catalog.int4) TO pgbackground_roleINFO: Executed command:GRANT EXECUTEON 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 EXECUTEON FUNCTION pg_background_launch(pg_catalog.text,pg_catalog.int4)FROM pgbackground_roleINFO: Executed command:REVOKE EXECUTEON FUNCTION pg_background_result(pg_catalog.int4)FROM pgbackground_roleINFO: Executed command:REVOKE EXECUTEON FUNCTION pg_background_detach(pg_catalog.int4)FROM pgbackground_role┌─────────────────────────────────┐│ revoke_pg_background_privileges │├─────────────────────────────────┤│ t │└─────────────────────────────────┘(1 row)