The PG_NET extension is an ingenious tool designed for PostgreSQL databases, allowing asynchronous HTTP/HTTPS requests directly from SQL queries. This sleek enhancement eliminates the need for constant polling, letting you focus on significant events within your database, while PG_NET takes care of notifying external resources when necessary. In this article, we’ll navigate through the installation, configuration, and usage of the PG_NET extension, step by step.

Contents

• Introduction
• Technical Explanation
• Installation
• Configuration
• Requests API
• Practical Examples
• Contributing

Introduction

Through the PG_NET extension, PostgreSQL is empowered to seamlessly handle HTTP/HTTPS requests by integrating directly into SQL. It leverages the power of triggers, cron jobs (like PG_CRON), and procedures for a variety of use cases including:

• Calling external APIs
• Syncing data with outside resources
• Triggering serverless functions upon specified events

Despite its capabilities, PG_NET currently supports only GET, POST with JSON payload, and DELETE HTTP requests.

Technical Explanation

Think of PG_NET as a skilled postman in a bustling city of data. It manages a postal queue — when you send requests to various addresses (i.e., APIs), PG_NET efficiently keeps track of which letters (or requests) need to be delivered and their responses. It accomplishes this with two unlogged tables:

• http_request_queue: Like a mailbox where the letters await collection, this table stores the requests before they’re sent off. It includes fields such as method, URL, headers, body, and timeout.

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

• _http_response: This table is like the return address on each envelope, capturing the response once the requests have been executed. It contains the response status, content, and any error messages.

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

When a request is made, it’s added to the ‘mailbox’ (http_request_queue), and PG_NET uses the libCurl library to manage these requests efficiently in the background.

Installation

Getting started with PG_NET is straightforward. Follow these instructions to install it on your PostgreSQL database:

Enabling the Extension with Supabase

1. Go to the Database page of your Supabase project.
2. Click on Extensions in the sidebar.
3. Search for pg_net and enable it.

Local Setup

For a manual setup, clone the repository and run the following commands:

make
make install

To enable PG_NET, add the following line to your postgresql.conf:

shared_preload_libraries = pg_net

Finally, activate the extension by running:

CREATE EXTENSION pg_net;

Extension Configuration

The PG_NET extension provides three configurable variables:

• pg_net.batch_size (default: 200): Limits how many rows are processed per read from the queue.
• pg_net.ttl (default: 6 hours): The lifespan of a row in _http_response before deletion.
• pg_net.database_name (default: postgres): Specifies the active database for the extension.

These settings can be modified in the postgresql.conf file or via SQL commands.

Requests API

PG_NET allows different types of HTTP requests — GET, POST, and DELETE. Below are examples of how to use these requests:

GET Requests

SELECT net.http_get('https://postman-echo.com/get?foo1=bar1&foo2=bar2') AS request_id;

POST Requests

SELECT net.http_post(
    'https://postman-echo.com/post',
    '{"key": "value"}'
);

DELETE Requests

SELECT net.http_delete('https://dummy.restapiexample.com/api/v1/delete/2');

Practical Examples

Here are some handy examples of using PG_NET:

Syncing Data with External Sources

Create a trigger to delete a record from an external service when it is deleted from your PostgreSQL database:

CREATE TRIGGER delete_products_trigger
AFTER DELETE ON public.products
FOR EACH ROW
EXECUTE FUNCTION delete_record();

Calling a Serverless Function Every Minute

Use PG_CRON to schedule regular queries:

SELECT cron.schedule('cron-job-name', '* * * * *', 
$$SELECT net.http_get('https://reference-id.functions.Supabase.co/example');$$);

Retrying Failed Requests

Identify and retry failed requests by querying the response table:

WITH retry_request AS (
    SELECT request_tracker.method, request_tracker.url 
    FROM request_tracker 
    INNER JOIN net._http_response ON net._http_response.id = request_tracker.request_id 
    WHERE net._http_response.status_code = 500
)
SELECT request_wrapper(retry_request.method, retry_request.url) 
FROM retry_request;

Contributing

If you’re interested in contributing to PG_NET, check out the Contributing page for more details.

Troubleshooting

If you encounter issues while using PG_NET, here are some troubleshooting ideas:

• Ensure that your PostgreSQL installation supports the required version and has the necessary permissions.
• Check if the libcurl version is 7.83 or higher.
• Consult the _http_response table to understand why requests may have failed.
• Adjust the pg_net.ttl or pg_net.batch_size parameters in the postgresql.conf file as needed.

For more insights, updates, or to collaborate on AI development projects, stay connected with fxis.ai.

At fxis.ai, we believe that such advancements are crucial for the future of AI, as they enable more comprehensive and effective solutions. Our team is continually exploring new methodologies to push the envelope in artificial intelligence, ensuring that our clients benefit from the latest technological innovations.