I am querying the same table multiple times in a single query in a transaction with isolation level set to READ COMMITTED (which is the default in PostgreSQL). Examples are:
BEGIN;
WITH
first AS (SELECT * FROM table),
second AS (SELECT * FROM table)
SELECT ...;
END;
or
BEGIN;
SELECT * FROM table
UNION ALL
SELECT * FROM table;
END;
Is it possible that the first sub-SELECT
yields other results than the second sub-SELECT
? I understand that SELECT
queries might yield different results when executed separately (due to the characteristics of a READ COMMITTED transaction), but I am wondering what might happen if they are embedded in the same query.
I am using PostgreSQL 9.5.