1

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.

1

No, that's not possible.

A single query (and even a query with many sub-queries is considered a single query) sees a consistent state of the database as it was when that query started.

It's not even necessary to use explicit transaction control as you did.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.