Re: pg_background contrib module proposal

Hi all,

As we have discussed previously, we need to rework this patch as a client of
Peter Eisentraut's background sessions code[1].

Attaching trial version patch to discussed possible design and api.
We could have following APIs :

• pg_background_launch : This function start and stores new background
session, and returns the process id of background worker.

• pg_background_run : This API takes the process id and SQL command as
input parameter. Using this process id, stored worker's session is
retrieved and give SQL command is executed under it.

• pg_background_result : This API takes the process id as input
parameter and returns the result of command executed thought the
background worker session. Same as it was before but now result can
be fetch in LIFO order i.e. result of last executed query using
pg_background_run will be fetched first.

• pg_background_detach : This API takes the process id and detach the
background process. Stored worker's session is not dropped until this
called.

• TBC : API to discard result of last query or discard altogether?

• TBC : How about having one more api to see all existing sessions ?

Kindly share your thoughts/suggestions. Note that attach patch is WIP
version, code, comments & behaviour could be vague.

------------------
Quick demo:
------------------
Apply attach patch to the top of Peter Eisentraut's
0001-Add-background-sessions.patch[1]

postgres=# select pg_background_launch();
pg_background_launch
----------------------
21004
(1 row)

postgres=# select pg_background_run(21004, 'vacuum verbose foo');
pg_background_run
-------------------

(1 row)

postgres=# select * from pg_background_result(21004) as (x text);
INFO: vacuuming "public.foo"
INFO: "foo": found 0 removable, 5 nonremovable row versions in 1 out of 1 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: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
x
--------
VACUUM
(1 row)

postgres=# select pg_background_run(21004, 'select * from foo');
pg_background_run
-------------------

(1 row)

postgres=# select * from pg_background_result(21004) as (x int);
x
---
1
2
3
4
5
(5 rows)

postgres=# select pg_background_detach(21004);
pg_background_detach
----------------------

(1 row)

References :
[1] https://www.postgresql.org/message-id/e1c2d331-ee6a-432d-e9f5-dcf85cffaf29%402ndquadrant.com.

Regards,
Amul Sul