Обсуждение: ERROR: cannot start subtransactions during a parallel operation
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
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 .
How could I continue to use the default max_parallel_workers_gather (2).
Thank you
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 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
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
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
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.
Greetings,
Andres Freund
On Sun, Jul 1, 2018 at 5:02 AM, Mai Peng <maily.peng@webedia-group.com> 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
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
Hi all,
LANGUAGE plpgsql VOLATILE;
Sorry for the late reply.
I could handle the pb by transforming my function check ( with an exception ) by a simple sql immutable function.
Function before
| CREATE OR REPLACE FUNCTION test_id(ltree[]) |
| RETURNS boolean AS |
| $BODY$ |
| DECLARE |
| id public.ltree; |
| BEGIN |
| FOR id IN SELECT unnest($1) LOOP |
| PERFORMl id::id; |
| IF () THEN |
| RETURN FALSE; |
| END IF; |
| END LOOP; |
| RETURN TRUE; |
| EXCEPTION |
| WHEN OTHERS THEN RETURN FALSE; |
| END; |
| $BODY$ |
AFTER
| CREATE OR REPLACE FUNCTION test_Id(ltree[]) |
| RETURNS boolean AS |
| $BODY$ |
| SELECT NOT EXISTS ( |
| SELECT 1 |
| FROM unnest ($1) id |
| WHERE id<>id::id |
| ) |
| $BODY$ |
| LANGUAGE sql IMMUTABLE STRICT; |
Le 1 juil. 2018 à 11:02, Mai Peng <maily.peng@webedia-group.com> a écrit :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.
Greetings,
Andres Freund