Re: How to run in parallel in Postgres
Lars Aksel Opsahl <[email protected]> Fri, 6 Dec 2019 08:39:55 +0000
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <HE1P189MB02665BBF238411890B93F7439D5F0@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM> |
--_000_HE1P189MB02665BBF238411890B93F7439D5F0HE1P189MB0266EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >From: Laurenz Albe <[email protected]> >Sent: Thursday, December 5, 2019 5:42 PM >To: Lars Aksel Opsahl <[email protected]>; [email protected]= gresql.org <[email protected]> >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 para= llel. >> >> 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 inst= alled. >> >> Since Postgres now supports parallel I was wondering if it's easy to tri= gger parallel dynamically created SQL calls. >> >> If you look at https://github.com/larsop/find-overlap-and-gap/blob/maste= r/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 external= ly install programs ? >> >> When this 28 sql calls are done, the find_overlap_gap_make_run_cmd may c= ontinue to the next step of work. So the function that triggers parallel ca= lls 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 lik= e 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 an= d 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 t= o 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 --_000_HE1P189MB02665BBF238411890B93F7439D5F0HE1P189MB0266EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <html> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-= 1"> <style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo= ttom:0;} </style> </head> <body dir=3D"ltr"> <div id=3D"appendonsend" style=3D"font-family: Calibri, Arial, Helvetica, s= ans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> </div> <div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12p= t; color:rgb(0,0,0)"> </div> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>From: = <span style=3D"text-decoration: underline"> Laurenz</span> <span style=3D"text-decoration: underline">Albe</span> <l= aurenz.albe<span style=3D"text-decoration: underline">@cybertec.at</span>&g= t;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>Sent: = Thursday, December 5, 2019 5:42 PM</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>To: <s= pan style=3D"text-decoration: underline"> Lars</span> <span style=3D"text-decoration: underline">Aksel</span> <span s= tyle=3D"text-decoration: underline"> Opsahl</span> <Lars.Opsahl<span style=3D"text-decoration: underline">@ni= bio.no</span>>; <span style=3D"text-decoration: underline">pgsql</span>-performance<span st= yle=3D"text-decoration: underline">@lists.postgresql.org</span> <pgsql-p= erformance<span style=3D"text-decoration: underline">@lists.postgresql.org<= /span>></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>Subjec= t: Re: How to run in parallel in <span style=3D"text-decoration: underline">Postgres</span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>On <sp= an style=3D"text-decoration: underline"> Thu</span>, 2019-12-05 at 12:10 +0000, <span style=3D"text-decoration: = underline">Lars</span> <span style=3D"text-decoration: underline">Aksel</span> <span style=3D"text= -decoration: underline"> Opsahl</span> wrote:</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> h= ave a function that prepares data, so the big job can be run it in parallel= .<span> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>><s= pan> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> T= oday I have solved this by using "<span style=3D"text-decoration: unde= rline">Gnu</span> parallel" like this.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> <= span style=3D"text-decoration: underline"> psql</span> <span style=3D"text-decoration: underline">testdb</span> -c&quo= t;\! <span style=3D"text-decoration: underline"> psql</span> -t -q -o /<span style=3D"text-decoration: underline">tmp</span>= /run_cmd.sql <span style=3D"text-decoration: underline">testdb</span> -c\"SELECT fi= nd_overlap_gap_make_run_cmd('sl_lop.overlap_gap_input_t1','<span style=3D"t= ext-decoration: underline">geom</span>',4258,'sl_lop.overlap_gap_input_t1_r= es',50);\"; parallel -j 4<span> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> <= span style=3D"text-decoration: underline"> psql</span> <span style=3D"text-decoration: underline">testdb</span> -c :::= : /<span style=3D"text-decoration: underline">tmp</span>/run_cmd.sql" = 2>> /<span style=3D"text-decoration: underline">tmp</span>/analyze.lo= g;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>><s= pan> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> T= he problem here is that I depend on external code which may not be installe= d.<span> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>><s= pan> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> S= ince <span style=3D"text-decoration: underline"> Postgres</span> now supports parallel I was wondering if it's easy to trigg= er parallel dynamically created SQL calls.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>><s= pan> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> I= f you look at https://github.com/larsop/find-overlap-and-gap/blob/master/sr= c/test/sql/regress/find_overlap_and_gap.sql<span> </span>you see that</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> f= ind_overlap_gap_make_run_cmd generates as set of 28 <span style=3D"text-decoration: underline">sql</span> calls.<span> </s= pan></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>><s= pan> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> S= o is it in a simple way possible to use <span style=3D"text-decoration: underline">Postgres</span> parallel functio= nality to call this 28 functions i parallel so I don't have dependent on ex= ternally install programs<span> </span>?</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>><s= pan> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> W= hen this 28 <span style=3D"text-decoration: underline"> sql</span> calls are done, the find_overlap_gap_make_run_cmd may continue t= o the next step of work. So the function that triggers parallel calls wait = for them complete and then may start on</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>> t= he next step of work.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>You ca= nnot run several queries in parallel in a PostgreSQL function.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>You ma= y want to have a look at PL/<span style=3D"text-decoration: underline">Prox= y</span> which might be used for things like that.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>Yours,= </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span = style=3D"text-decoration: underline">Laurenz</span> <span style=3D"text-decoration: underline">Albe</span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">>--<spa= n> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span = style=3D"text-decoration: underline">Cybertec</span> | https://www.cybertec= -postgresql.com</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height= : 14.0px"> <br> </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span styl= e=3D"text-decoration: underline">Hi</span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height= : 14.0px"> <br> </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Thanks, I = checked it out.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height= : 14.0px"> <br> </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">If I under= stand it correct I have to write the code using <span style=3D"text-decoration: underline">plproxy</span> syntax and this m= eans if <span style=3D"text-decoration: underline"> plproxy</span> is not installed the code will fail.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height= : 14.0px"> <br> </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">So the onl= y way now to use built in parallel functionality in <span style=3D"text-decoration: underline">Postgres</span> is to use C ?</p= > <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height= : 14.0px"> <br> </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">Do you bel= ieve it will possible in the future to run parallel calls from a PostgresSQ= L function (or is impossible/difficult because of design) ?</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; min-height= : 14.0px"> <br> </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span styl= e=3D"text-decoration: underline">Lars</span></p> <br> <div class=3D"BodyFragment"><font size=3D"2"><span style=3D"font-size:11pt"= > <div class=3D"PlainText"><br> </div> </span></font></div> </body> </html> --_000_HE1P189MB02665BBF238411890B93F7439D5F0HE1P189MB0266EURP_--