Adapter packages missing dbt-core<2.0 upper bound; pip resolves to dbt-core 2.0.0-alpha.1 and breaks installs

## Current Behaviour

Since `dbt-core 2.0.0-alpha.1` was published to PyPI on 2026-06-01, a clean `pip install dbt-` (snowflake, bigquery, redshift, postgres, spark) resolves `dbt-core` to `2.0.0-alpha.1` — the new Rust-based Fusion engine — instead of the latest 1.x release. The python adapter code is not compatible with that engine, so subsequent dbt commands fail at parse/validation time.

Observed errors (verbatim from #1992, #1994):

- `dbt-postgres`, `dbt-spark` (and any other non-Fusion-supported adapter):
```
[error] [InvalidConfig (dbt1005)]: The 'postgres' adapter is not yet supported by dbt Fusion.
Supported adapters: snowflake, bigquery, databricks, redshift
```
- `dbt-snowflake` (and other adapters Fusion *does* claim to support, on legacy 1.x project configs):
```
[error] [SerializationError (dbt1013)]: Invalid model definition transient: models.transient:
invalid type: boolean false, expected struct ProjectModelConfig
[error] [UnusedConfigKey (dbt1060)]: Ignored unexpected key "always_update_columns"
```

**Root cause:** every adapter package in this monorepo except `dbt-athena` declares `dbt-core` with a lower bound only — e.g. `"dbt-core>=1.10.0rc0"` in `dbt-snowflake/pyproject.toml`. Because that lower bound is itself a pre-release specifier (`rc0`, `b3`, `rc1`), PEP 440 makes pip eligible to install other pre-releases for `dbt-core`, so `2.0.0-alpha.1` (the highest available version) gets selected.

The file even comments: `# add dbt-core to ensure backwards compatibility of installation, this is not a functional dependency` — exactly the assumption that no longer holds.

| Package | Current `dbt-core` constraint | Has `<2.0`? |
|---|---|---|
| `dbt-snowflake` | `"dbt-core>=1.10.0rc0"` | No |
| `dbt-bigquery` | `"dbt-core>=1.10.0rc0"` | No |
| `dbt-redshift` | `"dbt-core>=1.8.0b3"` | No |
| `dbt-postgres` | `"dbt-core>=1.8.0rc1"` | No |
| `dbt-spark` | `"dbt-core>=1.8.0rc1"` | No |
| `dbt-athena` | `"dbt-core>=1.8.0rc1,<2.0"` | **Yes** |

## Expected Behaviour

`pip install dbt-` should install a `dbt-core` version that is compatible with the adapter — i.e., a 1.x release of `dbt-core`. The new Fusion engine (`dbt-core 2.0+`) should only be installed when the user explicitly opts in.

The fix mirrors the upper bound already present on `dbt-common<2.0`, `dbt-adapters<2.0`, and the existing `dbt-athena` line:

```toml
"dbt-core>=1.10.0rc0,<2.0", # dbt-snowflake, dbt-bigquery
"dbt-core>=1.8.0b3,<2.0", # dbt-redshift
"dbt-core>=1.8.0rc1,<2.0", # dbt-postgres, dbt-spark
```

## Steps to Reproduce

1. Create a fresh virtual environment:
```bash
python -m venv /tmp/dbt-repro && source /tmp/dbt-repro/bin/activate
```
2. Install any affected adapter:
```bash
pip install dbt-postgres # or dbt-snowflake, dbt-bigquery, dbt-redshift, dbt-spark
```
3. Check the resolved versions:
```bash
pip show dbt-core | grep -i version
# Version: 2.0.0a1
dbt --version
# dbt-core 2.0.0-alpha.1
```
4. Initialize or run any 1.x dbt project ( `dbt init` or `dbt parse` )
5. Observe `dbt1005` ("adapter is not yet supported by dbt Fusion") for non-Fusion adapters, or `dbt1013` / `dbt1060` for Fusion-supported adapters with legacy 1.x configs.

## Environment

- OS: macOS 14 / Ubuntu 22.04 / Windows 11 (reproduces on all)
- Python: 3.11 (also 3.10, 3.12, 3.13)
- dbt-adapters (working version): 1.24.1
- dbt-adapters (regression version): 1.24.1 (unchanged — the regression is in `dbt-core`, but the adapter's missing upper bound is what allows pip to pick it up)
- dbt-core (working version): 1.10.x
- dbt-core (regression version): 2.0.0-alpha.1
- `` (working version): latest 1.x (e.g. dbt-snowflake 1.10.5, dbt-postgres 1.10.x)
- `` (regression version): same — adapter version is unchanged; the resolver picks up the wrong `dbt-core` alongside it

## Additional Context

**Related issues (consolidating):**
- #1992 — `dbt-postgres`: install pulls `dbt-core 2.0.0-alpha.1`, fails with `dbt1005` on Windows 11
- #1994 — `dbt-snowflake==1.10.5` pulls `dbt-core 2.0.0-alpha.1`, fails with `dbt1013`/`dbt1060`
- #1996 — Feature request: adapters should pin a specific tested `dbt-core` version (stricter than just `<2.0`)
- #1367 — `dbt-snowflake` pulled pre-release `dbt-core 1.11.0-b2` (October 2025; same root cause, surfaced 8 months earlier)
- #1434 — Maintainer-filed: add CI to test `pip install` against pre-release `dbt-core`
- #1408 — Adjacent: `dbt-postgres:latest` Docker image shipped wrong `dbt-core` version

**Likely affected, no dedicated issue filed yet:** `dbt-bigquery`, `dbt-redshift`, `dbt-spark` — same unbounded constraint pattern. `dbt-bigquery` already mentioned in passing on #1367.

**Workarounds users posted:**
- Manually pin: `pip install "dbt-core<2.0" dbt-`
- Add `dbt-core>=1.10,<2.0` to project `requirements.txt` alongside the adapter

**Proposed fix:**
1. Add `,<2.0` to the `dbt-core` line in `pyproject.toml` for `dbt-snowflake`, `dbt-bigquery`, `dbt-redshift`, `dbt-postgres`, `dbt-spark`. Verify `dbt-athena-community` (currently inherits from `dbt-athena` via `dbt-athena==1.11.0b1`).
2. Cut patch releases of each affected adapter.
3. Address #1434 in follow-up: pre-install / pre-release CI check so the next major bump doesn't repeat this.
4. Discuss #1996 separately — single-version pinning is a larger policy change.