How can I do atomic selection using multiple queries in psql?

Question

Say I have postgresql db with 2 tables: User and Submission, and I'm doing:

SELECT * FROM User;
SELECT * FROM Submission;

As you see it's two queries so some changes may be applied to any table between the queries

So I have 2 questions:

1. How can I atomically fetch data from the different tables without any join or explicit lock? To be sure that no changes were apply on any table between the queries

2. Which queries guarantee atomic behavior? Is SELECT with INNER JOIN works atomically?

Answer 1

Run both statements in a single transaction which is set to repeatable read.

Quote from the manual

The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions.

---

And yes, a single statement is always atomic. It will never see changes to the underlying data while it is running even when those changes are committed while the statement is still running.