Does the data state stay persistent while executing a single query in Postgresql?

Question

Does the data state stay persistent while executing a single query in Postgresql? Example. I have a query:

Select * From t1; Select * From SomeSlowView; Select * From t1;

Does the first Select give always the same result as the third one? Imagine another query is running in parallel:

Update t1 Set x = x + 1;

or

Insert t1 ...;

Can I get different data whith the first Select and the third Select in the first query? If not, can transactions help?

Begin;
Select * From t1; Select * From SomeSlowView; Select * From t1;
Commit;

Do I need to exlpicitly lock t1?

Lock Table t1;
Select * From t1; Select * From SomeSlowView; Select * From t1;

Answer 1

The answer depends on the transaction isolation level you are using.

With the default READ COMMITTED isolation level, the first and the second query on t1 will have a different snapshot, so they can receive different results.

If you use the REPEATABLE READ isolation level (or a higher one), PostgreSQL will use the same snapshot for all statements in a transaction, so the both queries on t1 will see the same result.

If you need that kind of consistency, use REPEATABLE READ. Don't lock tables. Even if you used the appropriate lock level (LOCK t1 IN SHARE MODE) instead of ACCESS EXCLUSIVE as you do, that lock will be bad for concurrency, and, what is worse, it may well prevent autovacuum from cleaning up on t1, which can lead to serious problems.