colexec: clean up fragile eager cancellation mechanism in the parallel unordered sync needed for LOS

This issue tracks cleaning up the fragile and hacky eager cancellation mechanism we have in the parallel unordered synchronizer. This mechanism cancels the context for all inputs when the PUS transitions into draining and then the PUS swallows any context cancellation errors received from the inputs in order to not "poison" the query execution.

The current version is fragile and should be cleaned up. Namely, we have the following problems:
- we have two ways of signaling an operator to stop: 1) calling `DrainMeta` which is a "polite" ask to produce only rows from now on, and 2) canceling the context for a "rude" and ungraceful shutdown any work ASAP. The eager cancellation mechanism conflates the two when the whole query execution should be shut down gracefully.
- currently, there is no guarantee that if an operator observes the context cancellation, it'll be in the "valid" state to produce any necessary metadata. I do think it happens in practice, but we should codify it at least in comments / in the interface contract and audit relevant operators.

There is a lot of useful discussion in #133893.

Jira issue: CRDB-44166