Обсуждение: 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