isn't "insert into where not exists" atomic?

Hello,

I just received an error message:

PGError: ERROR: duplicate key value violates unique constraint "chu_user_id_chat_room_id"
DETAIL: Key (user_id, chat_room_id)=(8, 2) already exists.
CONTEXT: SQL statement "insert into chat_room_users (user_id, chat_room_id, active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists (select 1 from chat_room_users where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id))"
PL/pgSQL function "trf_chat_room_users_insert" line 3 at SQL statement
: INSERT INTO "chat_room_users" ("user_id", "chat_room_id", "active_at") VALUES (8, 2, NULL)

The important line is:
insert into chat_room_users (user_id, chat_room_id, active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists (select 1 from chat_room_users where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id))

I always thought this is atomic and can not fail. Was I wrong?

If it isn't then I have to rewrite my triggers. Do I have to use "lock
table" instead of the above to avoid errors in parallel inserts?

The trigger looks like:

create or replace function trf_chat_room_users_insert() returns trigger
as $$
begin
if NEW.active_at is null then
insert into chat_room_users (user_id, chat_room_id,
active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists
(select 1 from chat_room_users where user_id = NEW.user_id and
chat_room_id = NEW.chat_room_id));
if not found then
update chat_room_users set active_at = now()
where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id;
end if;
return null;
end if;
return NEW;
end;
$$ language plpgsql;

And it meant to be "insert or update".

Mage