Supabase trigger function works differently via client than direct manipulation does

Question

I have a trigger function that runs after insert/delete on a table, but seems it's only working as expected when i add rows directly into the DB in supabase console, but when I do inserts/deletions via the supabase js client, it behaves differently, what could be the reason?

Here is my function & trigger:

CREATE OR REPLACE FUNCTION h2h_run_pool_entry_matrix()
  RETURNS TRIGGER AS
$$
DECLARE

    v_total_pool_amount numeric;
    v_primary_entry_amount numeric;
    v_alt_entry_amount numeric;
    
    v_commission numeric;

    v_primary_multiplier numeric;
    v_alt_multiplier numeric;

    v_entrant_count int;
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- set counter
        SELECT COUNT(*)
        INTO v_entrant_count
        FROM (
            SELECT 1
            FROM public.h2h_pool_entry
            WHERE pool_id = NEW.pool_id
            FOR SHARE
        ) AS subquery;

        -- set commission
        SELECT commission
        INTO v_commission
        FROM public.h2h_pool
        WHERE id = NEW.pool_id;

        -- set primary entry amount 
        SELECT COALESCE(SUM(wager), 0)
        INTO v_primary_entry_amount
        FROM (
            SELECT wager
            FROM public.h2h_pool_entry
            WHERE pool_id = NEW.pool_id
            AND primary_outcome = TRUE
            FOR SHARE
        ) AS subquery;

        -- set alt entry count
        SELECT COALESCE(SUM(wager), 0)
        INTO v_alt_entry_amount
        FROM (
            SELECT wager
            FROM public.h2h_pool_entry
            WHERE pool_id = NEW.pool_id
            AND primary_outcome = FALSE
            FOR SHARE
        ) AS subquery;

        v_total_pool_amount := v_primary_entry_amount + v_alt_entry_amount;

        PERFORM pg_advisory_xact_lock(NEW.pool_id);

        v_primary_multiplier := (v_total_pool_amount - v_commission * v_total_pool_amount) / NULLIF(v_primary_entry_amount, 0);
        v_alt_multiplier := (v_total_pool_amount - v_commission * v_total_pool_amount) / NULLIF(v_alt_entry_amount, 0);

        UPDATE public.h2h_pool
        SET primary_entry_amount = v_primary_entry_amount, alt_entry_amount = v_alt_entry_amount, primary_multiplier = COALESCE(v_primary_multiplier, 0.00), alt_multiplier = COALESCE(v_alt_multiplier, 0.00), entrant_count = v_entrant_count
        WHERE id = NEW.pool_id;

        PERFORM pg_advisory_unlock(NEW.pool_id);

        RETURN NEW;
 
    ELSIF TG_OP = 'DELETE' THEN

        SELECT COUNT(*)
        INTO v_entrant_count
        FROM (
            SELECT 1
            FROM public.h2h_pool_entry
            WHERE pool_id = OLD.pool_id
            FOR SHARE
        ) AS subquery;

        -- set commission
        SELECT commission
        INTO v_commission
        FROM public.h2h_pool
        WHERE id = OLD.pool_id;

        -- set primary entry amount 
        SELECT COALESCE(SUM(wager), 0)
        INTO v_primary_entry_amount
        FROM (
            SELECT wager
            FROM public.h2h_pool_entry
            WHERE pool_id = OLD.pool_id
            AND primary_outcome = TRUE
            FOR SHARE
        ) AS subquery;

        -- set alt entry count
        SELECT COALESCE(SUM(wager), 0)
        INTO v_alt_entry_amount
        FROM (
            SELECT wager
            FROM public.h2h_pool_entry
            WHERE pool_id = OLD.pool_id
            AND primary_outcome = FALSE
            FOR SHARE
        ) AS subquery;

        v_total_pool_amount := v_primary_entry_amount + v_alt_entry_amount;
        
        PERFORM pg_advisory_xact_lock(OLD.pool_id);

        v_primary_multiplier := (v_total_pool_amount - v_commission * v_total_pool_amount) / NULLIF(v_primary_entry_amount, 0);
        v_alt_multiplier := (v_total_pool_amount - v_commission * v_total_pool_amount) / NULLIF(v_alt_entry_amount, 0);

        UPDATE public.h2h_pool
        SET primary_entry_amount = v_primary_entry_amount, alt_entry_amount = v_alt_entry_amount, primary_multiplier = COALESCE(v_primary_multiplier, 0.00), alt_multiplier = COALESCE(v_alt_multiplier, 0.00), entrant_count = v_entrant_count
        WHERE id = OLD.pool_id;

        PERFORM pg_advisory_unlock(OLD.pool_id);

        RETURN OLD;
    END IF;
END;
$$
LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS h2h_pool_entry_matrix ON public.h2h_pool_entry;
CREATE TRIGGER h2h_pool_entry_matrix
AFTER INSERT OR DELETE ON public.h2h_pool_entry
FOR EACH ROW
EXECUTE FUNCTION h2h_run_pool_entry_matrix();

Here is the action that does the insert:

async ({ request, locals: { supabase, getSession } }) => {
    const {
        user: { id: userId }
    } = await getSession();

    const formData = await request.formData();

    const poolId = formData.get('poolId');
    const wager = formData.get('wager');
    const primaryOutcome = formData.get('primaryOutcome');

    const { data: entryData, error: entryErr } = await supabase.from('h2h_pool_entry').insert({
        /* required fields */
    });
}

Answer 1

I ran into this problem and I noticed that I had RLS policies enabled on some of my tables that allowed admins to do some things that regular users couldn't do. When I updated rows in the DB console, I was acting as administrator, so I could get past the security. But when I updated a row from the client, as a regular non-admin user, I was stopped by the RLS policy and my changes wouldn't go through.

I changed my DB functions to SECURITY DEFINER instead of SECURITY INVOKER so that I could grant access with my security level instead of a regular user's privileges, and that gave users enough permissions. I don't know enough about RLS and security to know if that's the right thing to do yet... but it fixed my problem!

Answer 2

Try using aggregate functions in your queries instead of separate subqueries. This way, the queries will consider all the relevant rows within the transaction. e.g.

-- set primary entry amount 
SELECT COALESCE(SUM(wager) FILTER (WHERE primary_outcome = TRUE), 0)
INTO v_primary_entry_amount
FROM public.h2h_pool_entry
WHERE pool_id = NEW.pool_id
FOR SHARE;