pg_net: Async Networking
The pg_net API is in beta. Functions signatures may change.
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
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pg_net" and 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
This is a Postgres SECURITY DEFINER function.
_17net.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
_10select_10 net.http_get('https://news.ycombinator.com')_10 as request_id;_10request_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
This is a Postgres SECURITY DEFINER function
_18net.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
_10select_10 net.http_post(_10 url:='https://httpbin.org/post',_10 body:='{"hello": "world"}'::jsonb_10 ) as request_id;_10request_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
This is a Postgres SECURITY DEFINER function
_18net.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
_10select_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.
_10CREATE 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.
_11CREATE 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:
_10select * 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
_10select_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
_10select_10 "content"_10from net._http_response_10where 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.
_34create or replace function debugging_example (row_id int)_34returns jsonb as $$_34declare_34 -- Store payload data_34 row_data_var jsonb;_34begin_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_34exception_34 when others then_34 raise exception 'An error occurred: %', SQLERRM;_34end;_34$$ language plpgsql;_34_34-- calling function_34select debugging_example(<row_id>);
Inspecting failed requests
Finds all failed requests
_10select_10 *_10from net._http_response_10where "status_code" >= 400 or "error_msg" is not null_10order by "created" desc;
Configuration
Must be on pg_net v0.10.0 or above to reconfigure
Supabase supports reconfiguring pg*net starting from v0.10.0+. For the latest release, initiate a postgres upgrade in the Infrastructure Settings.
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
_10select_10 "name",_10 "setting"_10from pg_settings_10where "name" like 'pg_net%';
Alter settings
Change variables:
_10alter role "postgres" set pg_net.ttl to '24 hour';_10alter role "postgres" set pg_net.batch_size to 500;
Then reload the settings and restart the pg_net
background worker with:
_10select 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:
_10select_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.
_13select 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_24create or replace function <function_name>()_24 returns trigger_24 language plpgSQL_24as $$_24begin_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;_24END $$;_24_24-- trigger for table update_24create 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
_13with "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)_13select_13 net.http_post(_13 url := 'https://postman-echo.com/post'::text,_13 body := JSON_payload_13 ) AS request_id_13FROM "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
- Source code: github.com/supabase/pg_net
- Official Docs: github.com/supabase/pg_net