parallel plan in insert query

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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 a parallel operation
2016-10-07 19:41:41 MSK [11404]: [78415-1] user=gis,db=gis,app=psql,client=[local] CONTEXT: SQL statement "SELECT proj4text FROM 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 into edges_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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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