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?
Greetings,
Andres Freund
Hi,
On 2018-06-29 20:37:23 +0200, Tomas Vondra wrote:
> My guess is that it's a PL/pgSQL function with an EXCEPTION block, and
> there's no easy way to "fix" that.
Obviously not going to immediately help the OP, but I do think we should
be able to relax the subtransaction restriction around parallelism
without too much work. Can't allow xids to be assigned, but that's
probably ok for a lot of exception handling cases.
Greetings,
Andres Freund
Hi,
On 2018-07-01 11:02:24 +0200, Mai Peng wrote:
> Hello, how could I relax the subtransaction restriction, I used the
> Parallel Unsafe option, but still have the same issue.
> Rgds.
I'm unclear why you still get the error. Could you please give us the
query triggering the error, including an explain of it, and the involved
function definitions?
Greetings,
Andres Freund
Hello, how could I relax the subtransaction restriction, I used the Parallel Unsafe option, but still have the same issue.
Rgds.
Le ven. 29 juin 2018 20:47, Andres Freund <andres@anarazel.de> a écrit :
Hi,
On 2018-06-29 20:37:23 +0200, Tomas Vondra wrote: > My guess is that it's a PL/pgSQL function with an EXCEPTION block, and > there's no easy way to "fix" that.
Obviously not going to immediately help the OP, but I do think we should be able to relax the subtransaction restriction around parallelism without too much work. Can't allow xids to be assigned, but that's probably ok for a lot of exception handling cases.
Hello, how could I relax the subtransaction restriction, I used the Parallel Unsafe option, but still have the same issue.
Rgds.
Le ven. 29 juin 2018 20:47, Andres Freund <andres@anarazel.de> a écrit :
Hi,
On 2018-06-29 20:37:23 +0200, Tomas Vondra wrote: > My guess is that it's a PL/pgSQL function with an EXCEPTION block, and > there's no easy way to "fix" that.
Obviously not going to immediately help the OP, but I do think we should be able to relax the subtransaction restriction around parallelism without too much work. Can't allow xids to be assigned, but that's probably ok for a lot of exception handling cases.
On Sun, Jul 1, 2018 at 5:02 AM, Mai Peng wrote:
> Hello, how could I relax the subtransaction restriction, I used the Parallel
> Unsafe option, but still have the same issue.
There's no user option for that. Somebody would need to enhance
PostgreSQL by writing a patch.
I agree with Andres that the issue of XID-assignment needs to be
considered. There are probably separate guards against that problem,
though, so it may not be an issue. But somebody really ought to go
through xact.c and see if they can find any other problems - e.g.
whether
nParallelCurrentXids/ParallelCurrentXids will work properly, whether
any places check the current transaction for a parallel-in-progress
status rather than the top-transaction, whether aborting out of
multiple a subtransaction works directly from a parallel worker. As
the comment says:
/*
* Workers synchronize transaction state at the beginning of
each parallel
* operation, so we can't account for new subtransactions after that
* point. We might be able to make an exception for the type of
* subtransaction established by this function, which is
typically used in
* contexts where we're going to release or roll back the subtransaction
* before proceeding further, so that no enduring change to the
* transaction state occurs. For now, however, we prohibit
this case along
* with all the others.
*/
I think that at the time I wrote that comment (and I believe I was the
one who did write it) I mostly didn't have the time to do a careful
investigation for lurking problems, and it seemed better to be
conservative. But it's quite possible that there is nothing to do
other than remove that error check.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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
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
