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!