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;
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.