PostgreSQL调用add_hub首次SELECT无返回结果问题排查

现有自定义函数add_hub，预期逻辑为：若hub表中不存在传入JSON参数对应的数据，则新增枢纽记录并返回其ID；若已存在对应数据，则直接返回匹配的枢纽ID。函数定义如下：

CREATE OR REPLACE FUNCTION public.add_hub(
    new_hub json,
    OUT hub_id bigint)
    RETURNS bigint
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
hub_name hub.name%TYPE := new_hub->>'name';
hub_city hub.city%TYPE := new_hub->>'city';
hub_province hub.province%TYPE := new_hub->>'province';
hub_region hub.region%TYPE := new_hub->>'region';
hub_address hub.address%TYPE := new_hub->>'address';

BEGIN
    SELECT hub.id FROM hub 
        WHERE name = hub_name 
            AND city = hub_city 
            AND province = hub_province
            AND address = hub_address 
            AND region = hub_region 
            INTO hub_id;

   IF NOT FOUND THEN
         INSERT INTO public.hub(name, city, province, address, region)
            VALUES (hub_name, hub_city, hub_province, hub_address, hub_region)
            RETURNING hub.id INTO hub_id;
   END IF;
   
END;
$BODY$;

ALTER FUNCTION public.add_hub(json)
    OWNER TO postgres;

执行如下查询时出现异常：

SELECT * FROM hub 
  WHERE hub.id = add_hub('
  {
   "name":"HUB TEST",
   "city":"Los Angeles",
   "province":"LA",
   "address":"Street 1",
   "region":"CA"
  }'
);

异常表现为：当hub表中不存在对应枢纽数据时，函数会正常完成数据插入，但该SELECT查询首次执行无任何返回结果，第二次执行才能正确返回对应的枢纽记录。

但直接单独调用函数的查询始终运行正常，即使是首次执行触发数据新增的场景，也能稳定返回正确的hub_id：

SELECT add_hub('
  {
   "name":"HUB TEST",
   "city":"Los Angeles",
   "province":"LA",
   "address":"Street 1",
   "region":"CA"
  }'
);

该异常由PostgreSQL的语句级一致性快照机制触发，和函数本身的增查逻辑无关：

• PostgreSQL中每条SQL语句启动时会生成一份一致性数据快照，整个语句执行期间，对表的扫描操作只能感知到快照生成前已提交的数据，无法读取到语句执行过程中同会话新写入的记录。
• 执行SELECT * FROM hub WHERE hub.id = add_hub(...)时，语句启动的初始快照中不存在待新增的枢纽记录。虽然add_hub函数执行时确实完成了数据插入、也返回了正确的新生成hub_id，但后续WHERE条件逐行匹配hub表数据时，读取的是初始快照的内容，刚插入的新行对当前扫描不可见，因此匹配不到任何结果，首次执行返回空。
• 第二次执行同语句时，第一次插入的记录早已提交，属于初始快照可见范围内的数据，函数直接匹配到已有记录返回ID，扫描时可以读到对应行，因此能正常返回结果。
• 单独执行SELECT add_hub(...)时不需要扫描hub表的存量数据，仅需返回函数输出值，因此无论是否触发新增逻辑都能正常得到返回结果。

避免在查询表数据的WHERE/SELECT子句中直接调用带写入操作的函数，可选实现方案如下：

• 方案一：拆分两步执行，逻辑最清晰无副作用
  第一步调用函数获取hub_id，第二步根据ID查询对应记录：

  -- 第一步：执行新增/匹配逻辑拿到ID
  SELECT add_hub('
  {
   "name":"HUB TEST",
   "city":"Los Angeles",
   "province":"LA",
   "address":"Street 1",
   "region":"CA"
  }'
  ) AS hub_id;
  -- 第二步：用拿到的ID查询完整记录
  SELECT * FROM hub WHERE id = 替换为上一步返回的ID;
  

• 方案二：用CTE包裹函数调用，单条语句实现需求
  CTE子句会先独立执行完成函数调用拿到返回ID，再用ID关联查询hub表，避开快照可见性问题：

  WITH hub_res AS (
      SELECT add_hub('
      {
       "name":"HUB TEST",
       "city":"Los Angeles",
       "province":"LA",
       "address":"Street 1",
       "region":"CA"
      }'
      ) AS hub_id
  )
  SELECT h.* FROM hub h
  INNER JOIN hub_res r ON h.id = r.hub_id;
  

注意：不要尝试通过修改事务隔离级别、在函数内加自主事务提交的方式绕过该问题，这类操作会破坏数据一致性，引入更多难以排查的异常。

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