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

[Mage <mage@xxxxxxx>]

            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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general