sql: make sql liveness and descriptor leasing able to region failure while only SURVIVE ZONE.

[chengxiong-ruan]

Is your feature request related to a problem? Please describe.
Currently Serverless needs to chose between configuring the system database as SURVIVE REGION or SURVIVE ZONE. Configuring the database as SURVIVE REGION is optimal for availability whereas configuring the database as SURVIVE ZONE is optimal for cold start performance.

For most tables in the system database, write performance is not important for cold start performance. The only tables that need fast writes are system.sqlliveness, system.sql_instances, and system.lease. These tables are already partitioned as RBR tables. The high level idea is to configure these tables as SURVIVE ZONE and configure all other tables as SURVIVE REGION.

Actually making this work will require changes to liveness and lease subsystems to handle region failure. One possible approach is to introduce a global “region_liveness” table. If a region is unable to read from another region’s RBR data, it would mark the region as unreachable in the region_liveness table. After enough time has passed to ensure all leases in the region have expired, the region is marked as down. If a sql server wants to extend leases, the sql server would read from the region_liveness table in the transaction that extends the lease. The server is only allowed to extend it’s leases if the region is marked as available.

Jira issue: CRDB-28158

Epic CC-24173

[rmloveland]

@fqazi I'm assigned to work on docs for this epic (via DOC-8234)

I built cockroach from the master branch and am seeing the system.public.region_liveness table which was added in your commit here

show columns from system.public.region_liveness;
   column_name   | data_type | is_nullable | column_default | generation_expression |        indices         | is_hidden
-----------------+-----------+-------------+----------------+-----------------------+------------------------+------------
  crdb_region    | BYTES     |      f      | NULL           |                       | {region_liveness_pkey} |     f
  unavailable_at | TIMESTAMP |      t      | NULL           |                       | {region_liveness_pkey} |     f
(2 rows)

Based on reading the issue description above and looking at some of the other tables in system.public, it seems like this new region_liveness system table is not a user-facing feature and will only be used as part of the setup when other system tables are set as REGIONAL BY ROW during some of our multi-region cloud deployments. Is that correct?

(I also assumed this is not user-facing because in your commit you wrote "Release note: None", but I am confirming since I'm assigned a doc task for the epic)

[fqazi]

@rmloveland Right, no user-facing change is visible by this PR yet.

[rmloveland]

@fqazi thanks for confirming! I will likely close DOC-8234 then