Skip to content

SHOW RANGES does not show expected results in multi-region cluster with super regions #106128

Closed
Closed
SHOW RANGES does not show expected results in multi-region cluster with super regions#106128
Labels
C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.T-kvKV Team
@codingconcepts

Description

@codingconcepts
codingconcepts
opened on Jul 4, 2023

Epic: CRDB-30635
Problem

Creating a multi-region cluster with super regions does not appear to replicate data correctly.

To Reproduce

Create the cluster:

cockroach demo \
  --insecure \
  --no-example-database \
  --nodes 12 \
  --demo-locality=region=us-east1,az=a:region=us-east1,az=b:region=us-east2,az=a:region=us-east2,az=b:region=us-west2,az=a:region=us-west2,az=b:region=eu-west2,az=a:region=eu-west2,az=b:region=eu-west3,az=a:region=eu-west3,az=b:region=eu-north1,az=a:region=eu-north1,az=b

Create the database and populate a table:

CREATE DATABASE "meetup";
USE "meetup";

ALTER DATABASE "meetup" SET PRIMARY REGION "us-east1";
ALTER DATABASE "meetup" ADD REGION "us-east2";
ALTER DATABASE "meetup" ADD REGION "us-west2";
ALTER DATABASE "meetup" ADD REGION "eu-west2";
ALTER DATABASE "meetup" ADD REGION "eu-west3";
ALTER DATABASE "meetup" ADD REGION "eu-north1";

ALTER DATABASE "meetup" SURVIVE REGION FAILURE;

SET enable_super_regions = 'on';
ALTER DATABASE "meetup" ADD SUPER REGION us VALUES "us-east1", "us-east2", "us-west2";
ALTER DATABASE "meetup" ADD SUPER REGION eu VALUES "eu-west2", "eu-west3", "eu-north1";

CREATE TABLE "product" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "name" STRING NOT NULL,
  "market" STRING NOT NULL,
  "crdb_region" CRDB_INTERNAL_REGION AS (
    CASE
        WHEN "market" IN ('ie', 'uk') THEN 'eu-west2'
        WHEN "market" IN ('de', 'fr') THEN 'eu-west3'
        WHEN "market" IN ('no', 'se') THEN 'eu-north1'
        WHEN "market" IN ('us') THEN 'us-east1'
        ELSE 'eu-west2'
    END
  ) STORED,
  "amount" DECIMAL NOT NULL,
  "currency" STRING NOT NULL,

  INDEX ("market")
) LOCALITY REGIONAL BY ROW;

