使用Psycopg调用含dblink的PostgreSQL函数时出现搜索路径错误

使用psycopg2建立数据库连接时，指定search_path为my_og.my_schema：

conn = psycopg2.connect(
            host=os.environ['DATABASE_HOST'],
            port=os.environ['DATABASE_PORT'],
            database=str(base64.b64decode(
                os.environ['DATABASE_NAME']).decode('utf-8')),
            user=str(base64.b64decode(
                os.environ['DATABASE_USER']).decode('utf-8')),
            password=str(base64.b64decode(
                os.environ['DATABASE_PASSWORD']).decode('utf-8')),
            options=f'-c search_path=my_og.my_schema'
        )

随后执行函数调用：

cur.execute('SELECT get_data( %s,%s,%s); ',(str(month), str(year), str(region)))

PostgreSQL端的get_data函数会调用依赖public模式下dblink的run_consolidation函数，执行时触发错误：

psycopg2.errors.UndefinedFunction: function dblink(text, text) does not exist
2022-08-24T06:47:15.003662811Z LINE 1: SELECT * FROM dblink(db_string,'select * from (select...

提示无匹配给定名称和参数类型的函数，可能需要显式类型转换。该问题仅在Python调用时出现，PgAdmin中调用get_data可正常运行；若将search_path改为public，则无法找到my_og.my_schema中的get_data函数。

1. 扩展连接的search_path包含多模式 #

修改连接时的options参数，将search_path设置为同时包含my_og.my_schema和public，优先从目标模式查找对象，找不到再转向public：

options=f'-c search_path=my_og.my_schema,public'

2. 显式指定dblink的模式前缀 #

修改PostgreSQL中调用dblink的代码，直接添加public.前缀，彻底摆脱对会话级search_path的依赖：

-- 原调用代码
SELECT * FROM dblink(db_string, 'select * from ...');
-- 修改后
SELECT * FROM public.dblink(db_string, 'select * from ...');

3. 为函数设置独立的search_path属性 #

如果拥有函数修改权限，可调整get_data或run_consolidation函数的search_path属性，让函数执行时自动加载所需模式：

-- 以get_data为例，设置函数级search_path
ALTER FUNCTION my_og.my_schema.get_data(text, text, text)
SET search_path = my_og.my_schema, public;

此方式会让函数使用自身配置的search_path，不受会话级设置影响。

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