Re: ERROR: cannot start subtransactions during a parallel operation

On 06/29/2018 07:22 PM, Andres Freund wrote:
> On 2018-06-29 16:50:47 +0200, Tomas Vondra wrote:
>> 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.
>
> Wouldn't the right fix be to adjust the parallel safety of the function?
>

Or that, yes. I initially understood that OP wants to keep running the
query as parallel, but no I see the goal was not having to tweak the GUC
just to fix the query. In that case yes, marking it as PARALLEL UNSAFE
should do the trick. My guess is that it's a PL/pgSQL function with an
EXCEPTION block, and there's no easy way to "fix" that.

regards

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