S
Supabase•2w ago
fredguest

Unexpected error in database trigger

Following the basic example of creating a profile when a new user is created in the auth.users table, it works fine when simply inserting a new record into the public.profiles table as follows: 
begin
  insert into public.profiles (id, email, invite_id)
  values (new.id, new.email, (new.raw_user_meta_data ->> 'invite_id')::uuid);
  return new;
end;
begin
  insert into public.profiles (id, email, invite_id)
  values (new.id, new.email, (new.raw_user_meta_data ->> 'invite_id')::uuid);
  return new;
end;
However, if I use the same invite_id from raw_user_meta_data to update the public.invites table as follows, I get an error: 
begin
  update public.invites set receiver_id = new.id
  where id = (new.raw_user_meta_data ->> 'invite_id')::uuid;

  insert into public.profiles (id, email)
  values (new.id, new.email);
  return new;
end;
begin
  update public.invites set receiver_id = new.id
  where id = (new.raw_user_meta_data ->> 'invite_id')::uuid;

  insert into public.profiles (id, email)
  values (new.id, new.email);
  return new;
end;
The error has a code of "unexpected_failure" and a message of "Database error saving new user". The schema is correct and the RLS policy allows updates to the public.invites table, not sure what else it could be. Hard to debug these triggers.
2 Replies
garyaustin
garyaustin•2w ago
What is the Postgres log error? You can also use raise log 'new = %',new; and check the postgres log to see what is in new. There are many updates on auth.users and not all contain all the data of the row.
fredguest
fredguestOP•2w ago
Haha, I didn't even realize how much logging there is in the Supabase dashboard now. When I go to the Logs -> Postgres tab there didn't seem to be much, but in the Logs -> Auth tab, there is a much more descriptive error: 
ERROR: insert or update on table \"invites\" violates foreign key constraint \"invites_receiver_id_fkey\" (SQLSTATE 23503)
ERROR: insert or update on table \"invites\" violates foreign key constraint \"invites_receiver_id_fkey\" (SQLSTATE 23503)
Thanks for the tip about the logs! I should be able to sort it out now. Solved! I was referencing the id of the newly created profile record as a foreign key for the updated invite record, BEFORE I had actually inserted the new record in the profile table. I just needed to switch the order of operations, this works: 
begin
  insert into public.profiles (id, email)
  values (new.id, new.email);

  update public.invites set receiver_id = new.id
  where id = (new.raw_user_meta_data ->> 'invite_id')::uuid;

  return new;
end;
begin
  insert into public.profiles (id, email)
  values (new.id, new.email);

  update public.invites set receiver_id = new.id
  where id = (new.raw_user_meta_data ->> 'invite_id')::uuid;

  return new;
end;
Much easier to debug now that I know about the logs, thanks again!

Did you find this page helpful?