INSERT INTO product ("id", "name", "market", "amount", "currency") VALUES
  ('b7eaba08-2d20-4109-9a39-aeba20c486a4', 'Americano', 'uk', 3.80, 'gbp'),
  ('279d936b-2f68-445d-9301-cd062d283859', 'Latte', 'uk', 4.15, 'gbp'),
  ('2586aa2c-84e0-4f28-b299-732c2314aa91', 'Cappuccino', 'uk', 4.15, 'gbp'),

  ('fd16dd63-d9ea-418f-8353-a891287a7be0', 'Americano', 'ie', 4.40, 'gbp'),
  ('7ad66c52-6a6a-4f9f-a590-4b5ede1c82f1', 'Latte', 'ie', 4.80, 'gbp'),
  ('1a8b5e94-220e-411a-8f26-fe43dd6485f1', 'Cappuccino', 'ie', 4.80, 'gbp'),

  ('2f22da46-d983-4878-8ad2-a6e6ff7e8f39', 'Americano', 'de', 4.40, 'eur'),
  ('a5ece2cb-6b76-46fd-a11b-a53dcf2f0669', 'Latte', 'de', 4.80, 'eur'),
  ('de17b8db-a6dc-48a3-a33b-1272365c7226', 'Cappuccino', 'de', 4.80, 'eur'),

  ('a63a07fd-1f7a-41b7-bcaa-67c0118cd3f2', 'Américain', 'fr', 4.40, 'eur'),
  ('279c0aea-21a0-48dd-8197-0bbf0f7defd0', 'Latté', 'fr', 4.80, 'eur'),
  ('29bc6a72-35b8-456a-8e92-de5899bb8c0a', 'Cappuccino', 'fr', 4.80, 'eur'),

  ('6acf78f8-e6ed-4a02-a899-9978f958f513', 'Américain', 'no', 51.41, 'eur'),
  ('9b52c9ef-76ef-42d2-8302-0a74a0dad1c7', 'Latté', 'no', 56.15, 'eur'),
  ('a7b54333-dc25-4c01-bfb0-d93205af30a9', 'Cappuccino', 'no', 56.15, 'eur'),

  ('fe0b83b5-4573-439b-87ab-1193f12e01fb', 'Américain', 'se', 52.27, 'sek'),
  ('6214d841-9a52-4f06-8085-16cf076704e6', 'Latté', 'se', 57.09, 'sek'),
  ('f04f5fa9-88ba-4a52-b0e7-f77bb516f15a', 'Cappuccino', 'se', 57.09, 'sek'),

  ('b6f2327f-80c3-44ce-9c3c-d0c04736c90f', 'Americano', 'us', 4.80, 'usd'),
  ('0cc06e87-713c-4edd-af0b-627be0fb6843', 'Latte', 'us', 5.24, 'usd'),
  ('c880c792-32df-43d7-9b4d-d5745c842f4b', 'Cappuccino', 'us', 5.24, 'usd');

Show replicas by region and az:

SELECT DISTINCT
  split_part(split_part(unnest(replica_localities), ',', 1), '=', 2) region,
  split_part(split_part(unnest(replica_localities), ',', 2), '=', 2) az,
  unnest(replicas) replica
FROM [SHOW RANGES FROM TABLE product]
ORDER BY replica;
   region   | az | replica
------------+----+----------
  us-east1  | a  |       1
  us-east1  | b  |       2
  us-east2  | a  |       3
  us-east2  | b  |       4
  us-west2  | a  |       5
  us-west2  | b  |       6
  eu-west2  | a  |       7
  eu-west2  | b  |       8
  eu-west3  | a  |       9
  eu-west3  | b  |      10
  eu-north1 | a  |      11
  eu-north1 | b  |      12

Show replicas and ranges by region (all):

WITH
  replicas AS (
    SELECT DISTINCT
      split_part(unnest(replica_localities), ',', 1) replica_localities,
      replicas
    FROM [SHOW RANGES FROM TABLE product]
  ),
  ranges AS (
    SELECT
      replicas,
      range_id
    FROM [SHOW RANGES FROM TABLE product]
  )
SELECT
  split_part(re.replica_localities, '=', 2) region,
  re.replicas,
  array_agg(ra.range_id) range_ids
FROM replicas re
JOIN ranges ra ON re.replicas = ra.replicas
GROUP BY re.replica_localities, re.replicas
ORDER BY range_ids;
   region   |     replicas      |   range_ids
