distsql: distinguish between "gateway only" and "concurrency disallowed"

[yuzefovich]

Distributed query plans are powered by LeafTxns which - unlike RootTxns - allow concurrent requests. Whenever we disable DistSQL, it's currently not clear why that happens: it could be either because:

• the query must run on the gateway (for example, we need to evaluate something via eval.Planner which we only have access to on the gateway)
• we cannot allow any concurrency for kv.Txn access (e.g. currently all mutations fall into this category)
• or both.

It would be beneficial to distinguish between these two reasons since we could then tailor our behavior accordingly. It'd be applicable in scenarios like #147853 is working through as well as address some existing drawbacks like

cockroach/pkg/sql/distsql_running.go

Lines 841 to 845 in 67cb06a

	// TODO(yuzefovich): this check is both excessive and insufficient.
	// For example:
	// - it disables the usage of the Streamer when a subquery has an
	// Oid type, but that would have no impact on usage of the Streamer
	// in the main query;

Jira issue: CRDB-51610