Обсуждение: parallel plan in insert query
Hello, everyone! I have a query with following plan: https://gist.github.com/gsmol/a0e752a3f003330a85e2bf243516a2d6 It fails with: ERROR: cannot start commands during a parallel operation I was under impression that for a write query no parallel plan will every be generated. Am I missed something? -- Grigory Smolkin Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
The query doesn't look like a write query from the sql or the plan you sent. My guess is that one of the PostGIS functions does an update or insert internally. It should probably be marked VOLATILE in that case which I'm not sure but would assume would disable parallel queries as well. But there are reasons why they might not want to do that too in which case this is the kind of risk that would come with.
It`s INSERT: 2016-10-07 19:41:41 MSK [11404]: [78416-1] user=gis,db=gis,app=psql,client=[local] STATEMENT: explain analyze insert into edges_snapped_speeds select gid, speed*3600, ts from (select * from traffic_snapped_tracks limit 2) a join lateral snaptopgr(geom) on true; It does qualify query as 'write query'? On 10/11/2016 02:41 PM, Greg Stark wrote: > The query doesn't look like a write query from the sql or the plan you > sent. My guess is that one of the PostGIS > functions does an update or insert internally. It should probably be > marked VOLATILE in that case which I'm not sure but would assume would > disable parallel queries as well. But there are reasons why they might > not want to do that too in which case this is the kind of risk that > would come with. > > -- Grigory Smolkin Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On Tue, Oct 11, 2016 at 5:18 PM, Grigory Smolkin <g.smolkin@postgrespro.ru> wrote: > It`s INSERT: > 2016-10-07 19:41:41 MSK [11404]: [78416-1] > user=gis,db=gis,app=psql,client=[local] STATEMENT: > explain analyze insert into edges_snapped_speeds select gid, speed*3600, ts > from (select * from traffic_snapped_tracks limit 2) a join lateral > snaptopgr(geom) on true; > > It does qualify query as 'write query'? > That's right, but parallelism can be used read part of query. For example, insert into t1 select * from parallel_exec(); Now if there is some statement in parallel_exec() function, that can use parallelism. Example function definition which can use parallelism: create or replace function parallel_exec() returns integer as $$ begin Perform * from t1 where c1 >= 10 and c1 < 11; return 1; end; $$ language plpgsql STABLE PARALLEL SAFE; -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Grigory Smolkin <g.smolkin@postgrespro.ru> writes: > It`s INSERT: > 2016-10-07 19:41:41 MSK [11404]: [78416-1] > user=gis,db=gis,app=psql,client=[local] STATEMENT: > explain analyze insert into edges_snapped_speeds select gid, speed*3600, > ts from (select * from traffic_snapped_tracks limit 2) a join lateral > snaptopgr(geom) on true; No, it isn't: 2016-10-07 19:41:41 MSK [11404]: [78414-1] user=gis,db=gis,app=psql,client=[local] ERROR: cannot start commands during aparallel operation 2016-10-07 19:41:41 MSK [11404]: [78415-1] user=gis,db=gis,app=psql,client=[local] CONTEXT: SQL statement "SELECT proj4textFROM spatial_ref_sys WHERE srid = 4326 LIMIT 1" 2016-10-07 19:41:41 MSK [11404]: [78416-1] user=gis,db=gis,app=psql,client=[local] STATEMENT: explain analyze insert intoedges_snapped_speeds select gid, speed*3600, ts from (select * from traffic_snapped_tracks limit 2) a join lateral snaptopgr(geom)on true; This is somewhere down inside a SELECT issued by a called function. Apparently you've got multiple levels of nested SQL operations there. The outer INSERT wouldn't get parallelized, but a query planned and executed inside a called function could be. I concur with Greg's conclusion that somewhere in the stack there's a function marked PARALLEL SAFE that shouldn't be marked that way. But we don't have nearly enough details to identify it. regards, tom lane
Thanks all of you for help! Your answers helped me to better understand a problem. -- Grigory Smolkin Postgres Professional: http://www.postgrespro.com The Russian Postgres Company