[Bug] pg_cancel_backend() succeeds on Redshift Serverless but CLI hangs before receiving cancellation

### Is this a new bug?

- [x] I believe this is a new bug
- [x] I have searched the existing issues, and I could not find an existing issue for this bug

### Which packages are affected?

- [ ] dbt-adapters
- [ ] dbt-tests-adapter
- [ ] dbt-athena
- [ ] dbt-athena-community
- [ ] dbt-bigquery
- [ ] dbt-postgres
- [x] dbt-redshift
- [ ] dbt-snowflake
- [ ] dbt-spark

### Current Behavior

When running `dbt run` against a Redshift Serverless endpoint from the command line, cancelling a run (eg via Ctrl+C or a model error triggering cancellation of concurrent models) causes dbt to hang for around 800 seconds before the cancelled model connections are reported as errored.

`dbt run --debug` logs confirm that `pg_cancel_backend()` is called for each running query and returns `SUCCESS`, so the cancellation is reaching the server and is acknowledged. Despite this, the client connections waiting for query results do not receive the expected `query_canceled` error. Instead, they block until Redshift Serverless closes the TCP connection approximately 800 seconds later, at which point a `BrokenPipe: server socket closed` error is returned.

Checking `sys_query_history` on the Redshift Serverless instance immediately after the Ctrl+C cancel confirms the queries are no longer running.

### Expected Behavior

After `pg_cancel_backend()` returns `true`, the dbt connection that is waiting for results from the cancelled query should receive a `query_canceled` error, and the CLI should report that error and terminate normally.

### Steps To Reproduce

1. Configure a dbt project targeting a Redshift Serverless workgroup
2. Run `dbt run` from the command line, against a selection of models with `threads: 8` (or any value > 1)
3. While models are running concurrently, press Ctrl+C (or allow one model to error, eg by adding a non-existent column to the SQL, triggering cancellation of the remaining concurrent models)
4. dbt logs `CANCEL query ` for all running models, but then hangs for ~800 seconds
5. After ~800 seconds, all cancelled models report `BrokenPipe: server socket closed` and dbt exits

### Relevant log output

```shell
-- Truncated output from `dbt run --debug [more options...]

-- dbt sends pg_cancel_backend() for each running model; all return SUCCESS immediately
14:23:06 Redshift adapter: Cancel query on: 'model..model_a' with PID: 1073742216
14:23:06 SQL status: SUCCESS in 0.221 seconds
14:23:06 Redshift adapter: Cancel query on: 'model..model_b' with PID: 1073840432
14:23:06 SQL status: SUCCESS in 0.035 seconds
14:23:06 Redshift adapter: Cancel query on: 'model..model_c' with PID: 1073881505
14:23:06 SQL status: SUCCESS in 0.041 seconds
14:23:06 Redshift adapter: Cancel query on: 'model..model_d' with PID: 1073848896
14:23:06 SQL status: SUCCESS in 0.032 seconds
14:23:06 Redshift adapter: Cancel query on: 'model..model_e' with PID: 1073758838
14:23:06 SQL status: SUCCESS in 0.032 seconds
14:23:06 Redshift adapter: Cancel query on: 'model..model_f' with PID: 1073897815
14:23:06 SQL status: SUCCESS in 0.032 seconds
14:23:06 Redshift adapter: Cancel query on: 'model..model_g' with PID: 1073775146
14:23:06 SQL status: SUCCESS in 0.032 seconds
14:23:06 Redshift adapter: Cancel query on: 'model..model_h' with PID: 1073815724
14:23:06 SQL status: SUCCESS in 0.037 seconds

-- dbt logs the cancellations
14:23:06 CANCEL query model..model_a
14:23:06 CANCEL query model..model_b
14:23:06 CANCEL query model..model_c
14:23:06 CANCEL query model..model_d
14:23:06 CANCEL query model..model_e
14:23:06 CANCEL query model..model_f
14:23:06 CANCEL query model..model_g
14:23:06 CANCEL query model..model_h

-- ~800 seconds of silence...
-- Redshift Serverless closes the TCP connections at ~14:36:11

14:36:11 Redshift adapter: Not retrying error: Runtime Error
BrokenPipe: server socket closed. Please check that client side networking
configurations such as Proxies, firewalls, VPN, etc. are not affecting your
network connection.
14:36:11 2 of N ERROR creating sql table model ..model_b [ERROR in 801.95s]
14:36:11 ERROR creating sql table model ..model_c [ERROR in 786.60s]
14:36:11 ERROR creating sql table model ..model_d [ERROR in 793.45s]
14:36:11 ERROR creating sql table model ..model_e [ERROR in 793.45s]
14:36:11 ERROR creating sql table model ..model_f [ERROR in 787.78s]
14:36:11 ERROR creating sql table model ..model_g [ERROR in 802.23s]
14:36:15 ERROR creating sql table model ..model_a [ERROR in 805.66s]
14:36:15 Exited because of keyboard interrupt
```

### Environment

```markdown
- OS: Linux (Docker container)
- Python: 3.11.7
- dbt-core: 1.10.1
- dbt-adapters: 1.16.0
- dbt-redshift: 1.9.5
```

### Additional Context

The hang duration can be shortened by lowering `connect_timeout`, but doing so also causes `OperationalError: The read operation timed out` on any query that runs longer than `connect_timeout` seconds, so it seems like there's not an easy solution using the configs.