https://supabase.com/ logo
Join DiscordCommunities
Powered by
Does anyone have good suggestions for debugging a ...
# sql
d
Does anyone have good suggestions for debugging a plpgsql trigger? I'm not sure what's wrong with mine and I can't read the output if the trigger just fails
c
You can try and log the error, like this
Copy code
create or replace function link_cosmos_userid() 
returns trigger 
language plpgsql 

as $$
begin
  update auth.users
  set raw_user_meta_data = jsonb_set(raw_user_meta_data, '{cosmos_userid}', get_cosmos_userid(new.email), true)
  where users.id = new.id
exception when others then
  raise warning 'Issue with trigger! Sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
end;
$$;
Then search for the log in the dashboard (Settings -> Logs -> Database)
if you still don't see the log, it's possible that the trigger is running with a role that does NOT have access to auth.users
in that case you can try to define the trigger as SECURITY DEFINER (i.e. the trigger will be run with the role that defined the trigger, NOT the role which triggered the action)
t
I’m on mobile and can’t verify. Though is your issue that a trigger always supposed to 
return new
d
I'll have a look to see if this works, thanks for the advice guys πŸ™‚
Just for reference if others run into this problem; both the 
SECURITY DEFINER
and 
return new
were likely culprits, but there's also something going on with my 
jsonb_set()
call. I also needed to use 
jsonb_insert()
instead
if I update some other varchar field it works fine
oh well, i can figure it out now πŸ™‚
So yeah, my only problem now is the call to 
get_cosmos_userid(new.email)
Copy code
sql
create or replace function link_cosmos_userid() 
returns trigger 
language plpgsql 
security definer
as $$
begin
    update auth.users
  set raw_user_meta_data = jsonb_insert(raw_user_meta_data, '{cosmos_userid}', get_cosmos_userid(new.email), true)
  where users.id = new.id;
  return new;
end;
$$;
this works just fine if I replace that function call with some hardcoded value
but what's strange is that that function works perfectly fine in isolation
c
what type does get_cosmos_userId return
d
yeah that was one of the problems, it was returning 
text
but it needed to return 
jsonb
now i can execute it just fine as sql, but it's still not working in the trigger for some reason
c
maybe the logs show the error - what do you see there?
d
so this works:
Copy code
sql
  UPDATE auth.users
  SET raw_user_meta_data = jsonb_insert(raw_user_meta_data, '{cosmos_userid}',  public.get_cosmos_userid('MY_EMAIL_ADDRESS_HERE'), true)
  WHERE users.email = 'MY_EMAIL_ADDRESS_HERE';
but this does not:
Copy code
sql
create or replace function public.link_cosmos_userid() 
returns trigger 
language plpgsql 
security definer
as $$
begin
  update auth.users
  set raw_user_meta_data = jsonb_insert(raw_user_meta_data, '{cosmos_userid}',  public.get_cosmos_userid(new.email), true)
  where users.id = new.id;
  return new;
end;
$$;
The log goes something like this:
Copy code
eexecute 3: with recursive pks_fks as ( -- pk + fk referencing col select conrelid as resorigtbl, unnest(conkey) as resorigcol from pg_constraint where contype IN ('p', 'f') union -- fk referenced col select confrelid, unnest(confkey) from pg_constraint where contype='f' ), views as ( select c.oid as view_id, n.nspname as view_schema, c.relname as view_name, r.ev_action as view_definition from pg_class c join pg_namespace n on n.oid = c.relnamespace join pg_rewrite r on r.ev_class = c.oid where c.relkind in ('v', 'm') and n.nspname = ANY($1 || $2) ), transform_json as ( select view_id, view_schema, view_name, -- the following formatting is without indentation on purpose -- to allow simple diffs, with less whitespace noise replace( replace( replace( replace( replace( replace( replace( replace( regexp_replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( view_definition::text, -- This conversion to json is heavily optimized for performance. -- The general idea is to use as few regexp_replace() calls as possible. -- Simple replace() is a lot faster, so we jump through some hoops -- to be able to use regexp_replace() only once. -- This has been tested against a huge schema with 250+ different views. -- The unit tests do NOT reflect all possible inputs. Be careful when changing this!
which doesn't make a lot of sense, but I get it consistently whenever my trigger is fired
c
no idea what this is
btw, I am not sure it's a very good idea to define stored procedures as security definer in the public schema
in practice this means that anyone can call them and they will be executed with the role that defined them - a bit of a tangent
d
oof, thanks for noticing that
not getting any additional logging when attempting to raise a warning unfortunately. oh well, off to some more debugging I guess πŸ™‚ thanks for all the help so far guys, really appreciate it
PreviousNext
Powered by