"CREATE EXTENTION IF NOT EXISTS" doesn't really check if extention does not exist

Question

I want to load cube extention, but it might be already loaded. So I do

CREATE EXTENSION IF NOT EXISTS cube;

Docs say that when IF NOT EXISTS is specified, it shouldn't throw an error. But it does:

ff_postgres_1  | 2020-07-26 18:15:46.233 UTC [33] ERROR:  duplicate key value violates unique constraint "pg_extension_name_index"
ff_postgres_1  | 2020-07-26 18:15:46.233 UTC [33] DETAIL:  Key (extname)=(cube) already exists.
ff_postgres_1  | 2020-07-26 18:15:46.233 UTC [33] STATEMENT:  
ff_postgres_1  |                    CREATE EXTENSION IF NOT EXISTS cube;

What am I doing wrong here? I'm running PostgreSQL 12.3

Answer 1

You can get this with races. If two sessions are trying to create the extension at the same time, neither IF NOT EXISTS can see the other one yet, so both get past that step. The first one to get its row inserted into pg_extension wins, and the other blocks. And then once the first session commits, the second unblocks to fail with this message.