[SOLVED] How to enforce a phantom read in PostgreSQL?

How to enforce a phantom read in PostgreSQL?

I'm currently writing an article about different transaction isolation levels and want to show the dirty read / non-repeatable read / phantom read.

Dirty reads are impossible to show as PostgreSQL does not have READ_UNCOMMITTED, I do have an example for a non-repeatable read. However, I struggle with finding an example for a phantom read.

As an example, I create this:

CREATE TABLE balances (id varchar PRIMARY KEY, balance int);
INSERT INTO balances (id, balance) VALUES ('Alice', 40), ('Bob', 50);

Then I have two terminals (T1 and T2) with which I connect to the database to do this:

T1$ start transaction isolation level repeatable read;
T1$ SELECT * FROM balances WHERE balance > 10;
    id    | balance 
----------+---------
 Alice    |     40
 Bob      |     50

T2$ INSERT INTO balances (id, balance) VALUES ('Charlie', 60);
T1$ SELECT * FROM balances WHERE balance > 10;

    id    | balance 
----------+---------
 Alice    |     40
 Bob      |     50

Why does this not give a phantom read? I thought the last T1-Query should show Charlie, but it doesn't. I thought that would only be the case with the SERIALIZABLE transaction isolation level. Does REPEATABLE READ also prevent phantom reads in PostgreSQL?

Solution

In Postgres 12, the REPEATABLE_READ transaction isolation level prevents phantom reads (source).