How do I set the isolation level for SELECT statement?

Question

Using PostgreSQL 9.5 I would like to make sure that when I run a SELECT statement I get consistent results, i.e. if the table is modified while the SELECT is running, I want to get the state that was valid when the SELECT started.

Basically, it's like: Take a snapshot of a table, and don't allow updates to the table while the snapshot is being taken, and then deliver the snapshot, while updates are allowed again.

If I understood isolation levels correctly, REPEATABLE READ is what I'm looking for, isn't it?

Now my question is: How can I run a SELECT with REPEATABLE READ isolation level? Or am I missing something, and my approach is wrong? How would I solve this?

Answer 1

You don't need to do anything to get statement level consistency

A query always sees a consistent state of the database regardless of the isolation level you use.

Quote from the manual:

This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.

See the chapter about Multi Version Concurrency Control in the manual

Answer 2

Read committed (especially section 13.2.1) is the default read level in PostgreSQL.

This read level will give you a snapshot of what has been committed before your transaction starts. It will allow other transactions to read and write to your table, you just won't be able to see any writes made after the start of your transaction.

Does this only apply to SELECTs run in the context of a transaction, or also to SELECTs that are run alone, without an explicit transaction around them (i.e., just a single SELECT statement, e.g. manually entered to the REPL)?

Again from the documentation:

PostgreSQL actually treats every SQL statement as being executed within transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

So, individual SELECTs (one after the other) may see different contexts. There will be a difference if another user has modified your tables in the meantime.

However (and this should clarify) in a query with subselects - the individual subqueries will be consistent i.e. see the same context - in the same statement! Within the same transaction, you will not see changes made by other transactions but you will see changes made by previous changes within a single transaction of your own.

If you do not want this, then repeatable read or serializable may be a better option.