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 */
    });
}

When records are created directly in the DB, the trigger function runs as expected, but via client, it just inserts and the function doesnt execute. — Redgren Grumbholdt, Jul 12 '23 at 14:15
Can you share the client side code you use to insert or delete the data? — dshukertjr, Jul 19 '23 at 06:09

score 0 · Answer 1 · answered Jul 06 '23 at 10:58

0

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;

answered Jul 06 '23 at 10:58

Matt

14,906
27
99
149

FOR SHARE is not allowed with aggregate functions – Redgren Grumbholdt Jul 10 '23 at 07:59

Supabase trigger function works differently via client than direct manipulation does

1 Answers1