------------+-------------------+----------------
  us-east1  | {2,3,5,7,9,11,12} | {64}
  us-east2  | {2,3,5,7,9,11,12} | {64}
  us-west2  | {2,3,5,7,9,11,12} | {64}
  eu-west2  | {2,3,5,7,9,11,12} | {64}
  eu-west3  | {2,3,5,7,9,11,12} | {64}
  eu-north1 | {2,3,5,7,9,11,12} | {64}
  eu-west2  | {7,8,9,11,12}     | {65,79,77,67}
  eu-west3  | {7,8,9,11,12}     | {65,79,77,67}
  eu-north1 | {7,8,9,11,12}     | {65,79,77,67}
  us-east1  | {1,2,3,5,7,9,12}  | {66}
  us-east2  | {1,2,3,5,7,9,12}  | {66}
  us-west2  | {1,2,3,5,7,9,12}  | {66}
  eu-west2  | {1,2,3,5,7,9,12}  | {66}
  eu-west3  | {1,2,3,5,7,9,12}  | {66}
  eu-north1 | {1,2,3,5,7,9,12}  | {66}
  us-east1  | {2,4,5,7,8,9,11}  | {68}
  us-east2  | {2,4,5,7,8,9,11}  | {68}
  us-west2  | {2,4,5,7,8,9,11}  | {68}
  eu-west2  | {2,4,5,7,8,9,11}  | {68}
  eu-west3  | {2,4,5,7,8,9,11}  | {68}
  eu-north1 | {2,4,5,7,8,9,11}  | {68}
  eu-west2  | {7,9,10,11,12}    | {69,81}
  eu-west3  | {7,9,10,11,12}    | {69,81}
  eu-north1 | {7,9,10,11,12}    | {69,81}
  us-east1  | {2,3,4,5,8,9,11}  | {70}
  us-east2  | {2,3,4,5,8,9,11}  | {70}
  us-west2  | {2,3,4,5,8,9,11}  | {70}
  eu-west2  | {2,3,4,5,8,9,11}  | {70}
  eu-west3  | {2,3,4,5,8,9,11}  | {70}
  eu-north1 | {2,3,4,5,8,9,11}  | {70}
  us-east1  | {1,2,3,4,5}       | {71,85,83,73}
  us-east2  | {1,2,3,4,5}       | {71,85,83,73}
  us-west2  | {1,2,3,4,5}       | {71,85,83,73}
  us-east1  | {1,2,3,5,8,10,11} | {72}
  us-east2  | {1,2,3,5,8,10,11} | {72}
  us-west2  | {1,2,3,5,8,10,11} | {72}
  eu-west2  | {1,2,3,5,8,10,11} | {72}
  eu-west3  | {1,2,3,5,8,10,11} | {72}
  eu-north1 | {1,2,3,5,8,10,11} | {72}
  us-east1  | {1,2,3,5,7,10,12} | {74}
  us-east2  | {1,2,3,5,7,10,12} | {74}
  us-west2  | {1,2,3,5,7,10,12} | {74}
  eu-west2  | {1,2,3,5,7,10,12} | {74}
  eu-west3  | {1,2,3,5,7,10,12} | {74}
  eu-north1 | {1,2,3,5,7,10,12} | {74}
  us-east1  | {1,2,3,5,6}       | {75,87}
  us-east2  | {1,2,3,5,6}       | {75,87}
  us-west2  | {1,2,3,5,6}       | {75,87}
  us-east1  | {2,3,5,7,9,10,11} | {76}
  us-east2  | {2,3,5,7,9,10,11} | {76}
  us-west2  | {2,3,5,7,9,10,11} | {76}
  eu-west2  | {2,3,5,7,9,10,11} | {76}
  eu-west3  | {2,3,5,7,9,10,11} | {76}
  eu-north1 | {2,3,5,7,9,10,11} | {76}
  us-east1  | {2,3,5,6,7,9,11}  | {78,84}
  us-east2  | {2,3,5,6,7,9,11}  | {78,84}
  us-west2  | {2,3,5,6,7,9,11}  | {78,84}
  eu-west2  | {2,3,5,6,7,9,11}  | {78,84}
  eu-west3  | {2,3,5,6,7,9,11}  | {78,84}
  eu-north1 | {2,3,5,6,7,9,11}  | {78,84}
  us-east1  | {1,2,3,6,8,10,11} | {80}
  us-east2  | {1,2,3,6,8,10,11} | {80}
  us-west2  | {1,2,3,6,8,10,11} | {80}
  eu-west2  | {1,2,3,6,8,10,11} | {80}
  eu-west3  | {1,2,3,6,8,10,11} | {80}
  eu-north1 | {1,2,3,6,8,10,11} | {80}
  us-east1  | {2,3,4,5,7,9,11}  | {82}
  us-east2  | {2,3,4,5,7,9,11}  | {82}
  us-west2  | {2,3,4,5,7,9,11}  | {82}
  eu-west2  | {2,3,4,5,7,9,11}  | {82}
  eu-west3  | {2,3,4,5,7,9,11}  | {82}
  eu-north1 | {2,3,4,5,7,9,11}  | {82}
  us-east1  | {1,2,3,5,8,10,12} | {86}
  us-east2  | {1,2,3,5,8,10,12} | {86}
  us-west2  | {1,2,3,5,8,10,12} | {86}
  eu-west2  | {1,2,3,5,8,10,12} | {86}
  eu-west3  | {1,2,3,5,8,10,12} | {86}
  eu-north1 | {1,2,3,5,8,10,12} | {86}
  us-east1  | {2,3,5,6,7,10,12} | {88}
  us-east2  | {2,3,5,6,7,10,12} | {88}
  us-west2  | {2,3,5,6,7,10,12} | {88}
  eu-west2  | {2,3,5,6,7,10,12} | {88}
  eu-west3  | {2,3,5,6,7,10,12} | {88}
  eu-north1 | {2,3,5,6,7,10,12} | {88}

