There are a few reasons why an error can be returned from descs.Collection when accessing descriptors:

1. the descriptor doesn't exist (and Required is specified or the descriptor was requested by ID)
2. the descriptor is dropped or is offline (and IncludeDropped or IncludeOffline were not set, though this also depends somewhat on Required)
3. other errors unrelated to the descriptor metadata itself (e.g., transaction retry errors when reading from KV)

Currently, in the case of (1), we generate type-appropriate "user-facing" errors with pgcodes like pgcode.UndefinedTable (though the catalogkv APIs one level down generally return catalog.ErrDescriptorNotFound). In the case of (2) we return some internal errors such as catalog.ErrDescriptorDropped, which are not type-specific.

The problems here are that:

1. We don't return a reliable sentinel error, like catalog.ErrDescriptorNotFound, for indicating that a descriptor was not found. This came up in sql/catalog: unify the descs.Collection resolution-by-ID API #57343: our special-case handling for past sequence ownership bugs requires checking the error. Maybe the solution is just to have the by-ID resolution methods also return a boolean found value. But we do sometimes check for catalog.ErrDescriptorNotFound and catalog.ErrDescriptorDropped in other places in the code, which is relying on implicit behavior.
2. We return a mix of user-facing and internal errors, and it's not clear to callers when they have to wrap the error themselves.

I think the solution is to have descs.Collection consistently return (possibly wrapped) sentinel errors like catalog.ErrDescriptorNotFound, and either only return user-facing or internal errors from the functions.

Epic: CRDB-2454

Jira issue: CRDB-3344