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
相关产品推荐
相关产品推荐