Show replicas and ranges by region (one row):

WITH
  replicas AS (
    SELECT DISTINCT
      split_part(unnest(replica_localities), ',', 1) replica_localities,
      replicas
    FROM [SHOW RANGE FROM TABLE product FOR ROW ('us-east1', 'b6f2327f-80c3-44ce-9c3c-d0c04736c90f')]
  ),
  ranges AS (
    SELECT
      replicas,
      range_id
    FROM [SHOW RANGE FROM TABLE product FOR ROW ('us-east1', 'b6f2327f-80c3-44ce-9c3c-d0c04736c90f')]
  )
SELECT
  split_part(re.replica_localities, '=', 2) region,
  re.replicas,
  array_agg(ra.range_id) range_ids
FROM replicas re
JOIN ranges ra ON re.replicas = ra.replicas
GROUP BY re.replica_localities, re.replicas
ORDER BY range_ids;
   region  |  replicas   | range_ids
-----------+-------------+------------
  us-east1 | {1,2,3,4,5} | {71}
  us-east2 | {1,2,3,4,5} | {71}
  us-west2 | {1,2,3,4,5} | {71}

Show replicas and ranges by region (another row):

WITH
  replicas AS (
    SELECT DISTINCT
      split_part(unnest(replica_localities), ',', 1) replica_localities,
      replicas
    FROM [SHOW RANGE FROM TABLE product FOR ROW ('eu-west3', '2f22da46-d983-4878-8ad2-a6e6ff7e8f39')]
  ),
  ranges AS (
    SELECT
      replicas,
      range_id
    FROM [SHOW RANGE FROM TABLE product FOR ROW ('eu-west3', '2f22da46-d983-4878-8ad2-a6e6ff7e8f39')]
  )
SELECT
  split_part(re.replica_localities, '=', 2) region,
  re.replicas,
  array_agg(ra.range_id) range_ids
FROM replicas re
JOIN ranges ra ON re.replicas = ra.replicas
GROUP BY re.replica_localities, re.replicas
ORDER BY range_ids;
   region   |    replicas    | range_ids
------------+----------------+------------
  eu-west2  | {7,9,10,11,12} | {69}
  eu-west3  | {7,9,10,11,12} | {69}
  eu-north1 | {7,9,10,11,12} | {69}

Expected behavior
I would expect the query to show replicas and ranges by region (all) to show no overlap of replicas between regions.

Environment:

Build Tag:        v23.1.4
Build Time:       2023/06/16 21:03:22
Distribution:     CCL
Platform:         darwin arm64 (aarch64-apple-darwin21.2)
Go Version:       go1.19.4
C Compiler:       Clang 10.0.0 
Build Commit ID:  cf85e8ad48bb7a8afd821863d4f345baafa4a1b6
Build Type:       release
Enabled Assertions: false

Jira issue: CRDB-29416

Metadata

Metadata

Assignees

No one assigned

    Labels

    C-bugCode not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.T-kvKV Team

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions