Re: How to run in parallel in Postgres

[Lars Aksel Opsahl]

>From: Laurenz Albe 

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

>To: Lars Aksel Opsahl ; 
>pgsql-performance@lists.postgresql.org 

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

>

>On Thu, 2019-12-05 at 12:10 +, 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

RE: autovacuum locking question

[Mike Schanne]

Is this what you are referring to?

- Prevent VACUUM from trying to freeze an old multixact ID involving a 
still-running transaction (Nathan Bossart, Jeremy Schneider)
This case would lead to VACUUM failing until the old transaction terminates.
https://www.postgresql.org/docs/release/9.6.16/

Thanks,
Mike

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Thursday, December 05, 2019 6:49 PM
To: Mike Schanne
Cc: 'pgsql-performa...@postgresql.org'
Subject: Re: autovacuum locking question

Mike Schanne  writes:
> I am investigating a performance problem in our application and am seeing 
> something unexpected in the postgres logs regarding the autovacuum.

> 2019-12-01 13:05:39.029 
> UTC,"wb","postgres",6966,"127.0.0.1:53976",5ddbd990.1b36,17099,"INSERT 
> waiting",2019-11-25 13:39:28 UTC,12/1884256,12615023,LOG,0,"process 6966 
> still waiting for RowExclusiveLock on relation 32938 of database 32768 after 
> 1000.085 ms","Process holding the lock: 6045. Wait queue: 6966.","INSERT 
> INTO myschema.mytable (...) VALUES (...) RETURNING 
> process.mytable.mytable_id",13,,""
> 2019-12-01 13:05:39.458 UTC,,,6045,,5de3b800.179d,1,,2019-12-01 12:54:24 
> UTC,10/417900,0,ERROR,57014,"canceling autovacuum task","automatic vacuum 
> of table ""postgres.myschema.mytable"""""

> My understanding from reading the documentation was that a vacuum can run 
> concurrently with table inserts/updates, but from reading the logs it appears 
> they are conflicting over a row lock.  This particular table gets very 
> frequent inserts/updates (10-100 inserts / sec) so I am concerned that if the 
> autovacuum is constantly canceled, then the table never gets cleaned and its 
> performance will continue to degrade over time.  Is it expected for the 
> vacuum to be canceled by an insert in this way?

The main part of an autovacuum operation should go through OK.  The only part 
that would get canceled in response to somebody taking a non-exclusive lock is 
the last step, which is truncation of unused blocks at the end of the table; 
that requires an exclusive lock.  Normally, skipping that step isn't terribly 
problematic.

> We are using postgres 9.6.10.

IIRC, we've made improvements in this area since 9.6, to allow a partial 
truncation to be done if someone wants the lock, rather than just failing 
entirely.

regards, tom lane





This email is non-binding, is subject to contract, and neither Kulicke and 
Soffa Industries, Inc. nor its subsidiaries (each and collectively “K&S”) shall 
have any obligation to you to consummate the transactions herein or to enter 
into any agreement, other than in accordance with the terms and conditions of a 
definitive agreement if and when negotiated, finalized and executed between the 
parties. This email and all its contents are protected by International and 
United States copyright laws. Any reproduction or use of all or any part of 
this email without the express written consent of K&S is prohibited.

RE: autovacuum locking question

[Mike Schanne]

The error is not actually showing up very often (I have 8 occurrences from 
11/29 and none since then).  So maybe I should not be concerned about it.  I 
suspect we have an I/O bottleneck from other logs (i.e. long checkpoint sync 
times), so this error may be a symptom rather than the cause.

From: Jeff Janes [mailto:jeff.ja...@gmail.com]
Sent: Thursday, December 05, 2019 6:55 PM
To: Mike Schanne
Cc: pgsql-performa...@postgresql.org
Subject: Re: autovacuum locking question

On Thu, Dec 5, 2019 at 5:26 PM Mike Schanne 
mailto:mscha...@kns.com>> wrote:
Hi,
I am investigating a performance problem in our application and am seeing 
something unexpected in the postgres logs regarding the autovacuum.

2019-12-01 13:05:39.029 
UTC,"wb","postgres",6966,"127.0.0.1:53976",5ddbd990.1b36,17099,"INSERT
 waiting",2019-11-25 13:39:28 UTC,12/1884256,12615023,LOG,0,"process 6966 
still waiting for RowExclusiveLock on relation 32938 of database 32768 after 
1000.085 ms","Process holding the lock: 6045. Wait queue: 6966.","INSERT 
INTO myschema.mytable (...) VALUES (...) RETURNING 
process.mytable.mytable_id",13,,""
2019-12-01 13:05:39.458 UTC,,,6045,,5de3b800.179d,1,,2019-12-01 12:54:24 
UTC,10/417900,0,ERROR,57014,"canceling autovacuum task","automatic vacuum 
of table ""postgres.myschema.mytable"""""

