Does each WITH query count as a separate command in a read-committed transaction?-CodePudding

Suppose I have the following trivial code:

BEGIN;
WITH foo AS (SELECT * FROM t1),
     bar AS (SELECT * FROM t1)
SELECT * FROM foo, bar;
COMMIT;

The isolation level will be, by default, READ-COMMITTED. This of course means if you had two separate selects you could (in theory) have non-repeatable reads.

My question is: in the example code, do these two different selects count as a single read-committed command, or is each select on its own, making it possible for both to (in theory) see different rows?

CodePudding user response：

From here WITH:

The primary query and the WITH queries are all (notionally) executed at the same time. This implies that the effects of a data-modifying statement in WITH cannot be seen from other parts of the query, other than by reading its RETURNING output. If two such data-modifying statements attempt to modify the same row, the results are unspecified.

If you want more detailed information the see CTE. In this section it runs through a variety of scenerios.

CodePudding user response：

All three scans are performed with the snapshot of the query, so they are all guaranteed to see the same statenof the database.

CodePudding user response：

EXPLAIN ANALYSE applied to your example provides something like :

Seq Scan on t1 (cost=0.00..71.21 rows=621 width=233) (actual time=0.015..0.102 rows=621 loops=1)"
Planning Time: 0.078 ms
Execution Time: 0.132 ms

which confirms @Anton Grig comment.