I have a table:
user_id | project_id | permission
-------------------------------------- -------------------------------------- ------------
5911e84b-ab6f-4a51-942e-dab979882725 | b4f6d926-ac69-461f-9fd7-1992a1b1c5bc | owner
7e3581a4-f542-4abc-bbda-36fb91ea4bff | eff09e2a-c54b-4081-bde5-68de5d32dd73 | owner
46f9f2e3-edd1-40df-aa52-4bdc354abd38 | 59df2db8-5067-4bc2-b268-3fb1308d9d41 | owner
9089038d-4b77-4774-a095-a621fb73059a | 4f26ace1-f072-42d0-bd0d-ffbae9103b3f | owner
5911e84b-ab6f-4a51-942e-dab979882725 | 59df2db8-5067-4bc2-b268-3fb1308d9d41 | rw
I have a trigger on update:
--------------------------------------------------------------------------------
-- trigger that consumes the queue once the user responds
\set obj_name 'sharing_queue_on_update_trigger'
create or replace function :obj_name()
returns trigger as $$
begin
if new.status = 'accepted' then
-- add to the user_permissions table
insert into core.user_permissions (project_id, user_id, permission)
values (new.project, new.grantee, new.permission);
end if;
-- remove from the queue
delete from core.sharing_queue
where core.sharing_queue.grantee = new.grantee
and core.sharing_queue.project = new.project;
return null;
end;
$$ language plpgsql;
create trigger "Create a user_permission entry when user accepts invitation"
after update on core.sharing_queue
for each row
when (new.status != 'awaiting')
execute procedure :obj_name();
When I run the following update:
update sharing_queue set status='accepted' where project = 'eff09e2a-c54b-4081-bde5-68de5d32dd73';
The record in the following queue is supposed to fodder a new record in the first table presented.
grantor | maybe_grantee_email | project | permission | creation_date | grantee | status
-------------------------------------- --------------------- -------------------------------------- ------------ --------------- -------------------------------------- ----------
7e3581a4-f542-4abc-bbda-36fb91ea4bff | [email protected] | eff09e2a-c54b-4081-bde5-68de5d32dd73 | rw | | 46f9f2e3-edd1-40df-aa52-4bdc354abd38 | awaiting
(1 row)
Specifically, the grantee with id ending in 38, with the project_id ending in 73 is supposed feed a new record in the first table.
However, I get the following duplicate index error:
ERROR: duplicate key value violates unique constraint "pk_project_permissions_id"
DETAIL: Key (user_id, project_id)=(46f9f2e3-edd1-40df-aa52-4bdc354abd38, eff09e2a-c54b-4081-bde5-68de5d32dd73) already exists.
CONTEXT: SQL statement "insert into core.user_permissions (project_id, user_id, permission)
values (new.project, new.grantee, new.permission)
returning new"
I don't see how I'm violating the index. There is no record with the user and project combination in the first table presented. Right?
I'm new to using triggers this much. I'm wondering if somehow I might be triggering a "double" entry that cancels the transaction.
Any pointers would be greatly appreciated.
Requested Addendum
Here is the schema for user_permissions
--------------------------------------------------------------------------------
--