supabase
Queues: Update the API documentation for 'read' + feature request of 'batch_delete' :)
Improve documentation
Link
https://supabase.com/docs/guides/queues/api#pgmqpublicreadqueuename-sleepseconds-n
Describe the problem
In the documentation for 'read'-ing of queues, the description says "optional 'sleep_seconds'". However, when I try to make an RPC call with the supabase-js client, I get the following error message if I don't include that parameter:
error: {
code: 'PGRST202',
details: 'Searched for the function pgmq_public.read with parameters n, queue_name or with a single unnamed json/jsonb parameter, but no matches were found in the schema cache.',
hint: 'Perhaps you meant to call the function pgmq_public.read(n, queue_name, sleep_seconds)',
message: 'Could not find the function pgmq_public.read(n, queue_name) in the schema cache'
},
It works fine when I specify the "sleep_seconds".
Describe the improvement
- When I read the documentation, I didn't understand what "sleep_seconds" means. Is it the column that says "status" in the queue? Also what is a recommended sleep_seconds? It would be great to hyperlink to a page with more information regarding this.
- Sort of a feature request in this issue, but is it possible to get a function to "delete in batch", as well? (My use-case: I want to read the first 10 messages, process them, and then delete them)
@olirice, tagging you to double-check whether the documented API is the intended one 🙏🏼
For the pgmq_public.read(queue_name, sleep_seconds, n) RPC, sleep_seconds is mandatory.
This is reflected in the current docs https://supabase.com/docs/guides/queues/api#pgmqpublicreadqueuename-sleepseconds-n
It is an optional field on send but required for read
I didn't understand what "sleep_seconds" means
Sleep seconds delays when the next consumer would be able to see the message you just read. So if consumer 1 reads message id 5 and sets sleep seconds to 10, no other consumers will be able to read mesage 5 until 10 seconds have elapsed. After those 10 seconds, its visible in the queue again
A typical pattern is for the consumer to "snooze" the messages (sleep_seconds), process it, and then delete or archive the message before it becomes visible to other consumers. This has the benefit that if the consumer dies while processing the message, it isn't permanently lost.
If you prefer a simpler but less safe setup, you could use pop instead of read, which removes the message from the queue immediately.
Sort of a feature request in this issue, but is it possible to get a function to "delete in batch", as well?
We aim to keep the interface as small as possible. This is a planned feature but we'd like to collect more usage metrics first to decide how to do this.
In the short term please continue deleting them one at a time
For context, delete from .. where message_id = any(array[<user_defined_list>]::int[])> when <user_defined_list> is large will fall off the message_id index and cause a slow seq scan over the whole table so that's why we're being careful about exposing it over the REST API
@olirice how large does the msg id list has to be to be considered "large"? I want to delete 1000 items from the queue, for example.
By the way, apparently pgmq has a batch delete function. Would manually exposing it to pgmq_public be unsafe for the same reasons you're describing?
Yes it would be unsafe to manually expose via pgmq_public you can fall off-index at any number > 1 but as a rule of thumb I'd expect it be much much less than 1k. More like between 6 and 30 depending on statistics on your queue table
@olirice Hello, any news about the delete batch ? Deleting item from queue one by one is quite slow.. Is there an example to see the optimal way to read/process/delete multiple elements?
I really need this batch delete. Calling one by one is insane. I want to first read, then process, and if everything is okay, then delete messages to ensure delivery. Pls this feature NOW!
We have queues with hundreds of thousands of items, processed by dozens of workers each processing batches of items -- very much missing batch deletes!
