在PostgreSQL PL/Python中用plpy.execute调用Dask Delayed函数报错求助

问题场景 #

在PostgreSQL的PL/Python环境中，尝试通过Dask Delayed函数调用plpy.execute执行数据库查询时，触发栈深度超限错误：

ERROR:  spiexceptions.StatementTooComplex: stack depth limit exceeded

推测问题源于Dask Delayed的异步执行特性与plpy.execute的运行环境不兼容。

环境版本 #

• PostgreSQL 15
• 内置Python版本：3.8

示例代码 #

DO
LANGUAGE plpython3u
$$
    from dask import delayed
    
    @delayed
    def do_it():
        rv = plpy.execute("select 2 as a") -- 触发栈深度超限的位置
        return 0

    plpy.info(do_it().compute())
$$;

报错回溯 #

ERROR:  spiexceptions.StatementTooComplex: stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 7168kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  Traceback (most recent call last):
  PL/Python anonymous code block, line 10, in <module>
    plpy.info(do_it().compute())
  PL/Python anonymous code block, line 313, in compute
  PL/Python anonymous code block, line 598, in compute
  PL/Python anonymous code block, line 88, in get
  PL/Python anonymous code block, line 510, in get_async
  PL/Python anonymous code block, line 318, in reraise
  PL/Python anonymous code block, line 223, in execute_task
  PL/Python anonymous code block, line 118, in _execute_task
  PL/Python anonymous code block, line 7, in do_it
    rv = plpy.execute("select 2 as a") -- << max stack depth limit
PL/Python anonymous code block

问题原因与解决方案 #

核心原因 #

PL/Python的plpy模块与PostgreSQL的执行上下文强绑定，依赖当前数据库会话的栈环境和状态。而Dask Delayed的任务执行机制会创建多层调用栈，其异步执行上下文与PL/Python的会话上下文不兼容，导致调用plpy.execute时出现栈溢出。

解决方案 #

1. 避免在Dask Delayed任务中直接调用plpy模块
   将数据库查询逻辑改用纯Python数据库连接库（如psycopg2）建立独立连接执行，脱离PL/Python的会话上下文限制：

   DO
   LANGUAGE plpython3u
   $$
       from dask import delayed
       import psycopg2
       
       @delayed
       def do_it():
           # 建立独立数据库连接，参数需根据实际环境调整
           conn = psycopg2.connect("dbname=your_db user=your_user password=your_pass host=localhost")
           cur = conn.cursor()
           cur.execute("select 2 as a")
           result = cur.fetchone()
           cur.close()
           conn.close()
           return result[0]
   
       plpy.info(do_it().compute())
   $$;
   

   注意：需确保PL/Python环境已安装psycopg2。

2. 调整栈深度参数（不推荐）
   虽然报错提示可增加max_stack_depth参数，但这仅为临时 workaround，过度增大栈深度可能导致进程崩溃，不建议作为长期解决方案。

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