Trigger and function not defined in right order using db diff

[rienheuver]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

When having a trigger on your database that calls a certain trigger and you run supabase db diff to generate a migration, it will first output the trigger and then the function. When running this code (migration), it will throw an error function (...) does not exist. The simple fix is to manually edit the output and put the trigger creation after the function creation.

To Reproduce

1. Run the following SQL:

CREATE OR REPLACE FUNCTION public.handle_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$BEGIN
INSERT INTO public.profiles (id, email) VALUES (new.id, new.email);
return new;
END$function$
;

CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION handle_new_user();

2. Run supabase db diff -f "add_automatic_profile_creation"
3. Run supabase db reset
4. See Error: ERROR: function handle_new_user() does not exist (SQLSTATE 42883)

Expected behavior

The migration should simply succeed

System information

• OS: Ubuntu 20.04
• Supabase CLI: 1.61.1
• Docker version 23.0.6, build ef23cbc

Additional context

I created the function and trigger through the studio and later manipulated them through DBeaver. Can't imagine this has any effect though

[canardos]

I can confirm this behavior, with 1.50.12 at least. It seems to be limited to triggers on tables in the auth schema.

My application has two triggers on auth.users (on on insert, one on update) and two triggers on tables in the public schema. The auth table triggers are outputted as the first two statements using supabase db diff (the functions they call are defined later), while the public table triggers are correctly outputted after their respective function are defined.

[CareTiger]

I had the same issue. I filed a ticket. this from the support engineering team - I have made a product feedback to improve the diff tool and will present this case to engineering. Thanks for reporting this. I hope this information helps!

[sweatybridge]

The way db diff works is by diffing schemas one by one in alphabetical order. I'm not sure if there's an easy way to fix this problem. Meanwhile you can work around it by specifying the order of schema explicitly. For eg.

supabase db diff -f public_changes -s public
supabase db diff -f auth_changes -s auth

If it helps, you can also combine them into a single migration afterwards by concatenating the generated migration files.

[muezz]

I faced this with the latest version of 1.112 when I ran supabase db pull to pull down all changes from the remote instance. I am not sure if it is okay to manually modify the migration file. What is the recommended way to get around this?

[mqp]

I also experienced this with db pull even though the functions in question were both in the public schema.