Re: isn't "insert into where not exists" atomic? - Mailing list pgsql-general

On 02/03/2011 08:13 AM, Alban Hertroys wrote:
> 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
I think you missed the point that the insert contains a select which
sets found.

My trigger works fine and it was called thousands times. It just dropped
an exception two times.

The main question is that isn't "insert into ... select ... where not
exists" atomic?

Anyway, it you'd try it:

create table chat_room_users (
user_id int not null,
chat_room_id int not null,
active_at timestamp with time zone not null
);

create unique index chu_user_id_chat_room_id on chat_room_users
(user_id, chat_room_id);

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;

create trigger tr_chat_room_users_insert before insert on
chat_room_users for each row execute procedure trf_chat_room_users_insert();

insert into chat_room_users (user_id, chat_room_id) values (1, 1);
insert into chat_room_users (user_id, chat_room_id) values (2, 1);
insert into chat_room_users (user_id, chat_room_id) values (1, 1);
insert into chat_room_users (user_id, chat_room_id) values (2, 1);



         Mage