[store] Result list is silently limited to 10000 items

[barroco]

Currently, the DSS returns a maximum of 10000 items to clients when listing resources for RID and SCD. This value is set by the MaxResultLimit constant.
It seems that it was introduced to address OOM issues few years ago.

This issue has been created to discuss possible alternative approaches and make sure responses of the DSS are exhaustives.

[barroco]

This issue was discussed during the contributors sync call on 17/09/2024 and it was agreed the fact that the DSS is trimming results silently is an issue. The preferred approach discussed was to fail the response with a 400 or more appropriate error code in case the number of items is too big.

Pagination was mentioned as well but it would require changing the API. Requesting clients to better scope their request to reduce the number of items was preferred.

[hdimer]

Inventory of the remaining sites, as asked by @mickmis in the review of #1640, which does the RID identification service area case.

Client-facing searches that still truncate silently:

• RID subscriptions: SearchSubscriptions / SearchSubscriptionsByOwner via process in pkg/rid/store/sqlstore/subscriptions.go, and SearchSubscriptions in pkg/rid/store/memstore/subscriptions.go (which still carries the same // This mimics sqlstore behaviour, but it's not very good. TODO the ISA fix removed).
• SCD subscriptions: fetchSubscriptions in pkg/scd/store/sqlstore/subscriptions.go, and pkg/scd/store/memstore/subscriptions.go.
• SCD constraints: fetchConstraints in pkg/scd/store/sqlstore/constraints.go, and pkg/scd/store/memstore/constraints.go.
• SCD operational intents: fetchOperationalIntents in pkg/scd/store/sqlstore/operational_intents.go, and pkg/scd/store/memstore/operational_intents.go.

The ListExpired* queries use MaxResultLimit too, but those are the operator-run evict sweep: handling a limited number of entries per run and picking the rest up on the next one is the wanted behaviour there, not a non-exhaustive answer to a client. They should be left alone.

One gotcha for whoever takes these. The SCD loops stop on >= MaxResultLimit and the SQL side selects LIMIT MaxResultLimit, so neither can tell "exactly full" apart from "overflowing" - copying the predicate from the ISA fix would be off by one. Both backends need to look one item past the limit, which is what #1640 does with LIMIT MaxResultLimit + 1.