My understanding from reading the documentation was that a vacuum can run 
concurrently with table inserts/updates, but from reading the logs it appears 
they are conflicting over a row lock.  This particular table gets very frequent 
inserts/updates (10-100 inserts / sec) so I am concerned that if the autovacuum 
is constantly canceled, then the table never gets cleaned and its performance 
will continue to degrade over time.  Is it expected for the vacuum to be 
canceled by an insert in this way?

We are using postgres 9.6.10.

If the vacuum finds a lot of empty pages at the end of the table, it will try 
to truncate them and takes a strong lock to do so.  It is supposed to check 
every 20ms to see if anyone else is blocked on that lock, at which point it 
stops doing the truncation and releases the lock.  So it should never get 
"caught" holding the lock in order to be cancelled.  Is your setting for 
deadlock_timeout much lower than usual?  Also, if the truncation is bogged down 
in very slow IO, perhaps it doesn't actually get around to checking ever 20ms 
despite its intentionsl

How often have you seen it in the logs?

Cheers,

Jeff



This email is non-binding, is subject to contract, and neither Kulicke and 
Soffa Industries, Inc. nor its subsidiaries (each and collectively “K&S”) shall 
have any obligation to you to consummate the transactions herein or to enter 
into any agreement, other than in accordance with the terms and conditions of a 
definitive agreement if and when negotiated, finalized and executed between the 
parties. This email and all its contents are protected by International and 
United States copyright laws. Any reproduction or use of all or any part of 
this email without the express written consent of K&S is prohibited.

Re: autovacuum locking question

[Tom Lane]

Mike Schanne  writes:
> Is this what you are referring to?
> - Prevent VACUUM from trying to freeze an old multixact ID involving a 
> still-running transaction (Nathan Bossart, Jeremy Schneider)
> This case would lead to VACUUM failing until the old transaction terminates.
> https://www.postgresql.org/docs/release/9.6.16/

Hmmm ... after digging through the commit log, it seems the improvements
I was thinking of were all pre-9.6.  The only post-9.6 vacuum truncation
performance fix I can find is

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=7e26e02ee

which came in in v10.

regards, tom lane

unexpected result for wastedbytes query after vacuum full

[Mike Schanne]

Hi all,

This question is somewhat related to my previous question:
https://www.postgresql.org/message-id/0871fcf35ceb4caa8a2204ca9c38e330%40USEPRDEX1.corp.kns.com

I was attempting to measure the benefit of doing a VACUUM FULL on my database.  
I was using the query found here:

https://wiki.postgresql.org/wiki/Show_database_bloat

However, I got an unexpected result in that the "wastedbytes" value actually 
increased for some tables after doing the vacuum.

Before VACUUM FULL:
current_database |   schemaname   | tablename | tbloat | 
wastedbytes |  iname  | 
ibloat | wastedibytes
--++---++-+-++--
postgres | myschema | mytableA  |1.1 |
74440704 | myindex1 |   
 0.2 |0
postgres | myschema | mytableA  |1.1 |
74440704 | myindex2 |   
 0.2 |0
postgres | myschema | mytableA  |1.1 |
74440704 | myindex3 |   
 0.2 |0
postgres | myschema | mytableA  |1.1 |
74440704 | myindex4 |   
 0.2 |0
postgres | myschema | mytableB  |1.0 |
63324160 | myindex5 |   
 0.0 |0
...
After VACUUM FULL:
  current_database |   schemaname   | tablename | tbloat | 
wastedbytes |  iname | 
ibloat | wastedibytes
--++---++-+-++--
postgres | myschema | mytableA  |1.1 |
74506240 | myindex4  |  
  0.2 |0
postgres | myschema | mytableA  |1.1 |
74506240 | myindex3  |  
  0.2 |0
postgres | myschema | mytableA  |1.1 |
74506240 | myindex2  |  
  0.2 |0
postgres | myschema | mytableA  |1.1 |
74506240 | myindex1  |  
  0.2 |0
