Date: Fri, 6 Dec 2019 19:25:21 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: How to run in parallel in Postgres
Message-ID: <20191207012521.GU2082@telsasoft.com>
References: 
 <HE1P189MB026622ECFF2400B1932E4A2D9D5D0@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <HE1P189MB026622ECFF2400B1932E4A2D9D5D0@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Thu, Dec 05, 2019 at 12:10:42PM +0000, Lars Aksel Opsahl wrote:
> I have a function that prepares data, so the big job can be run it in parallel.
> 
> 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  ?

SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',1,28);
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',2,28);
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',3,28);
...

I see that find_overlap_gap_single_cell creates tables, so cannot be run in parallel.
Maybe you could consider rewriting it to return data to its caller instead.
You'd also need to mark it as PARALLEL SAFE, of course.
Your other functions involved should be PARALLEL SAFE too.

Justin