Версия: 1.3

GitHub

pg_background позволяет выполнять произвольные SQL-команды в фоновых рабочих процессах в рамках Tantor BE. Это обеспечивает удобный способ разгрузки длительных задач, выполнения операций асинхронно и реализации автономных транзакций.

Чтобы установить расширение, просто выполните команду:

CREATE EXTENSION pg_background;

-- 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'));

Для безопасности предоставьте привилегии выделенной роли:

-- 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);

Фоновые задачи: Переносите длительные задачи, такие как VACUUM, ANALYZE или CREATE INDEX CONCURRENTLY, на фоновые процессы.

Автономные транзакции: Реализуйте автономные транзакции более эффективно, чем с помощью dblink.

Процедурные языки: Выполняйте команды из процедурных языков, таких как PL/pgSQL, без блокировки.

Отсоединяет фоновый рабочий процесс с идентификатором процесса pid, позволяя ему работать независимо.

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)

Если пользователь хочет выполнить команду и дождаться результата, то он может использовать следующий пример:

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)

Предоставление/Отзыв разрешений

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)

Если вы хотите отозвать разрешение у конкретной роли, можно использовать следующую функцию:

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)