Database

pg_net: Async Networking

pg_net enables PostgreSQL to make asynchronous HTTP/HTTPS requests in SQL. It differs from the http extension in that it is asynchronous by default. This makes it useful in blocking functions (like triggers).

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

http_get

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

Signature


_17
net.http_get(
_17
-- url for the request
_17
url text,
_17
-- key/value pairs to be url encoded and appended to the `url`
_17
params jsonb default '{}'::jsonb,
_17
-- key/values to be included in request headers
_17
headers jsonb default '{}'::jsonb,
_17
-- the maximum number of milliseconds the request may take before being canceled
_17
timeout_milliseconds int default 2000
_17
)
_17
-- request_id reference
_17
returns bigint
_17
_17
strict
_17
volatile
_17
parallel safe
_17
language plpgsql

Usage


_10
select
_10
net.http_get('https://news.ycombinator.com')
_10
as request_id;
_10
request_id
_10
----------
_10
1
_10
(1 row)

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


_18
net.http_post(
_18
-- url for the request
_18
url text,
_18
-- body of the POST request
_18
body jsonb default '{}'::jsonb,
_18
-- key/value pairs to be url encoded and appended to the `url`
_18
params jsonb default '{}'::jsonb,
_18
-- key/values to be included in request headers
_18
headers jsonb default '{"Content-Type": "application/json"}'::jsonb,
_18
-- the maximum number of milliseconds the request may take before being canceled
_18
timeout_milliseconds int default 2000
_18
)
_18
-- request_id reference
_18
returns bigint
_18
_18
volatile
_18
parallel safe
_18
language plpgsql

Usage


_10
select
_10
net.http_post(
_10
url:='https://httpbin.org/post',
_10
body:='{"hello": "world"}'::jsonb
_10
) as request_id;
_10
request_id
_10
----------
_10
1
_10
(1 row)

http_delete

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

Signature


_18
net.http_delete(
_18
-- url for the request
_18
url text,
_18
-- key/value pairs to be url encoded and appended to the `url`
_18
params jsonb default '{}'::jsonb,
_18
-- key/values to be included in request headers
_18
headers jsonb default '{}'::jsonb,
_18
-- the maximum number of milliseconds the request may take before being canceled
_18
timeout_milliseconds int default 2000
_18
)
_18
-- request_id reference
_18
returns bigint
_18
_18
strict
_18
volatile
_18
parallel safe
_18
language plpgsql
_18
security definer

Usage


_10
select
_10
net.http_delete(
_10
'https://dummy.restapiexample.com/api/v1/delete/2'
_10
) as request_id;
_10
----------
_10
1
_10
(1 row)

Analyzing responses

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


_10
CREATE UNLOGGED TABLE
_10
net.http_request_queue (
_10
id bigint NOT NULL DEFAULT nextval('net.http_request_queue_id_seq'::regclass),
_10
method text NOT NULL,
_10
url text NOT NULL,
_10
headers jsonb NOT NULL,
_10
body bytea NULL,
_10
timeout_milliseconds integer NOT NULL
_10
)

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


_11
CREATE UNLOGGED TABLE
_11
net._http_response (
_11
id bigint NULL,
_11
status_code integer NULL,
_11
content_type text NULL,
_11
headers jsonb NULL,
_11
content text NULL,
_11
timed_out boolean NULL,
_11
error_msg text NULL,
_11
created timestamp with time zone NOT NULL DEFAULT now()
_11
)

The responses can be observed with the following query:


_10
select * from net._http_response;

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

Debugging requests

Inspecting request data

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.

Sending a post request to the echo API


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

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


_10
select
_10
"content"
_10
from net._http_response
_10
where id = <request_id>
_10
-- returns information about the request
_10
-- 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.


