PostgreSQL函数多临时表异常：Supabase运行结果不符排查

问题描述 #

需求为统计存在于指定列表但不存在于另一组列表的联系人总数（计算两组联系人的集合差）。编写的多临时表版本函数在Supabase浏览器SQL编辑器中可正常执行，但保存到数据库后，通过Web应用.rpc调用始终返回0；测试发现通过文件系统加载（supabase start）函数时，创建2个及以上临时表就会返回空集，仅创建单个临时表的函数可正常运行。

多临时表版本函数（异常） #

CREATE or replace FUNCTION get_net_num_contacts(to_list_ids bigint[], not_to_list_ids bigint[])
  returns bigint
  AS $$
declare
  net_num_contacts bigint;
BEGIN
  CREATE TEMP TABLE IF NOT EXISTS t1 AS
    select distinct list_members.contact_id from list_members where list_members.list_id = ANY(to_list_ids);
  CREATE TEMP TABLE IF NOT EXISTS t2 AS
    select distinct list_members.contact_id from list_members where list_members.list_id = ANY(not_to_list_ids);
  CREATE TEMP TABLE IF NOT EXISTS t3 AS
    select t1.contact_id from t1 except select t2.contact_id from t2;
  SELECT count(*) from t3 into net_num_contacts;
  return net_num_contacts;
END;
$$ LANGUAGE plpgsql;

CTE替代版本（正常） #

CREATE or replace FUNCTION get_net_num_contacts(to_list_ids bigint[], not_to_list_ids bigint[])
  returns bigint
  AS $$
declare
  net_num_contacts bigint;
BEGIN
  WITH r0 as
  (
    WITH r1 AS
    (
      select distinct contact_id from list_members where list_id = ANY(to_list_ids)
    )
    select contact_id from r1 except
      select contact_id from list_members where list_id = ANY(not_to_list_ids)
  )
  select count(*) from r0 into net_num_contacts;
  return net_num_contacts;
END;
$$ LANGUAGE plpgsql;

原因分析 #

1. 临时表生命周期与Supabase连接池冲突
   PostgreSQL临时表是会话级的，会在当前数据库连接存续期间保留。Supabase使用连接池（如PGBouncer）复用数据库连接，当RPC调用复用已有连接时，之前调用创建的临时表会残留。此时CREATE TEMP TABLE IF NOT EXISTS会跳过表重建，直接使用残留的旧表（可能为空或存储旧参数的结果），导致最终计算返回0。
   而SQL编辑器使用独立会话，每次执行函数时临时表都是全新创建，因此结果正常。

2. 临时表与CTE的执行逻辑差异
   CTE是单次查询内的临时结果集，每次调用函数都会重新计算，不存在会话残留数据的问题；而临时表依赖会话存续状态，在连接复用场景下会出现数据不一致。

修复方案（针对临时表版本） #

如果要保留临时表实现，可通过两种方式避免残留问题：

• 每次函数执行前主动删除可能存在的临时表：

CREATE or replace FUNCTION get_net_num_contacts(to_list_ids bigint[], not_to_list_ids bigint[])
  returns bigint
  AS $$
declare
  net_num_contacts bigint;
BEGIN
  DROP TABLE IF EXISTS t1, t2, t3;
  CREATE TEMP TABLE t1 AS
    select distinct list_members.contact_id from list_members where list_members.list_id = ANY(to_list_ids);
  CREATE TEMP TABLE t2 AS
    select distinct list_members.contact_id from list_members where list_members.list_id = ANY(not_to_list_ids);
  CREATE TEMP TABLE t3 AS
    select t1.contact_id from t1 except select t2.contact_id from t2;
  SELECT count(*) from t3 into net_num_contacts;
  return net_num_contacts;
END;
$$ LANGUAGE plpgsql;

• 使用ON COMMIT DROP让临时表在事务结束后自动删除：

CREATE or replace FUNCTION get_net_num_contacts(to_list_ids bigint[], not_to_list_ids bigint[])
  returns bigint
  AS $$
declare
  net_num_contacts bigint;
BEGIN
  CREATE TEMP TABLE t1 ON COMMIT DROP AS
    select distinct list_members.contact_id from list_members where list_members.list_id = ANY(to_list_ids);
  CREATE TEMP TABLE t2 ON COMMIT DROP AS
    select distinct list_members.contact_id from list_members where list_members.list_id = ANY(not_to_list_ids);
  CREATE TEMP TABLE t3 ON COMMIT DROP AS
    select t1.contact_id from t1 except select t2.contact_id from t2;
  SELECT count(*) from t3 into net_num_contacts;
  return net_num_contacts;
END;
$$ LANGUAGE plpgsql;

总结 #

核心问题是Supabase的连接池复用机制导致会话级临时表残留，IF NOT EXISTS逻辑跳过了表的重新生成，进而返回错误结果。CTE版本因为是单次查询内的临时结果，无会话残留问题，因此可以稳定运行。

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