Trying to the behavior of a parallel query with with a change in the transaction isolation mode

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Trying to the behavior of a parallel query with with a change in the transaction isolation mode

Luis Carril

Hi all,

  

     I am trying to verify if during a transaction a parallel plan ca be generated but later only a sequential one is executed.

     The documentation says the following (in https://www.postgresql.org/docs/9.6/static/when-can-parallel-query-be-used.html):


---
Even when parallel query plan is generated for a particular query, there are several circumstances under which it will be impossible to execute that plan in parallel at execution time. If this occurs, the leader will execute the portion of the plan below the Gather node entirely by itself, almost as if the Gather node were not present. This will happen if any of the following conditions are met:
     [...]

The transaction isolation level is serializable. This situation does not normally arise, because parallel query plans are not generated when the transaction isolation level is serializable. However, it can happen if the transaction isolation level is changed to serializable after the plan is generated and before it is executed.

---


     I have tried the following:


BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY;
EXPLAIN (COSTS OFF) SELECT avg(a) FROM parallel_big;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
EXPLAIN ANALYZE SELECT avg(a) FROM parallel_big;
COMMIT;

     But complains that after the first SELECT (even if it is in an EXPLAIN) the isolation level cannot be changed, so the transaction is aborted  and the SELECT is never executed (even sequentially).


     Is there any way to test the possible behavior described in the documentation?


Thanks you very much

Luis M Carril

   

Reply | Threaded
Open this post in threaded view
|

Re: Trying to the behavior of a parallel query with with a change in the transaction isolation mode

Álvaro Herrera
Luis Carril wrote:

> The transaction isolation level is serializable. This situation does not normally arise, because parallel query plans are not generated when the transaction isolation level is serializable. However, it can happen if the transaction isolation level is changed to serializable after the plan is generated and before it is executed.

> BEGIN;
> SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY;
> EXPLAIN (COSTS OFF) SELECT avg(a) FROM parallel_big;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
> EXPLAIN ANALYZE SELECT avg(a) FROM parallel_big;
> COMMIT;
>
>
>      But complains that after the first SELECT (even if it is in an EXPLAIN) the isolation level cannot be changed, so the transaction is aborted  and the SELECT is never executed (even sequentially).
>
>
>      Is there any way to test the possible behavior described in the documentation?

I think you would do a PREPARE in a regular transaction, then open a
transaction changing the isolation level to serializable and try the
EXPLAIN EXECUTE there.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Trying to the behavior of a parallel query with with a change in the transaction isolation mode

Luis Carril

Thanks for the answer it worked, the third analyze in the sequence below show multiple workers planned and none launched.


PREPARE st AS SELECT avg(a) FROM parallel_big;
EXPLAIN ANALYZE EXECUTE st;

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY;
EXPLAIN ANALYZE EXECUTE st;
COMMIT;

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
EXPLAIN ANALYZE EXECUTE st;
COMMIT;
DEALLOCATE st;

Cheers,
Luis M



From: Alvaro Herrera <[hidden email]>
Sent: Monday, February 12, 2018 4:19:52 PM
To: Luis Carril
Cc: [hidden email]
Subject: Re: Trying to the behavior of a parallel query with with a change in the transaction isolation mode
 
Luis Carril wrote:

> The transaction isolation level is serializable. This situation does not normally arise, because parallel query plans are not generated when the transaction isolation level is serializable. However, it can happen if the transaction isolation level is changed to serializable after the plan is generated and before it is executed.

> BEGIN;
> SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY;
> EXPLAIN (COSTS OFF) SELECT avg(a) FROM parallel_big;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
> EXPLAIN ANALYZE SELECT avg(a) FROM parallel_big;
> COMMIT;
>
>
>      But complains that after the first SELECT (even if it is in an EXPLAIN) the isolation level cannot be changed, so the transaction is aborted  and the SELECT is never executed (even sequentially).
>
>
>      Is there any way to test the possible behavior described in the documentation?

I think you would do a PREPARE in a regular transaction, then open a
transaction changing the isolation level to serializable and try the
EXPLAIN EXECUTE there.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Previous Thread Next Thread