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

On 3 Feb 2011, at 2:17, Mage wrote:

> 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;

Your trigger is the wrong way around. Insert doesn't set found, but update does.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4d4a559711736475013765!