[Core] Adapter support for query cancellation affects behavior of `--fail-fast`

### Link to the page(s) on docs.getdbt.com requiring updates

https://docs.getdbt.com/reference/global-configs/failing-fast

### Tell us more about this update

The docs currently say:

> Supply the `-x` or `--fail-fast` flag to dbt run to make dbt exit immediately if a single resource fails to build. If other models are in-progress when the first model fails, then dbt will terminate the connections for these still-running models.

But this is only true if the adapter supports query cancellation.

So we should update our docs to soften the language and explain how it will behave differently depending on the capabilities of the dbt adapter that is being used.

### An example

For example, dbt-postgres behaves as currently described in the docs and cancels any queries that are running concurrently:

But dbt-duckdb allows any concurrently running query to finish (rather than cancelling them):

dbt-duckdb gives this specific warning when `--fail-fast` is specified and there is an exception:

```
The duckdb adapter does not support query cancellation. Some queries may still be running!
```

### Reviewers/Stakeholders/SMEs

.

### Related GitHub issues

https://github.com/dbt-labs/dbt-core/issues/10057

### Additional information

_No response_