You need to enable JavaScript to run this app. PostgreSQL函数首次运行正常后续报relation已存在错误如何解决?
优惠活动
大模型
产品
解决方案
定价
更多

PostgreSQL函数首次运行正常后续报relation已存在错误如何解决?

问题描述

该PL/pgSQL函数首次调用运行正常,代码如下:

CREATE OR REPLACE FUNCTION public.find_locations(
    search_text character varying,
    record_offset integer DEFAULT 0,
    fetch_quantity integer DEFAULT 20,
    sort_by character varying DEFAULT 'name',
    direction character varying DEFAULT 'ASC')
    RETURNS json
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
  CREATE TEMP TABLE locations_found AS
  SELECT
    locations._id AS id, locations.name, locations.address, locations.city, locations.state, locations."zipCode", locations.phone,
    locations.map, locations.directions, locations.review, locations.parking, locations.active
  FROM locations
  WHERE search_text IS NULL OR search_text = '' OR LOWER(locations.name) LIKE LOWER(search_text) || '%'
  ORDER BY
    CASE 
      WHEN direction = 'ASC' THEN
        CASE
          WHEN sort_by = 'name' THEN locations.name
          WHEN sort_by = 'address' THEN locations.address
          WHEN sort_by = 'active' THEN locations.active::varchar
        END
    END ASC,
    CASE
      WHEN direction = 'DESC' THEN
        CASE
          WHEN sort_by = 'name' THEN locations.name
          WHEN sort_by = 'address' THEN locations.address
          WHEN sort_by = 'active' THEN locations.active::varchar
        END
    END DESC;

  RETURN  
   json_build_object(
      'count',(SELECT COUNT(*) FROM locations_found),
      'offset', record_offset,
      'limit', fetch_quantity,
      'results', (SELECT json_agg(locations_found.*) FROM locations_found OFFSET record_offset LIMIT fetch_quantity)
    );
END;
$BODY$;

调用示例语句:

SELECT * FROM find_locations('North',0,2,'name','ASC');

第二次调用会抛出如下错误:

ERROR: relation "locations_found" already exists

疑问:原本认为临时表会在事务执行完成后自动销毁,无需显式删除,为什么第二次执行事务时该临时表仍然存在?

原因说明

PostgreSQL的临时表默认是会话级别的生命周期,只有当前数据库会话断开时才会自动销毁,事务提交不会触发默认临时表的清理。如果需要临时表在事务结束后自动删除,需要在创建时添加ON COMMIT DROP参数。你两次调用函数都在同一个会话中执行,第一次创建的临时表没有被释放,第二次执行创建语句时就会触发重复存在的报错。

修复方案

你改用公共表表达式(CTE)的修复方案是完全合理的:CTE的作用域仅限当前查询,执行完成后自动销毁,不会出现重复对象冲突,同时避免了临时表的写入开销,性能更优。修复后的代码如下:

CREATE OR REPLACE FUNCTION public.find_locations(
    search_text character varying,
    record_offset integer DEFAULT 0,
    fetch_quantity integer DEFAULT 20,
    sort_by character varying DEFAULT 'name'::character varying,
    direction character varying DEFAULT 'ASC'::character varying)
    RETURNS json
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
  response json;
BEGIN
  WITH locations_found AS (
    SELECT
      locations._id AS id, locations.name, locations.address, locations.city, locations.state, locations."zipCode", locations.phone,
      locations.map, locations.directions, locations.review, locations.parking, locations.active
    FROM locations
    WHERE search_text IS NULL OR search_text = '' OR LOWER(locations.name) LIKE LOWER(search_text) || '%'
    ORDER BY
      CASE 
        WHEN direction = 'ASC' THEN
          CASE
            WHEN sort_by = 'name' THEN locations.name
            WHEN sort_by = 'address' THEN locations.address
            WHEN sort_by = 'active' THEN locations.active::varchar
          END
      END ASC,
      CASE
        WHEN direction = 'DESC' THEN
          CASE
            WHEN sort_by = 'name' THEN locations.name
            WHEN sort_by = 'address' THEN locations.address
            WHEN sort_by = 'active' THEN locations.active::varchar
          END
      END DESC
  )
  SELECT json_build_object(
      'count',(SELECT COUNT(*) FROM locations_found),
      'offset', record_offset,
      'limit', fetch_quantity,
      'results', (SELECT json_agg(locations_found.*) FROM locations_found OFFSET record_offset LIMIT fetch_quantity)
    ) INTO response;
    RETURN response;
END;
$BODY$;

内容的提问来源于stack exchange,提问作者nstuyvesant

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.10 14:06:03