Postgres race condition involving subselect and foreign key

The subselect in the INSERT INTO bar cannot see the new row concurrently inserted in foo because the latter is not committed yet.

But by the time that the query that checks the foreign key constraint is executed, the INSERT INTO foo has committed, so the foreign key constraint doesn't report an error.

A simple way to work around that is to use the REPEATABLE READ isolation level for the INSERT INT bar. Then the foreign key check uses the same snapshot as the INSERT, it won't see the newly committed row, and a constraint violation error will be thrown.