PostgreSQL transaction level guarantees for foreign key consistency in read only transactions

Question

Application A (think exporter) needs to read all rows of all tables from a running PostgreSQL database. Meanwhile Application B (think web application) continues to do reads and writes.

Table child has an optional foreign key to parent.

I've had trouble with the following access pattern:

• A: BEGIN TRANSACTION
• A: SELECT * FROM parent
• B: BEGIN TRANSACTION
• B: INSERT INTO parent
• B: INSERT INTO child -- has foreign key to inserted parent
• B: COMMIT
• A: SELECT * FROM child -- I do not want to receive the inserted child here

Application A breaks because it reads a child for which it could not read the parent. Therefore I do not want that A reads the child row inserted by B.

As far as I understand REPEATABLE_READ does not give me any guarantees here, since I did not already read the child table in this transaction. As far as I understand this is not considered a phantom read either for the same reason.

• Does SERIALIZABLE guarantee that A does not read the new child row?
• Do I need to resort to application logic in A to discard child rows with invalid references to parent?

Answer 1

Start transaction A with

START TRANSACTION READ ONLY ISOLATION LEVEL REPEATABLE READ;

Then all statements in that transaction will see the same state (snapshot) of the database, no matter what was modified by concurrent transactions.

I added the READ ONLY only because you said that A was, it is not necessary for it to work.