multi-region: Describe default crdb_region value for new Regional By Row table

John Sheaffer (sheaffej) commented:

We should add a clarifying note to the docs to explain that when you alter a table with SET LOCALITY REGIONAL BY ROW the value of crdb_region will be the database's primary region. As opposed to defaulting to the region value of the node performing the SET LOCALITY statement (which isn't stated in the docs, but considering that's how inserts would set the value, it's reasonable misinterpretation).

Most likely place would be in this section: https://www.cockroachlabs.com/docs/v21.1/set-locality.html#set-the-table-locality-to-regional-by-row

... probably just before the section starts talking about how to UPDATE that column value:

To update an existing row's home region, use an UPDATE statement like the following:

Here's an example showing that the primary region is used as the default.

demo@127.0.0.1:26257/movr> SHOW REGIONS FROM CLUSTER;

     region    |     zones
---------------+----------------
  europe-west1 | {az1,az2,az3}
  us-east1     | {az1,az2,az3}
  us-west1     | {az1,az2,az3}
(3 rows)

demo@127.0.0.1:26257/movr> ALTER DATABASE movr PRIMARY REGION "us-west1";
demo@127.0.0.1:26257/movr> ALTER DATABASE movr REGION "us-east1";
demo@127.0.0.1:26257/movr> ALTER DATABASE movr ADD REGION "europe-west1";

-- The database's PRIMARY region is us-west1
demo@127.0.0.1:26257/movr> SHOW REGIONS FROM DATABASE movr;

  database |    region    | primary |     zones
-----------+--------------+---------+----------------
  movr     | us-west1     |  true   | {az1,az2,az3}
  movr     | europe-west1 |  false  | {az1,az2,az3}
  movr     | us-east1     |  false  | {az1,az2,az3}
(3 rows)

-- Simplify the rides table
demo@127.0.0.1:26257/movr> ALTER TABLE rides ALTER PRIMARY KEY USING COLUMNS (id asc);
demo@127.0.0.1:26257/movr> ALTER TABLE rides ALTER COLUMN city DROP NOT NULL;
demo@127.0.0.1:26257/movr> DROP INDEX rides@rides_city_id_key cascade;

-- We are connected to a node in the us-east1 region
demo@127.0.0.1:26257/movr> show locality;
          locality
----------------------------
  region=us-east1,zone=az1
(1 row)

-- Set table locality to RBR
demo@127.0.0.1:26257/movr> ALTER TABLE rides SET LOCALITY REGIONAL BY ROW;

-- The crdb_region defaulted to us-west1 and not us-east1
select distinct crdb_region from rides;
  crdb_region
---------------
  us-west1
(1 row)

Jira Issue: DOC-1544

cockroachdb / docs

multi-region: Describe default crdb_region value for new Regional By Row table #10787