pg_net: Webhooks fail when multiple inserts are done

Bug report

Describe the bug

I have a webhook configured to watch a table for inserts. On an insert a POST request should be fired to an API hosted on Vercel. This works when one insert operation is carried out. If multiple inserts are carried out: .insert([{message: 1}, {message: 2}, {message: 3}]) then the webhooks aren’t fired at all. If instead we carry out multiple inserts by calling the Supabase API multiple times then the webhook is fired for only some of the inserts.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

  1. Configure a webhook on a table to watch for insert operations and call an external API

  2. Use the Supabase JS library to perform multiple inserts like: let { data, error } = await supabase .from("example_table") .insert([{message: 1}, {message: 2}, {message: 3}, {message: 4}, {message: 5}, {message: 6}, etc ]) .select("message");

  3. Check logs for external API to confirm it didn’t receive any requests

  4. See error

Something to note is that I was inserting 32 things at once.

Expected behavior

For each insert, I would expect the external API to be called. It would also help if there was a log in Supabase that showed what happens when the webhook is run.

Screenshots

If applicable, add screenshots to help explain your problem.

System information

  • OS: N/A
  • Browser (if applies): N/A
  • Version of supabase-js: 2.0.0-rc.8
  • Version of Node.js: v16.17.0 on desktop, 18.x on Vercel (per their docs)

Additional context

Add any other context about the problem here.

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Reactions: 16
  • Comments: 57 (14 by maintainers)

Commits related to this issue

Most upvoted comments

We’re experiencing the same problem. When inserting records in sequence all the webhooks are received. But if we insert 30 rows in parallel none of the webhooks send and the net/_http_response table is full of Timeout was reached errors.

Any new updates on this?

I’m running into this issue as well. I spun up a clean environment to reproduce, and did some testing. Supabase project qutskxfebtlgwyzsonvd. The setup is:

  • A users table
  • A queue table
  • A cron job that inserts one item into the queue for each user
  • A database webhook that sends the queue insert to an HTTPS endpoint, an AWS API Gateway
  • AWS API Gateway configured for asynchronous invocation, which always returns a 200 response (typically in <80ms)
  • AWS Lambda function that is just sleep(5)

I ran a handful of tests (spreadsheet here, screenshot below). It looks like a pretty linear relationship between the number of requests (in my case, the number of users for which the cron job inserts items into the queue) and the webhook timeout configuration. By my math, the webhook needs about 33.33ms per item to process. If only 20ms per item is available within the webhook timeout configuration, most or all of the events fail to be sent to the API Gateway.

Unlike the note from @lohrmann above, I did see some partial successes.

Screenshot 2024-01-06 at 6 22 11 PM

I did some testing in this area and found dropped requests due to ephemeral port exhaustion through docker networking. Limited testing of pg_net on bare metal did not reproduce dropped requests.

https://blog.sequinstream.com/benchmarking-pg_net-part-1/

pg_net has been super inconsistent. also tends to hang up sporadically. tried to build an event-driven app but decide to give up & rely on cron job instead

Any new updates on this?

By increasing the default timeout (2 seconds) to 4 seconds, all the above requests succeed on Supabase cloud:

        net.http_post(
            url := 'https://postman-echo.com/post',
            -- Use row data as payload
            body := row_data_var,
            timeout_milliseconds := 4000
        ) INTO row_id;
SELECT insert_test_data(100);

select count(*) from net._http_response where error_msg is null;
count
100

Will increase the default to 5 seconds.

The fix for this issue is now available on Supabase cloud πŸš€.

Make sure to check your pg_net version:

select * from pg_available_extensions where name ilike '%pg_net%';
name default_version installed_version comment
pg_net 0.13.0 0.13.0 Async HTTP

If you don’t have that version, you can upgrade by going to https://supabase.com/dashboard/project/_/settings/infrastructure or you can also create a new project.

Solved in https://github.com/supabase/pg_net/pull/139. Note that even at 1M requests we got no unexpected timeouts.

Issue still happening

I did some testing in this area and found dropped requests due to ephemeral port exhaustion through docker networking. Limited testing of pg_net on bare metal did not reproduce dropped requests.

https://blog.sequinstream.com/benchmarking-pg_net-part-1/

Fantastic write up!

