Supabase触发器函数客户端调用与直接操作表现异常原因排查

我有一个在h2h_pool_entry表的INSERT/DELETE操作后触发执行的触发器函数，在Supabase控制台直接增删行时表现符合预期，但通过Supabase JS客户端执行操作时，函数表现不一致，请问可能的原因是什么？

触发器函数与触发器代码 #

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();

Supabase JS客户端插入代码 #

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

可能的原因排查 #

1. 数据类型不匹配 #

JS客户端从formData.get()获取的参数默认是字符串类型，但数据库字段可能是其他类型：

• wager如果是数据库numeric类型，需要手动转为数字，否则插入的字符串会导致触发器计算逻辑出错；
• primaryOutcome如果是数据库布尔类型，formData.get()得到的"true"/"false"字符串需要转为实际布尔值，否则插入的数值不符合预期；
• poolId若为UUID类型，字符串格式不匹配也会导致后续关联查询异常。

2. 并发下的锁顺序错误 #

触发器中先读取表数据，再调用pg_advisory_xact_lock加锁，这会导致并发场景下：多个客户端请求同时读取旧数据，加锁后基于旧数据更新，最终结果不一致。而控制台操作是单步执行，无并发冲突，所以表现正常。正确顺序应该是先加锁再读取数据。

3. RLS（行级安全）权限限制 #

如果h2h_pool_entry或h2h_pool开启了RLS，JS客户端操作使用的是普通用户权限，而控制台用超级用户权限不受RLS限制。触发器执行时继承当前用户权限，如果普通用户没有读取相关表所有行的权限，触发器内的SELECT语句会返回不完整数据，导致计算结果错误。

4. 客户端插入操作未成功 #

JS客户端代码未捕获entryErr，可能插入操作本身因必填字段缺失、权限不足等原因失败，根本没触发触发器。而控制台操作时手动补全了所有字段，所以能正常触发。

内容的提问来源于stack exchange，提问作者Redgren Grumbholdt