S
Supabase•2mo ago
LignaLog

Persistent 500 Error on Sign Up (Auth/OAuth) due to PostgREST Cache

Hello, I'm experiencing a frozen PostgREST schema cache issue in my project. Error Log: ERROR: relation "profiles" does not exist (SQLSTATE 42P01) Context: The public.profiles table and the associated handle_new_user trigger/function are correctly set up and exist. The issue affects both email and Google sign-ups. Troubleshooting: I have already attempted every known fix (recreating trigger/function, renaming table, toggling RLS) with no success. The API component seems to be stuck on this error for over 24 hours. Action needed: Could an infrastructure engineer please manually force a schema refresh/restart of the PostgREST service for my project "vuvuofacxxdtvqsowtso"? Thank you!
4 Replies
garyaustin
garyaustin•2mo ago
This is not likely PostgREST cache but you have a trigger function on auth.users that calls profiles table and not public.profiles table.
LignaLog
LignaLogOP•2mo ago
Hello, Following your analysis, I confirm that my setup already includes the proposed fix, yet the issue persists. 1. Trigger function is correct: The function handle_new_user already uses the explicit schema qualification: INSERT INTO public.profiles (...). 
    -- My code :
    CREATE OR REPLACE FUNCTION public.handle_new_user()
    RETURNS trigger
    LANGUAGE plpgsql
    SECURITY DEFINER
    AS $function$
    BEGIN
      INSERT INTO public.profiles (
        id,
        username,
        [...]
      )
      VALUES (
        NEW.id,
        COALESCE(NEW.raw_user_meta_data->>'username', NEW.email),
        [...]
      );
      RETURN NEW;
    END;
    $function$;
    
    -- My code :
    CREATE OR REPLACE FUNCTION public.handle_new_user()
    RETURNS trigger
    LANGUAGE plpgsql
    SECURITY DEFINER
    AS $function$
    BEGIN
      INSERT INTO public.profiles (
        id,
        username,
        [...]
      )
      VALUES (
        NEW.id,
        COALESCE(NEW.raw_user_meta_data->>'username', NEW.email),
        [...]
      );
      RETURN NEW;
    END;
    $function$;
2. Trigger definition is correct: The trigger is correctly set on auth.users. The Problem: Despite these verifications, the sign-up process still fails with the PostgREST error: ERROR: relation "profiles" does not exist (SQLSTATE 42P01). Action Taken: I have manually tried to force a schema reload by executing NOTIFY pgrst, 'reload schema'; multiple times, and the error remains. Given that the database side is correct, the problem is highly likely related to the PostgREST schema cache freezing for my project vuvuofacxxdtvqsowtso. Action Needed: Could an infrastructure engineer please perform a manual restart/refresh of the PostgREST service at the infrastructure level for my project? This appears to be the only remaining solution. Thank you for your continued help.
garyaustin
garyaustin•2mo ago
PostgREST is not involved in the auth call. It does not use PostgREST or the cache. Look in the auth and postgres logs to see if the error matches up with an auth call. If so then you have a another trigger function possibly? You can reset the postgREST cache yourself in the SQL editor with: 
NOTIFY pgrst, 'reload schema'
NOTIFY pgrst, 'reload schema'
No one here can see or touch your instance. But unless you are making your own call to the profiles table in your app code and getting this error postgREST is not involved. Can you also show your profiles table in the UI?
LignaLog
LignaLogOP•2mo ago
Hi @garyaustin , Thank you so much for your analysis and for stressing that the issue was not with PostgREST, but strictly on the PostgreSQL side. I examined the raw database logs as you suggested and found the exact root cause: It was a cascaded function in the trigger chain, specifically public.auto_increment_usage(), which contained an UPDATE profiles statement without the explicit schema qualification (public.). 
-- The faulty statement within auto_increment_usage():
UPDATE profiles 
SET usage_custom_lists = ... 
-- Had to be corrected to: UPDATE public.profiles
-- The faulty statement within auto_increment_usage():
UPDATE profiles 
SET usage_custom_lists = ... 
-- Had to be corrected to: UPDATE public.profiles
Fixing that single line immediately resolved the ERROR: relation "profiles" does not exist and the subsequent transaction error. Your diagnosis was spot-on! Thank you for redirecting our focus to the logs and the trigger execution context. Problem solved. Best regards,

Did you find this page helpful?