pg_net: 异步网络

pg_net是一个PostgreSQL扩展，为异步网络暴露了一个SQL接口，重点是可扩展性和用户体验。

它与http扩展的不同之处在于，它默认是异步的。这使得它在阻塞函数（如触发器）中很有用。

用法

启用扩展功能

http_get {#http_get}#

创建一个HTTP GET请求，返回该请求的ID。在事务提交之前，HTTP请求不会被启动。

签名

1net.http_get(
2    -- url for the request
3    url text,
4    -- key/value pairs to be url encoded and appended to the `url`
5    params jsonb default '{}'::jsonb,
6    -- key/values to be included in request headers
7    headers jsonb default '{}'::jsonb,
8    -- WARNING: this is currently ignored, so there is no timeout
9    -- the maximum number of milliseconds the request may take before being cancelled
10    timeout_milliseconds int default 1000
11)
12    -- request_id reference
13    returns bigint
14
15    strict
16    volatile
17    parallel safe
18    language plpgsql

使用方法

1select net.http_get('https://news.ycombinator.com') as request_id;
2request_id
3----------
4         1
5(1 row)

在触发了http_get之后，使用http_get_result来获取请求的结果。

http_post {#http_post}#

创建一个带有JSON主体的HTTP POST请求，返回请求的ID。HTTP请求在事务提交之前不会被启动。

主体的字符集编码与数据库的server_encoding设置一致。

签名

1net.http_post(
2    -- url for the request
3    url text,
4    -- body of the POST request
5    body jsonb default '{}'::jsonb,
6    -- key/value pairs to be url encoded and appended to the `url`
7    params jsonb default '{}'::jsonb,
8    -- key/values to be included in request headers
9    headers jsonb default '{"Content-Type": "application/json"}'::jsonb,
10    -- WARNING: this is currently ignored, so there is no timeout
11    -- the maximum number of milliseconds the request may take before being cancelled
12    timeout_milliseconds int default 1000
13)
14    -- request_id reference
15    returns bigint
16
17    volatile
18    parallel safe
19    language plpgsql

使用方法

1select
2    net.http_post(
3        url:='https://httpbin.org/post',
4        body:='{"hello": "world"}'::jsonb
5    ) as request_id;
6request_id
7----------
8         1
9(1 row)

在触发了http_post之后，使用http_get_result来获得请求的结果。

http_collect_response {#http_collect_response}#

给出一个request_id参考，检索响应。

当async:=false被设置时，建议将statement_timeout设置为调用者愿意等待的最大时间，以防止响应填充缓慢。

签名

1net.http_collect_response(
2    -- request_id reference
3    request_id bigint,
4    -- when `true`, return immediately. when `false` wait for the request to complete before returning
5    async bool default true
6)
7    -- http response composite wrapped in a result type
8    returns net.http_response_result
9
10    strict
11    volatile
12    parallel safe

使用方法

1select
2    net.http_post(
3        url:='https://httpbin.org/post',
4        body:='{"hello": "world"}'::jsonb
5    ) as request_id;
6request_id
7----------
8         1
9(1 row)
10
11select * from net.http_collect_response(1, async:=false);
12status  | message | response
13--------+---------+----------
14SUCCESS        ok   (
15                      status_code := 200,
16                      headers     := '{"date": ...}',
17                      body        := '{"args": ...}'
18                    )::net.http_response_result
19
20
21select
22    (response).body::json
23from
24    net.http_collect_response(request_id:=1);
25                               body
26-------------------------------------------------------------------
27 {
28   "args": {},
29   "data": "{\"hello\": \"world\"}",
30   "files": {},
31   "form": {},
32   "headers": {
33     "Accept": "*/*",
34     "Content-Length": "18",
35     "Content-Type": "application/json",
36     "Host": "httpbin.org",
37     "User-Agent": "pg_net/0.2",
38     "X-Amzn-Trace-Id": "Root=1-61031a5c-7e1afeae69bffa8614d8e48e"
39   },
40   "json": {
41     "hello": "world"
42   },
43   "origin": "135.63.38.488",
44   "url": "https://httpbin.org/post"
45 }
46(1 row)

其中，response是一个组合：

1status_code integer
2headers jsonb
3body text

net.http_response_result.status的可能值是('PENDING', 'SUCCESS', 'ERROR')'。

资源

• 源代码: github.com/supabase/pg_net
• 官方文档: supabase.github.io/pg_net