sql, multiregion: support `ALTER DATABASE system DROP REGION` on the system tenant

## Summary

The system tenant currently cannot drop a region from the `system` database. Attempts to run:

```sql
ALTER DATABASE system DROP REGION "us-west1";
```

either fail because there are live sessions in that region, or—if those nodes are powered off—fail because replicas and system tables become unavailable. We need to extend the DROP REGION logic to work safely in the system tenant.

Previous issue for this was https://github.com/cockroachdb/cockroach/issues/126573

---

## Background

1. **Cannot drop while nodes are live**

```
ERROR: cannot drop region "us-west1"
SQLSTATE: 42P12
HINT: You must not have any active sessions that are in this region. Ensure that there no nodes that still belong to region "us-west1"
```

2. **Cannot drop after powering nodes down**

```sql
ERROR: replica unavailable: (n0,s0):? unable to serve request to r146:{-} [, next=0, gen=0]: lost quorum (down: ); closed timestamp: 0,0 (1970-01-01 00:00:00); raft status: : replica has been leaderless for 1m0s
```

Because cleaning up system tables (`system.sqlliveness`, `system.sql_instances`, etc.) requires reading or deleting rows in the target region, which is impossible when those replicas are offline.

3. The current implementation in [`alter_database.go`](https://github.com/cockroachdb/cockroach/blob/1c73c6f2bf71157afbf53f70e205cd0f506d7d1b/pkg/sql/alter_database.go#L497-L501) checks session liveness and then invokes `CleanupSystemTableForRegion`, which deletes rows in the dropped region.

---

## Ideas

1. **Skip session‐liveness checks** for regions that are fully down, by leveraging `region_liveness`.
2. **Avoid deletions** of system‐table rows when the region is offline. Instead, **UPDATE** their `crdb_region` to the primary region.
3. Display a notice similar to adding regions:

> *“Rolling restart is recommended after dropping a region from the system database to propagate region changes.”*

---

## Open Questions

**Safety of UPDATE while nodes are up:** Are there corner cases where in‐flight memory caches or zone configs need additional invalidation?
* **Enum cleanup:** How do we safely remove the region from the `crdb_region` enum once all references are gone?

---

## Notes from reproducing

```
❯ roachprod create rafi-mr-test --nodes=12 --geo --gce-zones='us-east1-d,us-west1-b,europe-west2-b,us-east1-b,us-west1-c,europe-west2-c,us-east1-c,us-west1-a,europe-west2-a,asia-east1-b,asia-east1-c,asia-east1-a'

❯ roachprod stage rafi-mr-test release v25.2.0-beta.3

❯ roachprod start rafi-mr-test:1-11

❯ roachprod sql rafi-mr-test:1

roachprod@localhost:26257/defaultdb> CREATE DATABASE mydb PRIMARY REGION "us-east1" REGIONS "us-west1", "europe-west2", "asia-east1";

roachprod@localhost:26257/defaultdb> use mydb;

roachprod@localhost:26257/mydb> SET CLUSTER SETTING sql.multiregion.system_database_multiregion.enabled = true;

roachprod@localhost:26257/mydb> CREATE TABLE my_table (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), value STRING) LOCALITY REGIONAL BY ROW;;

roachprod@localhost:26257/mydb> WITH region_data AS (
SELECT
unnest(ARRAY['us-east1', 'us-west1', 'europe-west2', 'asia-east1']) AS region
),
data AS (
SELECT
gen_random_uuid() AS id,
'value_' || i::string AS value,
region
FROM
generate_series(1, 10000) AS i,
region_data
WHERE
(i % 4) = (CASE
WHEN region = 'us-east1' THEN 0
WHEN region = 'us-west1' THEN 1
WHEN region = 'europe-west2' THEN 2
WHEN region = 'asia-east1' THEN 3
END)
)
INSERT INTO my_table (id, value, crdb_region)
SELECT id, value, region::crdb_internal_region FROM data;

❯ roachprod ssh rafi-mr-test:1

./cockroach sql --certs-dir=certs -u root

root@localhost:26257/defaultdb> ALTER DATABASE system SET PRIMARY REGION "us-east1";

root@localhost:26257/defaultdb> ALTER DATABASE system ADD REGION "us-west1";

root@localhost:26257/defaultdb> ALTER DATABASE system ADD REGION "europe-west2";

root@localhost:26257/defaultdb> ALTER DATABASE system ADD REGION "asia-east1";

❯ roachprod stop rafi-mr-test

❯ roachprod start rafi-mr-test:1-11

❯ roachprod ssh rafi-mr-test:1

./cockroach sql --certs-dir=certs -u root

root@localhost:26257/defaultdb> ALTER DATABASE system DROP REGION "asia-east1";
ERROR: cannot drop region "asia-east1" from the system database while that region is still in use
SQLSTATE: 2BP01
HINT: region is in use by databases: mydb

root@localhost:26257/defaultdb> ALTER DATABASE mydb DROP REGION "asia-east1";
ALTER DATABASE

root@localhost:26257/defaultdb> ALTER DATABASE system DROP REGION "asia-east1";
ERROR: cannot drop region "asia-east1"
SQLSTATE: 42P12
HINT: You must not have any active sessions that are in this region. Ensure that there no nodes that still belong to region "asia-east1"

# In another window
❯ roachprod stop rafi-mr-test:10-12

root@localhost:26257/defaultdb> ALTER DATABASE system DROP REGION "asia-east1";
ERROR: check-session-liveness-for-region: replica unavailable: (n0,s0):? unable to serve request to r121:{-} [, next=0, gen=0]: lost quorum (down: ); closed timestamp: 0,0 (1970-01-01 00:00:00); raft status: : replica has been leaderless for 1m0s

root@localhost:26257/defaultdb> select * from system.region_liveness ;
crdb_region | unavailable_at
--------------+-----------------
(0 rows)

root@localhost:26257/defaultdb> set cluster setting sql.region_liveness.enabled = true;
SET CLUSTER SETTING

root@localhost:26257/defaultdb> set cluster setting sql.region_liveness.probe.timeout = '5s';

root@localhost:26257/defaultdb> SET CLUSTER SETTING server.sqlliveness.ttl = '5s'

root@localhost:26257/defaultdb> select * from system.region_liveness ;
crdb_region | unavailable_at
--------------+-----------------------------
asia-east1 | 2025-05-03 15:36:20.545654
(1 row)

Time: 1ms total (execution 1ms / network 0ms)

root@localhost:26257/defaultdb> ALTER DATABASE system DROP REGION "asia-east1";
ERROR: check-session-liveness-for-region: replica unavailable: (n0,s0):? unable to serve request to r121:{-} [, next=0, gen=0]: lost quorum (down: ); closed timestamp: 0,0 (1970-01-01 00:00:00); raft status: : replica has been leaderless for 1m0s

root@localhost:26257/mydb> SELECT *, crdb_internal.sql_liveness_is_alive(session_id) FROM system.sqlliveness where crdb_region = 'us-west1' limit 1;
session_id | expiration | crdb_region | crdb_internal.sql_liveness_is_alive
-------------------------------------------+--------------------------------+-------------+--------------------------------------
\x0101c007e3b02f7f144434b7d12f02f2e10f23 | 1746286964022875955.0000000000 | us-west1 | t
(1 row)

root@localhost:26257/mydb> SELECT *, crdb_internal.sql_liveness_is_alive(session_id, false) FROM system.sqlliveness where crdb_region = 'asia-east1' limit 1;
ERROR: replica unavailable: (n0,s0):? unable to serve request to r121:{-} [, next=0, gen=0]: lost quorum (down: ); closed timestamp: 0,0 (1970-01-01 00:00:00); raft status: : replica has been leaderless for 1m0s

root@localhost:26257/defaultdb> select * from system.sql_instances;
ERROR: replica unavailable: (n0,s0):? unable to serve request to r128:{-} [, next=0, gen=0]: lost quorum (down: ); closed timestamp: 0,0 (1970-01-01 00:00:00); raft status: : replica has been leaderless for 1m0s
```

Jira issue: CRDB-50611

Epic CRDB-40070