pg_net：异步网络

pg_net API 还在测试阶段。函数签名可能会改变。

🌐 The pg_net API is in beta. Functions signatures may change.

pg_net 使 Postgres 可以在 SQL 中进行异步的 HTTP/HTTPS 请求。它与 http 扩展的不同之处在于，它默认就是异步的。这在阻塞函数（例如触发器）中非常有用。

它无需服务器不断轮询数据库的变化，而是让数据库主动通知外部资源重要事件。

🌐 It eliminates the need for servers to continuously poll for database changes and instead allows the database to proactively notify external resources about significant events.

启用扩展 #

🌐 Enable the extension

在仪表板中转到数据库页面。
点击侧边栏的扩展。
搜索“pg_net”并启用这个扩展。

`http_get`#

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

🌐 Creates an HTTP GET request returning the request's ID. HTTP requests are not started until the transaction is committed.

签名 #

🌐 Signature [#get-signature]

这是一个 Postgres 安全定义者函数。

🌐 This is a Postgres SECURITY DEFINER function.

1
net.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
    -- the maximum number of milliseconds the request may take before being canceled
9
    timeout_milliseconds int default 2000
10
)
11
    -- request_id reference
12
    returns bigint
13
14
    strict
15
    volatile
16
    parallel safe
17
    language plpgsql

使用情况 #

