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

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 :

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,
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$
LANGUAGE plpgsql VOLATILE;

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;