postgres | myschema | mytableB  |1.0 |
63332352 | myindex5  |  
  0.0 |0
...

This is the schema for mytableA above:

Column |Type |   
Modifiers
---+-+
colA  | integer | not null default 
nextval('myschema.myseq'::regclass)
colB  | integer |
colC  | integer |
colD  | timestamp without time zone |
colE  | json|
colF  | integer |
colG  | integer |

I was wondering if the fact that we use a json column could be interfering with 
the wastedbytes calculation.  Can anyone explain how wastedbytes could increase 
from a vacuum?

Thanks,
Mike



This email is non-binding, is subject to contract, and neither Kulicke and 
Soffa Industries, Inc. nor its subsidiaries (each and collectively "K&S") shall 
have any obligation to you to consummate the transactions herein or to enter 
into any agreement, other than in accordance with the terms and conditions of a 
definitive agreement if and when negotiated, finalized and executed between the 
parties. This email and all its contents are protected by International and 
United States copyright laws. Any reproduction or use of all or any part of 
this email without the express written consent of K&S is prohibited.

Re: autovacuum locking question

[Tom Lane]

Mike Schanne  writes:
> The error is not actually showing up very often (I have 8 occurrences from 
> 11/29 and none since then).  So maybe I should not be concerned about it.  I 
> suspect we have an I/O bottleneck from other logs (i.e. long checkpoint sync 
> times), so this error may be a symptom rather than the cause.

Well, it's also an inherently non-repeating problem: once some iteration
of autovacuum has managed to truncate away the large amount of trailing
dead space that the file presumably had, later runs won't need to do
that.

Of course, if you have a usage pattern that repeatedly bloats the table
with lots of stuff-to-be-vacuumed, the issue could recur that way.

regards, tom lane

Re: autovacuum locking question

[Jeff Janes]

On Fri, Dec 6, 2019 at 10:55 AM Mike Schanne  wrote:

> The error is not actually showing up very often (I have 8 occurrences from
> 11/29 and none since then).  So maybe I should not be concerned about it.
> I suspect we have an I/O bottleneck from other logs (i.e. long checkpoint
> sync times), so this error may be a symptom rather than the cause.
>

I think that at the point it is getting cancelled, it has done all the work
except the truncation of the empty pages, and reporting the results (for
example, updating n_live_tup  and n_dead_tup).  If this happens
every single time (neither last_autovacuum nor last_vacuum ever advances)
it will eventually cause problems.  So this is mostly a symptom, but not
entirely.  Simply running a manual vacuum should fix the reporting
problem.  It is not subject to cancelling, so it will detect it is blocking
someone and gracefully bow.  Meaning it will suspend the truncation, but
will still report its results as normal.

Reading the table backwards in order to truncate it might be contributing
to the IO problems as well as being a victim of those problems.  Upgrading
to v10 might help with this, as it implemented a prefetch where it reads
the table forward in 128kB chunks, and then jumps backwards one chunk at a
time.  Rather than just reading backwards 8kB at a time.

Cheers,

Jeff

>

Re: autovacuum locking question

[Justin Pryzby]

On Thu, Dec 05, 2019 at 06:49:06PM -0500, Tom Lane wrote:
> The only part that would get canceled in response to somebody taking a
> non-exclusive lock is the last step, which is truncation of unused blocks at
> the end of the table; that requires an exclusive lock.

On Thu, Dec 05, 2019 at 06:55:02PM -0500, Jeff Janes wrote:
> If the vacuum finds a lot of empty pages at the end of the table, it will
> try to truncate them and takes a strong lock to do so.

Should the exclusive lock bit be documented ?
https://www.postgresql.org/docs/12/explicit-locking.html

Re: autovacuum locking question

[MichaelDBA]

And Just to reiterate my own understanding of this...

autovacuum priority is less than a user-initiated request, so issuing a 
manual vacuum (user-initiated request) will not result in being cancelled.


Regards,
Michael Vitale

Jeff Janes wrote on 12/6/2019 12:47 PM:
On Fri, Dec 6, 2019 at 10:55 AM Mike Schanne > wrote:


The error is not actually showing up very often (I have 8
occurrences from 11/29 and none since then).  So maybe I should
not be concerned about it.  I suspect we have an I/O bottleneck
from other logs (i.e. long checkpoint sync times), so this error
may be a symptom rather than the cause.


