Re: How to run in parallel in Postgres

>From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>

>Sent: Thursday, December 5, 2019 5:42 PM

>To: Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no>; pgsql-performance(at)lists(dot)postgresql(dot)org <pgsql-performance(at)lists(dot)postgresql(dot)org>

>Subject: Re: How to run in parallel in Postgres

>

>On Thu, 2019-12-05 at 12:10 +0000, Lars Aksel Opsahl wrote:

>> have a function that prepares data, so the big job can be run it in parallel.

>>

>> Today I have solved this by using "Gnu parallel" like this.

>> psql testdb -c"\! psql -t -q -o /tmp/run_cmd.sql testdb -c\"SELECT find_overlap_gap_make_run_cmd('sl_lop.overlap_gap_input_t1','geom',4258,'sl_lop.overlap_gap_input_t1_res',50);\"; parallel -j 4

>> psql testdb -c :::: /tmp/run_cmd.sql" 2>> /tmp/analyze.log;

>>

>> The problem here is that I depend on external code which may not be installed.

>>

>> Since Postgres now supports parallel I was wondering if it's easy to trigger parallel dynamically created SQL calls.

>>

>> If you look at https://github.com/larsop/find-overlap-and-gap/blob/master/src/test/sql/regress/find_overlap_and_gap.sql you see that

>> find_overlap_gap_make_run_cmd generates as set of 28 sql calls.

>>

>> So is it in a simple way possible to use Postgres parallel functionality to call this 28 functions i parallel so I don't have dependent on externally install programs ?

>>

>> When this 28 sql calls are done, the find_overlap_gap_make_run_cmd may continue to the next step of work. So the function that triggers parallel calls wait for them complete and then may start on

>> the next step of work.

>

>You cannot run several queries in parallel in a PostgreSQL function.

>

>You may want to have a look at PL/Proxy which might be used for things like that.

>

>Yours,

>Laurenz Albe

>--

>Cybertec | https://www.cybertec-postgresql.com

Hi

Thanks, I checked it out.

If I understand it correct I have to write the code using plproxy syntax and this means if plproxy is not installed the code will fail.

So the only way now to use built in parallel functionality in Postgres is to use C ?

Do you believe it will possible in the future to run parallel calls from a PostgresSQL function (or is impossible/difficult because of design) ?

Lars