Unused older rows in the messages table prevents newer messages for other functional services from being delivered

[Thysk]

Description

On Rucio instances that have a messages table that is full of messages for one service that are undelivered, and you attempt to set up a new service type, messages for the new service are then generated but never picked up by Hermes as long as the backlog exists.
Hermes queries for 1000 messages by default, checks what service they are for and delivers accordingly.
If all 1000 messages are picked up for a service that is not configured this can block all other services' messages from being delivered.

Steps to reproduce

1. Create a service in the "services_list" and allow messages for said service to build up
2. Create a new service in the "services_list"
3. Configure new service to have a functional endpoint
4. Messages will be highly unlikely to be delivered to the new endpoint

Rucio Version

Server and Daemons 1.29, 1.30, 1.31, 32, 33, & 34

Additional Information

No response

[bari12]

The problem with the sub-query is that we need to index on this as well, otherwise, for a large table, it will be a heavy query.

But conceptually, I am not 100% sure if this is what we should do, in the end this is a queue table. So if there is a problem, this should rather be fixed. This way we might just ignore the problem and the messages accumulate.

[Thysk]

I agree that any problems should be addressed. But the issue is that while the issue is being addressed the other services messages are being prevented from being taken by Hermes and delivered should the service of issue not be resolved quickly.

Also ideally the message table should be basically empty, and only build up if there is an issue.
But I do see your point.
Is there any suggestions for this or should it be dropped as an issue?