@steve-chavez Limited testing. With .10 and a 5 second timeout I can do a bulk insert of 1000 rows and send 1000 realtime broadcast endpoint calls from a row trigger function and pg_net with no error. (tried twice). With 2 second timeout it fails in bunches of 100 or so working then not working. With a 1 second time a batch of 100 failed at 30 (1 try and was first try).

I did not test at 5 seconds before so don’t know if it is working better or not.

Can anyone confirm that this still happens on pg_net 0.10.0? To confirm the version do:


select extversion from pg_extension where extname = 'pg_net';
extversion
0.10.0

I’m no longer able to reproduce this previous error (https://github.com/supabase/pg_net/issues/86#issuecomment-1886040988) on Supabase cloud. We recently upgraded our libcurl version, which likely cleared the bug.

Yup, basically the webhook behaviour is flakey. I haven’t tried it recently. I wanted to use this to build an event driven architecture but can’t without it being reliable. Ended up using QStash as a replacement for the webhook. Would love Supabase to have some kind of full featured event queue like SQS/Eventbridge/Service Bus.

Can anyone confirm that this still happens on pg_net 0.10.0? To confirm the version do:

I want to test this out locally, but I cannot get that version in the CLI. I upgraded my preview environment last night, linked locally, and restarted so I do have the latest available docker images.

[Vicks-M1-Air]% npx supabase services


        SERVICE IMAGE      β”‚      LOCAL       β”‚   LINKED
  ─────────────────────────┼──────────────────┼─────────────
    supabase/postgres      β”‚ 15.6.1.118       β”‚ 15.6.1.118
    supabase/gotrue        β”‚ v2.160.0         β”‚ v2.160.0
    postgrest/postgrest    β”‚ v12.2.1          β”‚ -
    supabase/realtime      β”‚ v2.30.34         β”‚ -
    supabase/storage-api   β”‚ v1.11.0          β”‚ v1.11.0
    supabase/edge-runtime  β”‚ v1.58.2          β”‚ -
    supabase/studio        β”‚ 20240729-ce42139 β”‚ -
    supabase/postgres-meta β”‚ v0.83.2          β”‚ -
    supabase/logflare      β”‚ 1.4.0            β”‚ -
    supabase/supavisor     β”‚ 1.1.56           β”‚ -

[Vicks-M1-Air]% psql $PG_DEV
Timing is on.
Expanded display is used automatically.
psql (15.8 (Homebrew), server 15.6)
Type "help" for help.

postgres=> select extversion from pg_extension where extname = 'pg_net';
 extversion
------------
 0.8.0
(1 row)

Time: 4.371 ms
postgres=>
\q
[Vicks-M1-Air]% psql $PG_PREVIEW
Timing is on.
Expanded display is used automatically.
psql (15.8 (Homebrew), server 15.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=> select extversion from pg_extension where extname = 'pg_net';
 extversion
------------
 0.10.0
(1 row)

Time: 19.335 ms

Isn’t the docker image supposed to bring the newer version?

Is there any ETA on this? It’s blocking me and I would need to do some workaround. Thanks!

This bug has been open for a year, and it’s really frustrating that issues like this are not properly addressed by the Supabase team. They are adding other advanced features like branching, but the core remains unstable, and I don’t see a significant effort to fix that. I migrated a relatively small project from Firestore/Firebase to Supabase because I believed in their marketing, and now I really regret this decision. Currently, I’m paying more for significantly worse quality:

  • Webhooks are unstable. In my previous architecture, I heavily relied on Firestore triggers to perform some operations in the background. Now, they can randomly fail without any option to retry, which is a HUGE problem when something needs to happen in the background of our app.

  • Realtime functionality is useless; it only works fine when you have an active tab in the browser. Then, you need to figure out the whole mechanism for refetching data and reconnecting after you go from inactive. I don’t even want to mention how painful this is on mobile (React Native). With Firebase, I didn’t even have to think about it; it just worked out of the box.

  • Supervisor / pg_bouncer - Random failures when connecting to the DB from cloud functions / AWS Lambda (Connection terminated unexpectedly).

  • Upgrading your db? Okay, our app will be unavailable for minutes or hours, and then you need to figure out why something doesn’t work.

  • Storage - You can upload from RN, but first, you need to convert the entire file into base64 and then use ArrayBuffer.

But yes, Supabase is open-sourced. I guess I’ll tell my clients - our app doesn’t work properly, but at least you can read the source code.