Can changing a value simultaneously affect one choice in PostgreSQL 9.1?

Question

Consider the following query made in PostgreSQL 9.1 (or 9.2):

SELECT * FROM foo WHERE bar = true 

Suppose this is a rather long request (for example, per minute).

If at the beginning of the query there are 5 million records for which bar = true is executed, and during this query in another transaction there are rows added and deleted in the foo table, and for some existing row updates made in the bar field.

Will any of them affect the result of the above selected query?

I know about transaction isolation and visibility between individual statements in the same transaction, but what about one running statement?

Answer 1

Not.
Due to the MVCC model, only the tuples visible when the query is run will be used in a single SELECT . Details in the manual here :

Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, the SELECT query (without FOR UPDATE / SHARE) only sees data committed before the start of the query; he never sees either uncommitted data or changes made during the execution of queries in parallel transactions . Essentially, a SELECT query sees a database snapshot from the moment the query starts running. However, SELECT sees the effects of previous updates as part of its own transaction, although they are not yet complete. Also note that two consecutive SELECT statements can see different data, even if they are in the same transaction, if other transactions commit changes during the execution of the first SELECT.

Emphasis on mine.

Answer 2

The request will be consistent with reading data as of the beginning of the request. In Postgresql, the Multi-Version Concurrency Control (MVCC) documentation explains how this is done (there are several record versions in the table). Oracle uses a sequence change number (SCN) along with pre-images of the changed data. Here is an old document, Transaction Handling in Postgresql with the section "without overwriting storage management". But look at MVCC.

Or read the MVCC chapter in a Postgresql document