expand pg_net functionality with more operations and other data types

[davidaventimiglia]

Feature request

expand pg_net functionality with more operations and other data types

Is your feature request related to a problem? Please describe.

Yes. I would like to use pg_net to access REST micro-services in an asynchronous manner, when those services rely on other HTTP methods besides just GET, such as PUT, PATCH, and DELETE. Moreover, sometimes those services work with payloads that are not JSON and therefore cannot be passed to a PostgreSQL function as a json or jsonb data type.

Describe the solution you'd like

In addition to the existing net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds int) and net.http_post(url text, params jsonb, headers jsonb, timeout_milliseconds int) functions, I would like for there to be a master function net.http(request http_request, timeout_milliseconds int) function similar to the http.http(request http_request) function in the psql-http extension. Like in that extension, http_request would be a data type that has both a method and a content attribute, the latter being varchar. This would be enough to support other HTTP methods and other payloads.

Describe alternatives you've considered

I have considered and even used the synchronous http extension in conjunction with custom tables and the pg_cron extension to (re)implement a pseudo-async processor, but it's cumbersome and duplicative of the work that's in the pg_net extension.

Additional context

No other context is relevant.

[tvogel]

Lack of DELETE makes it impossible to use pg_net to clean up supabase storage objects in UPDATE/DELETE Postgres triggers. Or am I missing something?

[JulienLecoq]

Lack of DELETE makes it impossible to use pg_net to clean up supabase storage objects in UPDATE/DELETE Postgres triggers. Or am I missing something?

Was thinking the same

[steve-chavez]

DELETE is now supported, see #63 (comment).

Can be used on v0.7 as:

    select net.http_delete(
        url:='https://httpbin.org/delete'
    ,   params:= '{"param-foo": "bar"}'
    ,   headers:= '{"X-Baz": "foo"}'
    );

[erik-beus]

DELETE is now supported, see #63 (comment).

Can be used on v0.7 as:

    select net.http_delete(

        url:='https://httpbin.org/delete'

    ,   params:= '{"param-foo": "bar"}'

    ,   headers:= '{"X-Baz": "foo"}'

    );

That's awesome 👍 Do you know if it's possible to upgrade without downtime using supabase on cloud? 🤞

https://discord.com/channels/839993398554656828/1078585162943705099/1078599101769339010

[steve-chavez]

Maybe try with:

ALTER EXTENSION pg_net UPDATE 0.7

If that doesn't work, then you'd need to do a pause/restore.

[erik-beus]

Maybe try with:

ALTER EXTENSION pg_net UPDATE 0.7

If that doesn't work, then you'd need to do a pause/restore.

I just get this output:
version "0.2" of extension "pg_net" is already installed
It seems that on our instance it's only version 0.2 that's available. I'll try pausing the instance instead 👍

[JulienLecoq]

DELETE is now supported, see #63 (comment).

Can be used on v0.7 as:

    select net.http_delete(
        url:='https://httpbin.org/delete'
    ,   params:= '{"param-foo": "bar"}'
    ,   headers:= '{"X-Baz": "foo"}'
    );

Works wonderfully, but the README needs to be updated because it doesn't mention that DELETE is now supported 👍🏻

[GaryAustin1]

I'm going to add a request for DELETE to support a BODY.

Supabase storage-api has a delete that uses the body to pass pathnames to delete from a bucket.

I have a cron task that deletes files. It probably does not matter that it is much slower using http than pg_net, but there is no need for it to wait for the status of the delete operation (it is not even clear storage-api errors in the bulk delete case).

Using http I can group the files by bucket and do a single http request thus saving many http requests and storage-api overhead for each file. But this takes about 300msec for 10 files in one bucket. The problem is if I have 10 buckets with 1 file each it takes 2 seconds and pg_net with single delete would be the clear winner in the second case.

With pg_net DELETE the cron task takes .03 seconds either way, as I don't care about the status.

So the dilemma is a case where there are 100 files to delete in a few buckets. http bulk delete will take longer, but will drastically reduce the number of storage-api calls and http traffic. pg_net will be done very quickly in the cron task but have generated 100 http requests and storage-api calls.

I could use both extensions and make a decision based on the group by sizes but seems like over kill.

[GaryAustin1]

Looked pretty easy to add it...

create or replace function pg_net_http_delete_body(
    -- url for the request
    url text,
    -- key/value pairs to be url encoded and appended to the `url`
    params jsonb default '{}'::jsonb,
    -- body
    body jsonb default '{}'::jsonb,
    -- key/values to be included in request headers
    headers jsonb default '{}'::jsonb,
    -- the maximum number of milliseconds the request may take before being cancelled
    timeout_milliseconds int default 2000
)
    -- request_id reference
    returns bigint
    strict
    volatile
    parallel safe
    language plpgsql
    security definer
as $$
declare
    request_id bigint;
    params_array text[];
begin
    select coalesce(array_agg(net._urlencode_string(key) || '=' || net._urlencode_string(value)), '{}')
    into params_array
    from jsonb_each_text(params);

    -- Add to the request queue
    insert into net.http_request_queue(method, url, body, headers, timeout_milliseconds)
    values (
               'DELETE',
               net._encode_url_with_params_array(url, params_array),
               convert_to(body::text, 'UTF8'),
               headers,
               timeout_milliseconds
           )
    returning id
        into request_id;

    return request_id;
end
$$;

But no...
[42501] ERROR: permission denied for sequence http_request_queue_id_seq