pg_net

All requests made via pg_net are logged in the net._http_response table. To identify failed requests, query this table for status codes $\ge 500$.

Because net._http_response does not store the original request payload (body, params, etc.), a robust retry pattern involves:

1. Creating a custom request_tracker table to store all request metadata.
2. Using a request_wrapper function to perform the request and log the details to your tracker simultaneously.
3. Running a retry query that joins your tracker with net._http_response to re-issue failed calls.

-- 1. Find failed requests
SELECT * FROM net._http_response WHERE status_code >= 500;

-- 2. Example retry logic using a tracker and wrapper
WITH retry_request AS (
    SELECT
        rt.method, rt.url, rt.params, rt.body, rt.headers, rt.request_id
    FROM request_tracker rt
    INNER JOIN net._http_response nr ON nr.id = rt.request_id
    WHERE nr.status_code >= 500
    LIMIT 3
),
retry AS (
    SELECT request_wrapper(method, url, params, body, headers) FROM retry_request
),
delete_old_logs AS (
    DELETE FROM net._http_response WHERE id IN (SELECT request_id FROM retry_request)
)
DELETE FROM request_tracker WHERE request_id IN (SELECT request_id FROM retry_request);

The extension provides several configurable parameters that can be adjusted via postgresql.conf or ALTER SYSTEM. If you change pg_net.database_name or pg_net.username, you must restart the background worker using net.worker_restart() for changes to take effect.

Configurable Variables:

• pg_net.batch_size (default: 200): Max rows processed from net.http_request_queue per read.
• pg_net.ttl (default: 6 hours): Max time a row lives in net._http_response before deletion.
• pg_net.database_name (default: 'postgres'): The database the extension is applied to.
• pg_net.username (default: NULL): The user the background worker connects as (defaults to bootstrap user).

-- View current settings
SHOW pg_net.batch_size;

-- Update settings
ALTER SYSTEM SET pg_net.ttl TO '1 hour';
ALTER SYSTEM SET pg_net.batch_size TO 500;

-- Reload configuration
SELECT pg_reload_conf();

-- Restart worker (required for database_name or username changes)
SELECT net.worker_restart();

Use net.http_post to perform asynchronous HTTP POST requests with a JSON payload. The function returns a bigint representing the request_id.

Function Signature:

net.http_post(
    url text,
    body jsonb default '{}'::jsonb, -- The JSON payload
    params jsonb default '{}'::jsonb, -- URL encoded key/value pairs
    headers jsonb default '{"Content-Type": "application/json"}'::jsonb,
    timeout_milliseconds int default 1000
) returns bigint

-- Simple POST with JSON body
SELECT net.http_post(
    'https://postman-echo.com/post',
    '{"key": "value", "key": 5}'::JSONB,
    headers := '{"API-KEY-HEADER": "<API KEY>"}'::JSONB
) AS request_id;

-- Send a single row from a table as a JSON payload
WITH selected_row AS (
    SELECT * FROM target_table LIMIT 1
)
SELECT
    net.http_post(
        'https://postman-echo.com/post',
        to_jsonb(selected_row.*),
        headers := '{"API-KEY-HEADER": "<API KEY>"}'::JSONB
    ) AS request_id
FROM selected_row;

-- Send multiple rows as a single JSON array payload
WITH selected_rows AS (
    SELECT jsonb_agg(to_jsonb(target_table)) AS JSON_payload
    FROM target_table
)
SELECT
    net.http_post(
        'https://postman-echo.com/post',
        JSON_payload,
        headers := '{"API-KEY-HEADER": "<API KEY>"}'::JSONB
    ) AS request_id
FROM selected_rows;

Use the net.http_delete function to send HTTP DELETE requests from PostgreSQL. You can pass URL parameters as a jsonb object, which will be URL-encoded and appended to the URL, or include custom headers.

Function Signature:

net.http_delete(
    url text,
    params jsonb default '{}'::jsonb,
    headers jsonb default '{}'::jsonb,
    timeout_milliseconds int default 2000
) returns bigint

Parameters:

• url: The target URL for the request.
• params: Key/value pairs to be URL-encoded and appended to the URL.
• headers: Key/values to be included in the request headers.
• timeout_milliseconds: Maximum time in milliseconds before the request is cancelled (default: 2000).

Returns a bigint representing the request_id.

-- Simple delete request
SELECT net.http_delete('https://dummy.restapiexample.com/api/v1/delete/2') AS request_id;

-- Delete request with query parameters
SELECT net.http_delete(
    'https://dummy.restapiexample.com/api/v1/delete/'::TEXT,
    format('{"id": "%s"}', id)::JSONB
) AS request_id
FROM target_table
LIMIT 1;

Use net.http_get to perform asynchronous HTTP GET requests. The function returns a bigint representing the request_id, which you can use to track the request.

Function Signature:

net.http_get(
    url text,
    params jsonb default '{}'::jsonb, -- URL encoded key/value pairs
    headers jsonb default '{}'::jsonb, -- Request headers
    timeout_milliseconds int default 1000
) returns bigint

To view the response after the request completes, query the net._http_response table.

-- Basic GET request
SELECT net.http_get ('https://postman-echo.com/get') AS request_id;

-- GET request with URL encoded params
SELECT net.http_get(
  'https://postman-echo.com/get',
  '{"foo1": "bar1", "encoded": "!"}'::JSONB
) AS request_id;

-- GET request with custom headers
SELECT net.http_get(
  'https://postman-echo.com/get',
   headers := '{"API-KEY-HEADER": "<API KEY>"}'::JSONB
) AS request_id;

-- Check the response
SELECT * FROM net._http_response;