Home > OS >  duplicate key value error that is difficult to understand
duplicate key value error that is difficult to understand

Time:04-15

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

--------------------------------------------------------------------------------
--            
  • Related