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?

If A runs in repeatable read, it will not see any new (or changed or deleted) rows that were inserted afterthe transaction started. — , Mar 25 '21 at 20:53

score 2 · Accepted Answer · answered Mar 26 '21 at 09:04

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.

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

1 Answers1