PostgreSQL PL/pgSQL函数执行超时导致PHP脚本超时问题咨询

我来帮你梳理下这个问题的解决方案——核心痛点就是耗时的查询导出流程阻塞了ID的返回，导致PHP脚本超时拿不到关键标识。咱们可以通过拆分函数+异步解耦的方式彻底解决这个问题，下面给你两个可行的方案：

这个方案把同步执行的流程拆成两个独立部分，让ID能快速返回给PHP，耗时的导出任务丢给后台异步处理，完全避免超时问题。

步骤1：创建仅生成任务ID的轻量函数 #

这个函数只做两件事：生成唯一ID、插入adc_query_log日志，确保几毫秒内就能响应PHP：

CREATE OR REPLACE FUNCTION create_download_task(user_params jsonb)
RETURNS uuid AS $$
DECLARE
    task_id uuid := gen_random_uuid(); -- 用PostgreSQL内置函数生成唯一ID
BEGIN
    -- 插入日志表，记录任务ID、用户参数和初始状态
    INSERT INTO adc_query_log (task_id, params, status, created_at)
    VALUES (task_id, user_params, 'pending', NOW());
    
    RETURN task_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

步骤2：创建独立的导出执行函数 #

这个函数专门负责耗时的查询构建、CSV导出逻辑，只需要接收任务ID作为参数：

CREATE OR REPLACE FUNCTION execute_download_task(task_id uuid)
RETURNS void AS $$
DECLARE
    user_params jsonb;
    export_query text;
    -- 定义CSV文件存储路径，用任务ID命名确保唯一性
    csv_file_path text := '/var/postgres/exports/' || task_id || '_data.csv';
BEGIN
    -- 从日志表取出对应的用户参数
    SELECT params INTO user_params FROM adc_query_log WHERE task_id = $1;
    
    -- 构建导出查询（一定要用format函数做参数转义，防止SQL注入！）
    export_query := format(
        'COPY (
            SELECT col1, col2, col3 
            FROM your_target_table 
            WHERE filter_column = %L 
            AND date_range >= %L AND date_range <= %L
        ) TO %L WITH (FORMAT CSV, HEADER, DELIMITER '','')',
        user_params->>'filter_value',
        user_params->>'start_date',
        user_params->>'end_date',
        csv_file_path
    );
    
    -- 执行导出操作
    EXECUTE export_query;
    
    -- 更新日志表，标记任务完成
    UPDATE adc_query_log 
    SET status = 'completed', completed_at = NOW(), export_file_path = csv_file_path
    WHERE task_id = $1;
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获异常，标记任务失败并记录错误信息
        UPDATE adc_query_log 
        SET status = 'failed', error_msg = SQLERRM
        WHERE task_id = $1;
        RAISE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

步骤3：异步触发导出任务 #

PHP调用create_download_task拿到ID后，直接返回给前端或者继续后续逻辑，不需要等待导出完成。然后通过以下方式触发后台任务：

• 用PHP队列工具：比如Laravel Queue、Symfony Messenger，把调用execute_download_task的任务丢进队列，由后台Worker进程执行
• 用PostgreSQL扩展pg_cron：如果不想依赖PHP队列，可以安装pg_cron，定时扫描adc_query_log中的pending任务自动执行：

  -- 每分钟扫描一次待处理任务
  SELECT cron.schedule(
      'run-pending-downloads',
      '* * * * *',
      $$ SELECT execute_download_task(task_id) FROM adc_query_log WHERE status = 'pending' $$
  );
  

如果不想引入外部队列工具，可以用PostgreSQL的pg_notify和触发器实现异步通知：

步骤1：给日志表添加通知触发器 #

当新的任务日志插入时，自动发送通知：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION notify_download_task()
RETURNS trigger AS $$
BEGIN
    -- 发送通知到指定频道，携带任务ID
    PERFORM pg_notify('download_task_channel', NEW.task_id::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 给adc_query_log表添加INSERT触发器
CREATE TRIGGER trigger_notify_new_download
AFTER INSERT ON adc_query_log
FOR EACH ROW WHEN (NEW.status = 'pending')
EXECUTE FUNCTION notify_download_task();

步骤2：编写后台监听脚本 #

用简单的Shell/Python脚本监听通知频道，收到任务ID后调用导出函数：

#!/bin/bash
# 持续监听通知频道
psql -U your_db_user -d your_db_name -c "LISTEN download_task_channel;"
while true; do
    # 读取通知并处理
    psql -U your_db_user -d your_db_name -t -c "
        SELECT pg_notify('download_task_channel');
        -- 取出未处理的通知并执行导出
        WITH new_tasks AS (
            SELECT task_id FROM adc_query_log WHERE status = 'pending'
        )
        SELECT execute_download_task(task_id) FROM new_tasks;
    "
    sleep 1
done

步骤3：PHP调用流程 #

1. PHP调用create_download_task快速拿到唯一ID，立即返回给用户
2. 触发器自动发送通知，后台脚本收到后执行导出
3. 导出完成后，PHP可以通过任务ID查询adc_query_log的状态，完成打包和邮件发送

• SQL注入防护：构建导出查询时必须用format()函数做参数转义，绝对不能直接拼接用户输入！
• 文件权限：确保PostgreSQL进程有CSV存储路径的写入权限，同时PHP进程有读取权限用于打包
• 任务状态跟踪：一定要在adc_query_log中维护status（pending/completed/failed）、completed_at、error_msg字段，方便PHP追踪任务进度
• 超时与重试：可以给导出函数添加超时限制，或者在队列中配置重试机制，避免任务卡死

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