pg_net: Idiomatic synchronous execution / waiting for response?

[stefan-girlich]

I am using a wrapper function around pg_net to call an edge function when rows are inserted. For my previous use cases, the response from the edge function has been irrelevant; but for a new use case I want to consider the insert transaction valid only when the edge function call has finished or when the response contains a specific status code.

I already learned the following:

• An async parameter for http_get was removed for technical reasons. (source)
• net.http_collect_response() can be used to determine the execution state and status code (source)
  • but it's deprecated according to @steve-chavez (source)
  • and "must be in a separate transaction from the calls to net.http_<method>" which is not possible using my existing trigger function (source: docs linked above)
• Querying net._http_response directly is mentioned as alternative (source), but my corresponding code (see below) runs into a timeout because net._http_response is never updated and the HTTP call to the edge function is not executed. I assume that the query would need to be executed in a separate transaction.
• Other PostgreSQL HTTP modules are synchronous by design (e.g pgsql-http), but I'd prefer a solution using pg_net rather than adding a second HTTP module.

Looking forward to hear your ideas and suggestions!

Example code: Querying net._http_response

Omitted: code from my original wrapper function. I added the code below just above the final RETURN NEW:

LOOP
    SELECT
      status_code INTO http_status
    FROM
      net._http_response
    WHERE
      id = request_id;

    -- If the response is found, exit the loop
    IF http_status IS NOT NULL THEN EXIT;

    END IF;

    -- Increment the elapsed time
    elapsed_time := elapsed_time + wait_interval;

    -- Check if the maximum wait time has been exceeded
    IF elapsed_time >= max_wait_time THEN RAISE EXCEPTION 'Timeout waiting for HTTP response';

    END IF;

    -- Sleep for the wait interval before checking again
    PERFORM pg_sleep(wait_interval / 1000.0); -- pg_sleep expects seconds, not milliseconds
END LOOP;```

[steve-chavez]

For now I'd recommend just using pgsql-http as it offers sync requests.

For my previous use cases, the response from the edge function has been irrelevant; but for a new use case I want to consider the insert transaction valid only when the edge function call has finished or when the response contains a specific status code.

Although even with pgsql-http, this pattern is not recommended as it will lead to terrible performance (insertions will take seconds instead of milliseconds).

net.http_collect_response() can be used to determine the execution state and status code but it's deprecated according
Other PostgreSQL HTTP modules are synchronous by design (e.g pgsql-http), but I'd prefer a solution using pg_net rather than adding a second HTTP module.

That being said, I see how that's inconvenient. I've opened: supabase/pg_net#154.