I think that at the point it is getting cancelled, it has done all the 
work except the truncation of the empty pages, and reporting the 
results (for example, updating n_live_tup  and n_dead_tup).  If this 
happens every single time (neither last_autovacuum nor last_vacuum 
ever advances) it will eventually cause problems.  So this is mostly a 
symptom, but not entirely.  Simply running a manual vacuum should fix 
the reporting problem.  It is not subject to cancelling, so it will 
detect it is blocking someone and gracefully bow.  Meaning it will 
suspend the truncation, but will still report its results as normal.
Reading the table backwards in order to truncate it might be 
contributing to the IO problems as well as being a victim of those 
problems.  Upgrading to v10 might help with this, as it implemented a 
prefetch where it reads the table forward in 128kB chunks, and then 
jumps backwards one chunk at a time.  Rather than just reading 
backwards 8kB at a time.


Cheers,

Jeff

Legal disclaimers on emails to this group

[Craig James]

(I've changed the original subject, "autovacuum locking question", of the
sender's email so as not to hijack that thread.)

On Thu, Dec 5, 2019 at 2:26 PM Mike Schanne  wrote:

> Hi,
>
> I am investigating a performance problem...
> ... This email is non-binding, is subject to contract, and neither Kulicke
> and Soffa Industries, Inc. nor its subsidiaries (each and collectively
> “K&S”) shall have any obligation to you to consummate the transactions
> herein or to enter into any agreement, other than in accordance with the
> terms and conditions of a definitive agreement if and when negotiated,
> finalized and executed between the parties. This email and all its contents
> are protected by International and United States copyright laws. Any
> reproduction or use of all or any part of this email without the express
> written consent of K&S is prohibited.
>

Sorry to be off topic, but this bugs me. Language is important. This isn't
directed at you specifically, but I see these disclaimers all the time. How
can you post to a public newsgroup that automatically reproduces your email
to thousands of subscribers, and additionally publishes it on
publicly accessible archives, in direct conflict with your company's policy
appended to your email? And why on Earth do your company's lawyers think
this sort of disclaimer is helpful and even legally useful? Not to mention,
do they realize it's vaguely offensive to every customer and colleague who
receives it?

Craig

Re: Legal disclaimers on emails to this group

[Tom Lane]

Craig James  writes:
> Sorry to be off topic, but this bugs me. Language is important. This isn't
> directed at you specifically, but I see these disclaimers all the time. How
> can you post to a public newsgroup that automatically reproduces your email
> to thousands of subscribers, and additionally publishes it on
> publicly accessible archives, in direct conflict with your company's policy
> appended to your email? And why on Earth do your company's lawyers think
> this sort of disclaimer is helpful and even legally useful? Not to mention,
> do they realize it's vaguely offensive to every customer and colleague who
> receives it?

Yeah, it's annoying, and the idea that such an addendum is legally
enforceable is just laughable (bearing in mind that IANAL --- but
without a pre-existing contract, it's laughable).  But the folks
actually emailing to our lists are generally peons with no say over
corporate policies, so there's not much they can do about it.  Might
as well chill out, or just ignore any mail with a disclaimer you
find particularly offensive.

regards, tom lane

Disclaimer: if you believe that email disclaimers have any legal
force whatsoever, you are required to immediately send me $1M USD.

threads (Re: Legal disclaimers on emails to this group)

[Justin Pryzby]

On Fri, Dec 06, 2019 at 10:42:19AM -0800, Craig James wrote:
> (I've changed the original subject, "autovacuum locking question", of the
> sender's email so as not to hijack that thread.)

Note that threads are defined by these headers, not by "Subject".

References: <0871fcf35ceb4caa8a2204ca9c38e...@useprdex1.corp.kns.com>   

   
In-Reply-To: <0871fcf35ceb4caa8a2204ca9c38e...@useprdex1.corp.kns.com>  

   

https://www.postgresql.org/message-id/CAFwQ8rcEExxB8ZuE_CYj6u6FZbRZjyWn%2BPo31hrfLAw1uBnKMg%40mail.gmail.com

Justin
(now hijacking your thread)

Re: Legal disclaimers on emails to this group

[Tim Cross]

Craig James  writes:

> (I've changed the original subject, "autovacuum locking question", of the
> sender's email so as not to hijack that thread.)
>
> On Thu, Dec 5, 2019 at 2:26 PM Mike Schanne  wrote:
>
>> Hi,
>>
>> I am investigating a performance problem...
>> ... This email is non-binding, is subject to contract, and neither Kulicke
>> and Soffa Industries, Inc. nor its subsidiaries (each and collectively
>> “K&S”) shall have any obligation to you to consummate the transactions
>> herein or to enter into any agreement, other than in accordance with the
>> terms and conditions of a definitive agreement if and when negotiated,
>> finalized and executed between the parties. This email and all its contents
>> are protected by International and United States copyright laws. Any
>> reproduction or use of all or any part of this email without the express
>> written consent of K&S is prohibited.
>>
>
> Sorry to be off topic, but this bugs me. Language is important. This isn't
> directed at you specifically, but I see these disclaimers all the time. How
> can you post to a public newsgroup that automatically reproduces your email
> to thousands of subscribers, and additionally publishes it on
> publicly accessible archives, in direct conflict with your company's policy
> appended to your email? And why on Earth do your company's lawyers think
> this sort of disclaimer is helpful and even legally useful? Not to mention,
> do they realize it's vaguely offensive to every customer and colleague who
> receives it?
>
> Craig

Oh how I hear you!

This is what I was using as my email signature (but not for groups). I
feel for the OP who probably has little choice (other than work for a
different employer, which is a very valid choice given the 'organisational
culture' exhibited by policies requiring such nonsense)

Notice to all senders:

If you send me a message, on receipt of that message I consider that message to
be my property and I will copy, share and deceminate as I see fit. I will
provide attribution when appropriate and I willl endeavour to comply with all
reasonable requests. However, I reject all threats or implied threats of legal
action arising from an error or mistake on your part. It is your responsibility
to manage your communications appropriately, not mine.

-- 
Tim Cross

Re: unexpected result for wastedbytes query after vacuum full

[Justin Pryzby]

On Fri, Dec 06, 2019 at 05:18:20PM +, Mike Schanne wrote:
> Hi all,
> 
> This question is somewhat related to my previous question:
> https://www.postgresql.org/message-id/0871fcf35ceb4caa8a2204ca9c38e330%40USEPRDEX1.corp.kns.com
> 
> I was attempting to measure the benefit of doing a VACUUM FULL on my 
> database.  I was using the query found here:
> https://wiki.postgresql.org/wiki/Show_database_bloat
> 
> However, I got an unexpected result in that the "wastedbytes" value actually 
> increased for some tables after doing the vacuum.

> I was wondering if the fact that we use a json column could be interfering 
> with the wastedbytes calculation.  Can anyone explain how wastedbytes could 
> increase from a vacuum?

Is it due to dropped columns, like Tom explained here ?
https://www.postgresql.org/message-id/18375.1520723971%40sss.pgh.pa.us

Re: autovacuum locking question

[Jeff Janes]

On Fri, Dec 6, 2019 at 12:50 PM MichaelDBA  wrote:

> And Just to reiterate my own understanding of this...
>
> autovacuum priority is less than a user-initiated request, so issuing a
> manual vacuum (user-initiated request) will not result in being cancelled.
>

Somethings happen in some situations and not in others.  I don't know that
it is useful to categorize them into a monotonic priority scale.

Autovacs "to prevent wraparound" don't get cancelled the way ordinary
autovacs do, but they still use autovac IO throttling settings, not the
unthrottled (by default settings) manual vacuum settings, which can be a
major problem sometimes.

Note that no kind of vacuum should normally get cancelled using the
signalling mechanism during truncation phase, that seems to be due to some
rather extreme situation with IO congestion.

Cheers,

Jeff

Re: How to run in parallel in Postgres

[Ondrej Ivanič]

Hi Lars,

I have two suggestions:

- `xargs` almost always present and it can run in parallel (-P) but script
needs to be changed:
for((i=1;i<=28;i++)); do echo "SELECT
find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',${I},28);";
done | xargs -n1 -P 10 psql ...

- `UNION ALL` might trigger parallel execution (you need to mess with the
cost of the function and perhaps other settings):
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',
4258,'test_data.overlap_gap_input_t1_res',1,28) UNION ALL
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',
4258,'test_data.overlap_gap_input_t1_res',2,28)
...

Cheers,

On Thu, 5 Dec 2019 at 23:11, Lars Aksel Opsahl  wrote:

> Hi
>
> I 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.
>
>
> Thanks .
>
>
> Lars
>
>
>
>
>
>


-- 
Ondrej

Re: How to run in parallel in Postgres

[Justin Pryzby]

On Thu, Dec 05, 2019 at 12:10:42PM +, 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