pg_net: Async Networking

pg_net is a PostgreSQL extension exposing a SQL interface for async networking with a focus on scalability and UX.

It differs from the http extension in that it is asynchronous by default. This makes it useful in blocking functions (like triggers).

Usage#

Enable the extension#

http_get#

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

Signature#

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

Usage#

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

After triggering http_get, use http_get_result to get the result of the request.

http_post#

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

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

Signature#

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

Usage#

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)

After triggering http_post, use http_get_result to get the result of the request.

http_collect_response#

Given a request_id reference, retrieves the response.

When async:=false is set it is recommended that statement_timeout is set for the maximum amount of time the caller is willing to wait in case the response is slow to populate.

Signature#

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

Usage#

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)

Where response is a composite:

1status_code integer
2headers jsonb
3body text

Possible values for net.http_response_result.status are ('PENDING', 'SUCCESS', 'ERROR')

Resources#

• Source code: github.com/supabase/pg_net
• Official Docs: supabase.github.io/pg_net