pgsql-http中CURLOPT_TIMEOUT设置无效，循环请求超时求助

使用PL/pgSQL批量从HappyWhale获取encounter记录并存储到本地数据库，循环发送请求时频繁触发连接超时，调整CURLOPT_TIMEOUT参数无法解决问题。

原代码 #

do
$$
declare
    fromTime timestamp := '2000-01-01'::timestamp;
    toTime timestamp := 'now'::timestamp;
    deltaTime interval := '1 day'::interval;
    
    currTime timestamp;
    response record;
begin
    perform http_set_curlopt('CURLOPT_TIMEOUT', '30');
    perform http_set_curlopt('CURLOPT_TCP_KEEPALIVE', '30');
    create table if not exists encounters(id integer primary key, region text, species text, startdate timestamp, mincount integer, maxcount integer, geom geometry(point, 4326));
    currTime := fromTime;
    while currTime <= toTime loop
         select * into response from http((
              'POST'
             ,'https://critterspot.happywhale.com/v1/cs/admin/encounter/search'
             ,array[http_header('Connection', 'keep-alive'), http_header('Host', 'critterspot.happywhale.com')]
             ,'application/json'
             ,jsonb_build_object('showConnections', false, 'encounter', jsonb_build_object('datesearch', jsonb_build_object('startdate', to_char(currTime, 'YYYY-MM-DD'), 'type', 0)))
         )::http_request);
         if response.status <> 200 then
             raise notice '[%] %', currTime, (response.content::json)->>'message';
         else
             insert into encounters select
                  (e->>'id')::int
                 ,e->>'region'
                 ,e->>'species'
                 ,((e#>>'{dateRange,startDate}')::date + (e#>>'{dateRange,startTime}')::time)
                 ,(e->>'minCount')::int minCount
                 ,(e->>'maxCount')::int maxCount
                 ,ST_Point((e#>>'{location,lng}')::float, (e#>>'{location,lat}')::float, 4326) geom
             from (select json_array_elements(response.content::json) e) on conflict (id) do nothing;
             
             raise notice '[%] %', currTime, response.status;
             currTime := currTime + deltaTime;
         end if;
    end loop;
end;
$$

错误信息 #

NOTICE:  [2000-01-01 00:00:00] 200
NOTICE:  [2000-01-02 00:00:00] 200
NOTICE:  [2000-01-03 00:00:00] 200
NOTICE:  [2000-01-04 00:00:00] 200
NOTICE:  [2000-01-05 00:00:00] 200
NOTICE:  [2000-01-06 00:00:00] 200
NOTICE:  [2000-01-07 00:00:00] 200
NOTICE:  [2000-01-08 00:00:00] 200
NOTICE:  [2000-01-09 00:00:00] 200
NOTICE:  [2000-01-10 00:00:00] 200
NOTICE:  [2000-01-11 00:00:00] 200
NOTICE:  [2000-01-12 00:00:00] 200
NOTICE:  [2000-01-13 00:00:00] 200
NOTICE:  [2000-01-14 00:00:00] 200
ERROR:  Failed to connect to critterspot.happywhale.com port 443 after 1001 ms: Timeout was reached
CONTEXT:  SQL statement "select *               from http((
              'POST'
             ,'https://critterspot.happywhale.com/v1/cs/admin/encounter/search'
             ,array[http_header('Connection', 'keep-alive'), http_header('Host', 'critterspot.happywhale.com')]
             ,'application/json'
             ,jsonb_build_object('showConnections', false, 'encounter', jsonb_build_object('datesearch', jsonb_build_object('startdate', to_char(currTime, 'YYYY-MM-DD'), 'type', 0)))
         )::http_request)"
PL/pgSQL function inline_code_block line 15 at SQL statement 

已尝试操作 #

设置curl选项CURLOPT_TIMEOUT和CURLOPT_TCP_KEEPALIVE，但问题未解决：

perform http_set_curlopt('CURLOPT_TIMEOUT', '30');
perform http_set_curlopt('CURLOPT_TCP_KEEPALIVE', '30');

解决方案 #

1. 修正curl参数设置 #

CURLOPT_TCP_KEEPALIVE是布尔值，需设为1启用；同时单独设置CURLOPT_CONNECTTIMEOUT控制连接阶段的超时时间，避免和整体请求超时混淆：

perform http_set_curlopt('CURLOPT_TIMEOUT', '30'); -- 整体请求超时时间（秒）
perform http_set_curlopt('CURLOPT_CONNECTTIMEOUT', '10'); -- 连接服务器的超时时间（秒）
perform http_set_curlopt('CURLOPT_TCP_KEEPALIVE', '1'); -- 启用TCP保活机制
perform http_set_curlopt('CURLOPT_TCP_KEEPIDLE', '60'); -- 60秒无数据传输后发送第一个保活包
perform http_set_curlopt('CURLOPT_TCP_KEEPINTVL', '10'); -- 保活包发送间隔（秒）

2. 添加请求间隔与重试机制 #

短时间高频请求易触发目标服务器限流或拒绝连接，需在请求之间增加间隔，并对超时/失败请求进行重试：

3. 优化请求批次（可选） #

如果API支持，将按天请求改为按周/月的日期范围查询，减少总请求次数，降低触发限流的概率。

修改后的完整代码 #

do
$$
declare
    fromTime timestamp := '2000-01-01'::timestamp;
    toTime timestamp := 'now'::timestamp;
    deltaTime interval := '1 day'::interval;
    
    currTime timestamp;
    response record;
    retry_count integer := 3; -- 最大重试次数
    wait_interval interval := '2 seconds'; -- 请求间隔时间
begin
    -- 修正curl参数配置
    perform http_set_curlopt('CURLOPT_TIMEOUT', '30');
    perform http_set_curlopt('CURLOPT_CONNECTTIMEOUT', '10');
    perform http_set_curlopt('CURLOPT_TCP_KEEPALIVE', '1');
    perform http_set_curlopt('CURLOPT_TCP_KEEPIDLE', '60');
    perform http_set_curlopt('CURLOPT_TCP_KEEPINTVL', '10');
    
    create table if not exists encounters(id integer primary key, region text, species text, startdate timestamp, mincount integer, maxcount integer, geom geometry(point, 4326));
    currTime := fromTime;
    
    while currTime <= toTime loop
        retry_loop: for i in 1..retry_count loop
            begin
                select * into response from http((
                    'POST'
                   ,'https://critterspot.happywhale.com/v1/cs/admin/encounter/search'
                   ,array[http_header('Connection', 'keep-alive'), http_header('Host', 'critterspot.happywhale.com')]
                   ,'application/json'
                   ,jsonb_build_object('showConnections', false, 'encounter', jsonb_build_object('datesearch', jsonb_build_object('startdate', to_char(currTime, 'YYYY-MM-DD'), 'type', 0)))
                )::http_request);
                
                exit retry_loop; -- 请求成功，退出重试循环
            exception
                when others then
                    if i = retry_count then
                        raise notice '[%] 重试%d次后仍失败: %', currTime, retry_count, SQLERRM;
                        currTime := currTime + deltaTime;
                        continue;
                    else
                        raise notice '[%] 请求失败，第%d次重试...', currTime, i;
                        perform pg_sleep(wait_interval);
                    end if;
            end;
        end loop retry_loop;
        
        if response.status = 200 then
            insert into encounters select
                (e->>'id')::int
               ,e->>'region'
               ,e->>'species'
               ,((e#>>'{dateRange,startDate}')::date + (e#>>'{dateRange,startTime}')::time)
               ,(e->>'minCount')::int minCount
               ,(e->>'maxCount')::int maxCount
               ,ST_Point((e#>>'{location,lng}')::float, (e#>>'{location,lat}')::float, 4326) geom
            from (select json_array_elements(response.content::json) e) on conflict (id) do nothing;
            
            raise notice '[%] 请求成功，状态码: %', currTime, response.status;
        else
            raise notice '[%] 请求失败，状态码: %，信息: %', currTime, response.status, (response.content::json)->>'message';
        end if;
        
        currTime := currTime + deltaTime;
        perform pg_sleep(wait_interval); -- 增加请求间隔，避免高频访问
    end loop;
end;
$$

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