search

supabase / pg_net

A PostgreSQL extension that enables asynchronous (non-blocking) HTTP/HTTPS requests with SQL
https://supabase.github.io/pg_net
Apache License 2.0
224 stars 18 forks source link

webhooks: order is not guaranteed on rapidly generated requests to same end point. #135

Open GaryAustin1 opened 3 months ago

GaryAustin1 commented 3 months ago

Improve documentation

I suggest adding a note in the docs that for rapidly generated requests order of the http requests at the endpoint is not guaranteed.

Based on observation testing realtime broadcast endpoint and bulk inserts the requests arrive out of order at the endpoint. I assume this is because the actual http request generated by pg_net does not wait for a response before sending the next request. With http there is no guarantee the requests will arrive in order especially with a very short time span between them.

The lack of order was observed both with realtime endpoint and a webhook service endpoint.

The sequence generating the order issue is a bulk insert 

insert into cats (count)
select * from generate_series (2300,2350)

The trigger function calls pg_net per row.

Note: It would be useful to have a mode where pg_net would be "sync" on the http side. It could wait for response before sending next response (especially if url is same). It will still be async on the SQL caller side so different that the http extension.

steve-chavez commented 4 days ago

I don't think any async HTTP client guarantees order, why is this needed?

steve-chavez commented 4 days ago

Note: It would be useful to have a mode where pg_net would be "sync" on the http side. It could wait for response before sending next response (especially if url is same). It will still be async on the SQL caller side so different that the http extension.

IIUC, you want the HTTP requests to be done in order, I don't think this is possible (or straightforward) with the current epoll implementation (https://github.com/supabase/pg_net/pull/139) as the file descriptors might be readable/writable at different times. But really this is a strange requirement for an async HTTP client. I would like to know if any other client supports something like that.

GaryAustin1 commented 4 days ago

Just saying it should be mentioned maybe at the webhook level as users issuing webhooks close together from something like inserts may expect them to show up in order at their edge function. Not sure pg_net is even mentioned at the webhook level. Also without looking into the details of pg_net it could be async in regards to the SQL call, but still enforce sync http requests from a queue. A user just looking at the docs may not think about it.

Maybe I'm naive, but had not thought about it before, and in doing realtime broadcasts with pg_net on bulk inserts, got caught off guard when they would show up out of order.

If you think all your users of webhooks understand this, then feel free to ignore.

Was not asking for a fix from pg_net, it just makes it not usable for doing realtime broadcasts where order might be needed. Which was the task at hand at the time to avoid using http extension or notify as a way to have higher performance than postgres_changes with RLS.

steve-chavez commented 4 days ago

You Can't Guarantee Webhook Ordering You obviously CAN guarantee ordering, it's just that you can't guarantee it as the sender, you need cooperation from the receiver.

References:

So it looks like the sender can't guarantee order.

If you think all your users of webhooks understand this

webhooks uses pg_net but is another component in the end, there's no control over it on this repo. Maybe this ordering matter can be documented once webhooks is established as a pg extension (see https://github.com/supabase/postgres/pull/1152). It may be added here or in another repo.