https://supabase.com/ logo
Join Discord
Powered by
this simple trigger breaks my signup flow, when I ...
# help
m
this simple trigger breaks my signup flow, when I remove it everything works again
c
this simple trigger breaks my signup flow, when I remove it everything works again
Triggers need to return the row that should be inserted - if they don't return anything, the insertion is aborted
so you need RETURN NEW at the end
m
ah cool, I actually just tried that, but it still failed
c
also, you need to make sure that the role which invokes the trigger (if this is on user sign-up, it's probably the anon role) has permissions to insert into the userlists table
alternatively, you can mark the trigger as SECURITY DEFINER, in which case the trigger will run with the role which defined the trigger, NOT the role which invoked it
however, your trigger is on the public schema, so you need to be very careful about running it as Security definer
m
thank you so much, I'll try your suggestions now
it's still failing
how can I debug this function
if I delete this part of the trigger: " INSERT INTO public.userlists (user_id, name) VALUES(NEW.user_id, 'Favorites')...." everything works fine
c
does this insert statement work if you run it manually through the dashboard?
m
yes
if I just manually put in the ID of an existing user it works
the NEW.user_id should not be null because I trigger AFTER
c
in that case, look at the logs of the db
maybe there will be more info there about why this fails
the logs are in the dashboard -> Settings -> Database -> Logs
s
This is due to the language definer, change it from plpsql to just sql and remove the BEGIN and END and it should work
m
I am gonna try it thanks
btw I just removed the NEW.user_id with some test user id hard coded, just to see if it works and lo and behold it indeed works
c
Out of curiosity, why do you think this is the problem? I have similar triggers defined in plpgsql and they work fine
m
also it works if I hard code some uuid like "a486f520-4839-4586-bd23-88e762c89b71" instead of NEW.user_id
s
@User can you share your trigger code too, so we know when this trigger is called
Ah I see the issue, it should be 
NEW.id
m
I tried that many times, but let me try again
s
@User no you are inserting based on data from 
auth.users
not 
auth.identities
Just a hunch as I've had similar issue and it was based on this.
m
yea I tried both of them
s
Something else I'm also noticing here, you shouldn't add functions or tables to the 
auth
schema, this schema is not your own and could change if the Supabase team need to do a security update or schema change.
m
I had it in public, but was worried that it wasn't safe there
s
@User your statements look wrong, because the trigger is saying 
auth.create_common_lists()
but your actual trigger function is saying 
public.create_common_lists()
trigger functions are safe in the 
public
schema since they can only be called by a trigger
m
I tried both, also from the supabase ui directly selected
it's so damn odd, because it works when I hard code the UUID and remove NEW.id/NEW.user_id
c
what is the type of the user_id column in your userslist table?
m
also I read in the documentation the NEW is null if you have statement level execution instead of row execution, but I NOTICE logged it and the uuid is there
uuid foreign key from auth.users
c
did you see anything in the logs?
m
yes I logged RAISE EXCEPTION 'Values in NEW: %', NEW::text; and the uuid is there, if I remove NEW.id/NEW.user_id with an existing user like "a486f520-4839-4586-bd23-88e762c89b71" it works fine
( hard coded value just to debug )
c
I don't understand - what do the logs show if you do NOT hardcode?
m
nothing
it's full of random entries, I didn't find an error regarding this trigger
c
are you sure - sometimes you need to wait a bit and refresh to pull the latest relevant logs
my suggestion is to RAISE WARNING 'Starting trigger' at the beginning of the trigger so you have an anchor to find your way around the logs
m
I am going to try this RAISE WARNING 'Starting trigger'
omg I solved it
It wasn't working because I wanted the list column to be case insensitive and implemented it using a unique index, so it was failing because the insertion would violate the uniqueness constraint
thank you guys so much ❤️ learned a lot today 🙏
btw is this actually even good practice what I am doing or is it bad design? like I am inserting some common lists for each user so they don't have to do it themselves
c
I wouldn't say it's bad design, but it's a tradeoff - if you don't implement your own signUp method which inserts both inside the auth.users and the userslists table, there is really no other way other than using triggers. In an ideal world, I think it's a bit better to have your own signUp method which does the inserts, but that would require you to re-implement much of what is provided out of the box with supabase
so using a trigger is a decent plan B (I am also using something similar). The reason why I don't like triggers as much is because the logic is a bit scattered and it's NOT as obvious what would happen when you sign someone up (basically, you need to go inspect all triggers, you cannot just look at the implementation of a signUp function). Also, you lose the option to manually insert a user without adding the lists
PreviousNext
Powered by