Postgres race condition involving subselect and foreign key

Question

We have 2 tables defined as follows

CREATE TABLE foo (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE bar (
  foo_id BIGINT UNIQUE,
  foo_name TEXT NOT NULL UNIQUE REFERENCES foo (name)
);

I've noticed that when executing the following two queries concurrently

INSERT INTO foo (name) VALUES ('BAZ')

INSERT INTO bar (foo_name, foo_id) VALUES ('BAZ', (SELECT id FROM foo WHERE name = 'BAZ'))

it is possible under certain circumstances to end up inserting a row into bar where foo_id is NULL. The two queries are executed in different transactions, by two completely different processes.

How is this possible? I'd expect the second statement to either fail due to a foreign key violation (if the record in foo is not there), or succeed with a non-null value of foo_id (if it is).

What is causing this race condition? Is it due to the subselect, or is it due to the timing of when the foreign key constraint is checked?

We are using isolation level "read committed" and postgres version 10.3.

EDIT

I think the question was not particularly clear on what is confusing me. The question is about how and why 2 different states of the database were being observed during the execution of a single statement. The subselect is observing that the record in foo as being absent, whereas the fk check sees it as present. If it's just that there's no rule preventing this race condition, then this is an interesting question in itself - why would it not be possible to use transaction ids to ensure that the same state of the database is observed for both?

Answer 1

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.