_34
create or replace function debugging_example (row_id int)
_34
returns jsonb as $$
_34
declare
_34
-- Store payload data
_34
row_data_var jsonb;
_34
begin
_34
-- Retrieve row data and convert to JSON
_34
select to_jsonb("<example_table>".*) into row_data_var
_34
from "<example_table>"
_34
where "<example_table>".id = row_id;
_34
_34
-- Initiate HTTP POST request to URL
_34
perform
_34
net.http_post(
_34
url := 'https://postman-echo.com/post',
_34
-- Use row data as payload
_34
body := row_data_var
_34
) as request_id;
_34
_34
-- Optionally Log row data or other data for inspection in Supabase Dashboard's Postgres Logs
_34
-- (https://supabase.com/dashboard/project/_/logs/postgres-logs)
_34
raise log 'Logging an entire row as JSON (%)', row_data_var;
_34
_34
-- return the row data to inspect
_34
return row_data_var;
_34
-- Handle exceptions here if needed
_34
exception
_34
when others then
_34
raise exception 'An error occurred: %', SQLERRM;
_34
end;
_34
$$ language plpgsql;
_34
_34
-- calling function
_34
select debugging_example(<row_id>);

Inspecting failed requests

Finds all failed requests


_10
select
_10
*
_10
from net._http_response
_10
where "status_code" >= 400 or "error_msg" is not null
_10
order by "created" desc;

Configuration

The extension is configured to execute up to 200 requests per second reliably. 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


_10
select
_10
"name",
_10
"setting"
_10
from pg_settings
_10
where "name" like 'pg_net%';

Alter settings

Note, that doing ALTER SYSTEM requires SUPERUSER but on PostgreSQL >= 15, you can do:


_10
grant alter system on parameter pg_net.ttl to <role>;
_10
grant alter system on parameter pg_net.batch_size to <role>;

Then variables can be changed:


_10
alter system set pg_net.ttl to '1 hour'
_10
alter system set pg_net.batch_size to 500;

Then reload the settings and restart the pg_net background worker with:


_10
select net.worker_restart();

Examples

Invoke a Supabase Edge Function

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


_10
select
_10
net.http_post(
_10
url:='https://project-ref.supabase.co/functions/v1/function-name',
_10
headers:='{"Content-Type": "application/json", "Authorization": "Bearer <YOUR_ANON_KEY>"}'::jsonb,
_10
body:='{"name": "pg_net"}'::jsonb
_10
) as request_id;

Call an endpoint every minute with pg_cron

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


_13
select cron.schedule(
_13
'cron-job-name',
_13
'* * * * *', -- Executes every minute (cron syntax)
_13
$$
_13
-- SQL query
_13
select "net"."http_post"(
_13
-- URL of Edge function
_13
url:='https://project-ref.supabase.co/functions/v1/function-name',
_13
headers:='{"Authorization": "Bearer <YOUR_ANON_KEY>"}'::jsonb,
_13
body:='{"name": "pg_net"}'::jsonb
_13
) as "request_id";
_13
$$
_13
);

Execute pg_net in a trigger

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


_24
-- function called by trigger
_24
create or replace function <function_name>()
_24
returns trigger
_24
language plpgSQL
_24
as $$
_24
begin
_24
-- calls pg_net function net.http_post
_24
-- sends request to postman API
_24
perform "net"."http_post"(
_24
'https://postman-echo.com/post'::text,
_24
jsonb_build_object(
_24
'old_row', to_jsonb(old.*),
_24
'new_row', to_jsonb(new.*)
_24
),
_24
headers:='{"Content-Type": "application/json"}'::jsonb
_24
) as request_id;
_24
return new;
_24
END $$;
_24
_24
-- trigger for table update
_24
create trigger <trigger_name>
_24
after update on <table_name>
_24
for each row
_24
execute function <function_name>();

Send multiple table rows in one request


_13
with "selected_table_rows" as (
_13
select
_13
-- Converts all the rows into a JSONB array
_13
jsonb_agg(to_jsonb(<table_name>.*)) as JSON_payload
_13
from <table_name>
_13
-- Generally good practice to LIMIT the max amount of rows
_13
)
_13
select
_13
net.http_post(
_13
url := 'https://postman-echo.com/post'::text,
_13
body := JSON_payload
_13
) AS request_id
_13
FROM "selected_table_rows";

More examples can be seen in the Extension's GitHub page

Limitations

  • To improve speed and performance, the requests and responses are stored in unlogged tables, which are not preserved during a crash or unclean shutdown.
  • By default, response data is saved for only 6 hours
  • Can only make POST requests with JSON data. No other data formats are supported
  • Intended to handle at most 200 requests per second. Increasing the rate can introduce instability
  • Does not have support for PATCH/PUT requests
  • Can only work with one database at a time in PostgreSQL. It defaults to the postgres database.

Resources