🌐 Usage [#get-usage]

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

`http_post`#

创建一个带有 JSON 内容的 HTTP POST 请求，并返回请求的 ID。HTTP 请求只有在事务提交后才会开始。

🌐 Creates an HTTP POST request with a JSON body, returning the request's ID. HTTP requests are not started until the transaction is committed.

主体的字符集编码与数据库的 server_encoding 设置相匹配。

🌐 The body's character set encoding matches the database's server_encoding setting.

签名 #

🌐 Signature [#post-signature]

这是一个 Postgres 安全定义者函数

🌐 This is a Postgres SECURITY DEFINER function

1
net.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
    -- the maximum number of milliseconds the request may take before being canceled
11
    timeout_milliseconds int default 2000
12
)
13
    -- request_id reference
14
    returns bigint
15
16
    volatile
17
    parallel safe
18
    language plpgsql

使用 #

🌐 Usage [#post-usage]

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

`http_delete`#

创建一个 HTTP DELETE 请求，并返回请求的 ID。HTTP 请求直到事务提交后才会开始。

🌐 Creates an HTTP DELETE request, returning the request's ID. HTTP requests are not started until the transaction is committed.

签名 #

🌐 Signature [#post-signature]

这是一个 Postgres 安全定义者函数

🌐 This is a Postgres SECURITY DEFINER function

1
net.http_delete(
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
    -- the maximum number of milliseconds the request may take before being canceled
9
    timeout_milliseconds int default 2000
10
)
11
    -- request_id reference
12
    returns bigint
13
14
    strict
15
    volatile
16
    parallel safe
17
    language plpgsql
18
    security definer

使用 #

🌐 Usage [#delete-usage]

1
select
2
    net.http_delete(
3
        'https://dummy.restapiexample.com/api/v1/delete/2'
4
    ) as request_id;
5
----------
6
         1
7
(1 row)

分析回应 #

🌐 Analyzing responses

等待的请求存储在 net.http_request_queue 表中。执行后，它们会被删除。

🌐 Waiting requests are stored in the net.http_request_queue table. Upon execution, they are deleted.

1
CREATE UNLOGGED TABLE
2
    net.http_request_queue (
3
        id bigint NOT NULL DEFAULT nextval('net.http_request_queue_id_seq'::regclass),
4
        method text NOT NULL,
5
        url text NOT NULL,
6
        headers jsonb NOT NULL,
7
        body bytea NULL,
8
        timeout_milliseconds integer NOT NULL
9
    )

一旦有响应返回，默认情况下，它会在 net._http_response 表中存储 6 个小时。

🌐 Once a response is returned, by default, it is stored for 6 hours in the net._http_response table.

1
CREATE UNLOGGED TABLE
2
    net._http_response (
3
        id bigint NULL,
4
        status_code integer NULL,
5
        content_type text NULL,
6
        headers jsonb NULL,
7
        content text NULL,
8
        timed_out boolean NULL,
9
        error_msg text NULL,
10
        created timestamp with time zone NOT NULL DEFAULT now()
11
    )

可以用以下查询来观察这些响应：

🌐 The responses can be observed with the following query:

1
select * from net._http_response;

这些数据也可以在 net 模式下通过 Supabase 仪表板的 SQL 编辑器进行查看

🌐 The data can also be observed in the net schema with the Supabase Dashboard's SQL Editor

调试请求 #

🌐 Debugging requests

检查请求数据 #

🌐 Inspecting request data

Postman Echo API 会返回一个与请求内容相同的响应。你可以用它来查看发送的数据。

🌐 The Postman Echo API returns a response with the same body and content as the request. It can be used to inspect the data being sent.

向回声 API 发送一个 POST 请求

🌐 Sending a post request to the echo API

1
select
2
    net.http_post(
3
        url := 'https://postman-echo.com/post',
4
        body := '{"key1": "value", "key2": 5}'::jsonb
5
    ) as request_id;

检查回声 API 响应内容，确保它包含正确的主体

🌐 Inspecting the echo API response content to ensure it contains the right body

1
select
2
    "content"
3
from net._http_response
4
where id = <request_id>
5
-- returns information about the request
6
-- including the body sent: {"key": "value", "key": 5}

另外，通过将请求封装在一个数据库函数中，发送的行数据可以被记录或返回以供检查和调试。

🌐 Alternatively, by wrapping a request in a database function, sent row data can be logged or returned for inspection and debugging.

1
create or replace function debugging_example (row_id int)
2
returns jsonb as $$
3
declare
4
    -- Store payload data
5
    row_data_var jsonb;
6
begin
7
    -- Retrieve row data and convert to JSON
8
    select to_jsonb("<example_table>".*) into row_data_var
9
    from "<example_table>"
10
    where "<example_table>".id = row_id;
11
12
    -- Initiate HTTP POST request to URL
13
    perform
14
        net.http_post(
15
            url := 'https://postman-echo.com/post',
16
            -- Use row data as payload
17
            body := row_data_var
18
        ) as request_id;
19
20
    -- Optionally Log row data or other data for inspection in Supabase Dashboard's Postgres Logs
21
    raise log 'Logging an entire row as JSON (%)', row_data_var;
22
23
    -- return row data to inspect
24
    return row_data_var;
25
26
-- Handle exceptions here if needed
27
exception
28
    when others then
29
        raise exception 'An error occurred: %', SQLERRM;
30
end;
31
$$ language plpgsql;
32
33
-- calling function
34
select debugging_example(<row_id>);

检查失败的请求 #

🌐 Inspecting failed requests

查找所有失败的请求

🌐 Finds all failed requests

1
select
2
  *
3
from net._http_response
4
where "status_code" >= 400 or "error_msg" is not null
5
order by "created" desc;

配置 #

🌐 Configuration

必须使用 pg_net v0.12.0 或更高版本才能重新配置

从 v0.12.0+ 开始，Supabase 支持重新配置 pg*net。对于最新版本，可以在基础设施设置中启动 Postgres 升级。

🌐 Supabase supports reconfiguring pg*net starting from v0.12.0+. For the latest release, initiate a Postgres upgrade in the Infrastructure Settings.

这个扩展被配置为可以可靠地每秒执行最多 200 个请求。响应消息只会存储 6 小时，以防不必要的堆积。默认行为可以通过重写配置变量来修改。

🌐 The extension is configured to reliably execute up to 200 requests per second. The response messages are stored for only 6 hours to prevent needless buildup. The default behavior can be modified by rewriting config variables.

获取当前设置 #

🌐 Get current settings

1
select
2
  "name",
3
  "setting"
4
from pg_settings
5
where "name" like 'pg_net%';

更改设置 #

🌐 Alter settings

你必须在系统级别更改 pg_net 设置。

🌐 You must change the pg_net settings at the system level.

更改这些设置需要超级用户权限。联系支持以获得你想要更改的参数的所需权限，例如：

🌐 Changing these settings requires superuser privileges. Contact Support to have the required permission granted for the parameter you want to change, e.g.:

1
grant alter system on parameter pg_net.ttl to postgres;

一旦权限被分配，在系统级别应用设置并重启后台工作程序：

🌐 Once the privilege is assigned, apply the setting at the system level and restart the background worker:

1
alter system set pg_net.ttl to '24 hours';
2
select net.worker_restart();

示例 #

🌐 Examples

调用 Supabase Edge 函数 #

🌐 Invoke a Supabase Edge Function

向 Supabase Edge Function 发送带有认证头和 JSON 请求体的 POST 请求：

🌐 Make a POST request to a Supabase Edge Function with auth header and JSON body payload:

1
select
2
    net.http_post(
3
        url:='https://project-ref.supabase.co/functions/v1/function-name',
4
        headers:='{"Content-Type": "application/json", "apikey": "<SUPABASE_PUBLISHABLE_KEY>"}'::jsonb,
5
        body:='{"name": "pg_net"}'::jsonb
6
    ) as request_id;

用 pg_cron #

🌐 Call an endpoint every minute with pg_cron

pg_cron 扩展让 Postgres 可以变成自己的 cron 服务器。借助它，你可以以最小一分钟的精度定期调用接口。

🌐 The pg_cron extension enables Postgres to become its own cron server. With it you can schedule regular calls with up to a minute precision to endpoints.

1
select cron.schedule(
2
	'cron-job-name',
3
	'* * * * *', -- Executes every minute (cron syntax)
4
	$$
5
	    -- SQL query
6
	    select "net"."http_post"(
7
            -- URL of Edge function
8
            url:='https://project-ref.supabase.co/functions/v1/function-name',
9
            headers:='{"apikey": "<SUPABASE_PUBLISHABLE_KEY>"}'::jsonb,
10
            body:='{"name": "pg_net"}'::jsonb
11
	    ) as "request_id";
12
	$$
13
);

在触发器中执行 pg_net #

🌐 Execute pg_net in a trigger

当触发事件发生时，向外部端点发起一次调用。

🌐 Make a call to an external endpoint when a trigger event occurs.

1
-- function called by trigger
2
create or replace function <function_name>()
3
    returns trigger
4
    language plpgSQL
5
as $$
6
begin
7
    -- calls pg_net function net.http_post
8
    -- sends request to postman API
9
    perform "net"."http_post"(
10
      'https://postman-echo.com/post'::text,
11
      jsonb_build_object(
12
        'old_row', to_jsonb(old.*),
13
        'new_row', to_jsonb(new.*)
14
      ),
15
      headers:='{"Content-Type": "application/json"}'::jsonb
16
    ) as request_id;
17
    return new;
18
END $$;
19
20
-- trigger for table update
21
create trigger <trigger_name>
22
    after update on <table_name>
23
    for each row
24
    execute function <function_name>();

一次发送多行表格 #

🌐 Send multiple table rows in one request

1
with "selected_table_rows" as (
2
    select
3
        -- Converts all the rows into a JSONB array
4
        jsonb_agg(to_jsonb(<table_name>.*)) as JSON_payload
5
    from <table_name>
6
    -- good practice to LIMIT the max amount of rows
7
)
8
select
9
    net.http_post(
10
        url := 'https://postman-echo.com/post'::text,
11
        body := JSON_payload
12
    ) AS request_id
13
FROM "selected_table_rows";

更多示例可以在扩展的GitHub页面上看到

🌐 More examples can be seen on the Extension's GitHub page

限制 #

🌐 Limitations

为了提高速度和性能，请求和响应会存储在未记录表中，这些表在系统崩溃或非正常关闭时不会被保留。
默认情况下，响应数据只会保存 6 小时
只能使用 JSON 数据进行 POST 请求，不支持其他数据格式
设计最多处理每秒200个请求。提高速率可能会导致不稳定
不支持PATCH/PUT请求
一次只能处理一个数据库。默认使用 postgres 数据库。

资源 #

🌐 Resources

源代码：github.com/supabase/pg_net
官方文档：github.com/supabase/pg_net

启用扩展 #

http_get#

签名 #

使用情况 #

http_post#

签名 #

使用 #

http_delete#

签名 #

使用 #

分析回应 #

调试请求 #

检查请求数据 #

检查失败的请求 #

配置 #

获取当前设置 #

更改设置 #

示例 #

调用 Supabase Edge 函数 #

用 pg_cron#

在触发器中执行 pg_net #

一次发送多行表格 #

限制 #

资源 #

`http_get`#

`http_post`#

`http_delete`#

用 pg_cron #