well that's a stretch it has "5 minute window"
You can hold a lock on a row in PG queue for as long as you need
pgmq (which is linked on this gist) provides an api to this functionality. It can be 0 seconds, or 10 years if you want. It's not a row lock in, which can be expensive. In pgmq, its build into the design of the visibility timeout. FOR UPDATE SKIP LOCKED is there to ensure that only a single consumer gets any message, and then the visibility timeout lets consumer determine how long it should continue to remain unavailable to other consumers.
Not sure why this is making people upset.
Because it's incorrect. If you have any non-postgres side-effect, you can't have exactly-once (unless you do 2PC or something like that). There isn't any technology that gives you "exactly once" in the general case.
That's not how exactly once is defined for queue. We are talking about semantics of what queue systems is providing.
Nobody will understand it like that.
Anyone who has ever selected queue service/product will understand it like that.
Because thats one of the most prominent features that gets highlighted by those products:
SQS Standard queues support at-least-once message delivery.