© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
SupabaseS
Supabase•7mo ago•
3 replies
Cyber Grandma

Infinite recursion on trigger, can't set session replication role

Hi, I'm making a leaderboard system. I have a player_stats table with the player's statistics and rank. I'm making a trigger that re-calculates the rank when a player's stats are updated.

I've tried to add a 'base case' to stop recursive trigger calls but it doesn't work. The AI tool now recommends me to do :
SET session_replication_role = 'replica'; -- Disables all triggers
But I'm getting :
{"code":"42501","details":null,"hint":null,"message":"permission denied to set parameter "session_replication_role""}

Here's my setup:

create or replace function public.update_player_rank_on_stat_change () RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER
set
  search_path to '' as $function$
BEGIN
    -- Check for recursion
    IF (NEW.average_cpm = old.average_cpm and NEW.average_accuracy = OLD.average_accuracy) THEN
        RETURN NULL;
    END IF;

    SET session_replication_role = 'replica';  -- Disables all triggers
    PERFORM public.calculate_player_ranks();
    SET session_replication_role = 'origin';   -- Re-enables triggers
    RETURN NULL;
END;
$function$;
create or replace function public.update_player_rank_on_stat_change () RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER
set
  search_path to '' as $function$
BEGIN
    -- Check for recursion
    IF (NEW.average_cpm = old.average_cpm and NEW.average_accuracy = OLD.average_accuracy) THEN
        RETURN NULL;
    END IF;

    SET session_replication_role = 'replica';  -- Disables all triggers
    PERFORM public.calculate_player_ranks();
    SET session_replication_role = 'origin';   -- Re-enables triggers
    RETURN NULL;
END;
$function$;


Which is hooked like so:

DROP TRIGGER IF EXISTS "trigger_update_player_ranks" ON "public"."player_stats";

CREATE TRIGGER "trigger_update_player_ranks"
AFTER UPDATE
ON "public"."player_stats"
FOR EACH STATEMENT
EXECUTE FUNCTION "public"."update_player_rank_on_stat_change"();
DROP TRIGGER IF EXISTS "trigger_update_player_ranks" ON "public"."player_stats";

CREATE TRIGGER "trigger_update_player_ranks"
AFTER UPDATE
ON "public"."player_stats"
FOR EACH STATEMENT
EXECUTE FUNCTION "public"."update_player_rank_on_stat_change"();


And the actual function that does the heavy lifting:
BEGIN
    WITH ranked_players AS (
        SELECT 
            player_id,
            DENSE_RANK() OVER (
                ORDER BY average_cpm DESC, average_accuracy DESC
            ) as new_rank
        FROM 
            public.player_stats
    )
    UPDATE public.player_stats ps
    SET rank = rp.new_rank
    FROM ranked_players rp
    WHERE ps.player_id = rp.player_id
    AND (ps.rank IS NULL OR ps.rank != rp.new_rank);
END;
BEGIN
    WITH ranked_players AS (
        SELECT 
            player_id,
            DENSE_RANK() OVER (
                ORDER BY average_cpm DESC, average_accuracy DESC
            ) as new_rank
        FROM 
            public.player_stats
    )
    UPDATE public.player_stats ps
    SET rank = rp.new_rank
    FROM ranked_players rp
    WHERE ps.player_id = rp.player_id
    AND (ps.rank IS NULL OR ps.rank != rp.new_rank);
END;

Everything is in Definer mode. So I don't know why I don't permissions here.
Supabase banner
SupabaseJoin
Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.
45,816Members
Resources
Was this page helpful?

Similar Threads

Recent Announcements

Similar Threads

Infinite Recursion on RLS Policity causes timeout
SupabaseSSupabase / help-and-questions
4w ago
Infinite Recursion RLS policy issues
SupabaseSSupabase / help-and-questions
8mo ago
PostgREST Infinite Recursion on Constraint after removing constraint
SupabaseSSupabase / help-and-questions
4mo ago
infinite recursion for table company_members
SupabaseSSupabase / help-and-questions
6mo ago