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
0002-pg_background_worker_as_client_of_bgsession_trial.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers