Re: ERROR: cannot start subtransactions during a parallel operation

On 06/29/2018 04:00 PM, Mai Peng wrote:
> Hello,
> 
> On a pG10.4 instance, my query ( a simple select from a view) throw this 
> error:
> ERROR:  cannot start subtransactions during a parallel operation
> CONTEXT:  PL/pgSQL function check_validity(ltree[]) line 4 during 
> statement block entry
> 

So, what does this check_validity function do? It's not part of 
PostgreSQL, and it apparently tries to do something that opens a 
subtransation. Like a SAVEPOINT for example ...

> But prefixing this query by "set max_parallel_workers_gather=0" make it 
> works.
> 
> When I take off the column that is checked by a function, no need to add 
> set max_parallel_workers_gather=0 .
> 

Well, that's not really surprising - if you disable parallelism the 
query is no longer subject to the restriction about subtransactions.

> How could I continue to use the default max_parallel_workers_gather (2).
> 

Modity the function not to start a subtransaction.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services