regional_by_row

# LogicTest: multiregion-9node-3region-3azs

statement ok
CREATE DATABASE multi_region_test_db PRIMARY REGION "ca-central-1" REGIONS "ap-southeast-2", "us-east-1" SURVIVE REGION FAILURE

statement error REGIONAL BY ROW is currently experimental
CREATE TABLE regional_by_row_table (pk int) LOCALITY REGIONAL BY ROW

statement ok
SET experimental_enable_implicit_column_partitioning = true

statement error cannot set LOCALITY on a database that is not multi-region enabled
CREATE TABLE regional_by_row_table (pk int) LOCALITY REGIONAL BY ROW

statement ok
USE multi_region_test_db

statement error REGIONAL BY ROW on a TABLE containing PARTITION BY is not supported
CREATE TABLE regional_by_row_table (
  pk int
)
PARTITION BY LIST (pk) (PARTITION one VALUES IN ((1)))
LOCALITY REGIONAL BY ROW

statement error REGIONAL BY ROW AS is not yet supported
CREATE TABLE regional_by_row_table_on_col (a int) LOCALITY REGIONAL BY ROW AS a

statement error REGIONAL BY ROW on a table with an INDEX containing PARTITION BY is not supported
CREATE TABLE regional_by_row_table (
  pk int,
  a int,
  INDEX (a) PARTITION BY LIST (a) (PARTITION one VALUES IN ((1)))
)
LOCALITY REGIONAL BY ROW

statement error REGIONAL BY ROW on a table with an UNIQUE constraint containing PARTITION BY is not supported
CREATE TABLE regional_by_row_table (
  pk int,
  a int,
  UNIQUE (a) PARTITION BY LIST (a) (PARTITION one VALUES IN ((1)))
)
LOCALITY REGIONAL BY ROW

statement ok
CREATE TABLE regional_by_row_table (
  pk int PRIMARY KEY,
  a int,
  b int,
  INDEX (a),
  UNIQUE (b),
  FAMILY (pk, a, b)
) LOCALITY REGIONAL BY ROW

# TODO(#multiregion): determine how we should display the presence of a crdb_region column
# to the user.
# TODO(#multiregion): ensure this CREATE TABLE statement round trips.
query T
SELECT create_statement FROM [SHOW CREATE TABLE regional_by_row_table]
----
CREATE TABLE public.regional_by_row_table (
  pk INT8 NOT NULL,
  a INT8 NULL,
  b INT8 NULL,
  crdb_region public.crdb_internal_region NOT NULL DEFAULT gateway_region()::public.crdb_internal_region,
  CONSTRAINT "primary" PRIMARY KEY (pk ASC),
  INDEX regional_by_row_table_a_idx (a ASC),
  UNIQUE INDEX regional_by_row_table_b_key (b ASC),
  FAMILY fam_0_pk_a_b_crdb_region (pk, a, b, crdb_region)
) LOCALITY REGIONAL BY ROW;
ALTER PARTITION "ap-southeast-2" OF INDEX multi_region_test_db.public.regional_by_row_table@regional_by_row_table_a_idx CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=ap-southeast-2: 1}',
  lease_preferences = '[[+region=ap-southeast-2]]';
ALTER PARTITION "ap-southeast-2" OF INDEX multi_region_test_db.public.regional_by_row_table@regional_by_row_table_b_key CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=ap-southeast-2: 1}',
  lease_preferences = '[[+region=ap-southeast-2]]';
ALTER PARTITION "ap-southeast-2" OF INDEX multi_region_test_db.public.regional_by_row_table@primary CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=ap-southeast-2: 1}',
  lease_preferences = '[[+region=ap-southeast-2]]';
ALTER PARTITION "ca-central-1" OF INDEX multi_region_test_db.public.regional_by_row_table@regional_by_row_table_a_idx CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=ca-central-1: 1}',
  lease_preferences = '[[+region=ca-central-1]]';
ALTER PARTITION "ca-central-1" OF INDEX multi_region_test_db.public.regional_by_row_table@regional_by_row_table_b_key CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=ca-central-1: 1}',
  lease_preferences = '[[+region=ca-central-1]]';
ALTER PARTITION "ca-central-1" OF INDEX multi_region_test_db.public.regional_by_row_table@primary CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=ca-central-1: 1}',
  lease_preferences = '[[+region=ca-central-1]]';
ALTER PARTITION "us-east-1" OF INDEX multi_region_test_db.public.regional_by_row_table@regional_by_row_table_a_idx CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=us-east-1: 1}',
  lease_preferences = '[[+region=us-east-1]]';
ALTER PARTITION "us-east-1" OF INDEX multi_region_test_db.public.regional_by_row_table@regional_by_row_table_b_key CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=us-east-1: 1}',
  lease_preferences = '[[+region=us-east-1]]';
ALTER PARTITION "us-east-1" OF INDEX multi_region_test_db.public.regional_by_row_table@primary CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=us-east-1: 1}',
  lease_preferences = '[[+region=us-east-1]]'

query TTB colnames
SELECT index_name, column_name, implicit FROM crdb_internal.index_columns
WHERE descriptor_name = 'regional_by_row_table' AND column_type = 'key'
ORDER BY 1, 2
----
index_name                   column_name  implicit
primary                      crdb_region  true
primary                      pk           false
regional_by_row_table_a_idx  a            false
regional_by_row_table_a_idx  crdb_region  true
regional_by_row_table_b_key  b            false
regional_by_row_table_b_key  crdb_region  true

query TTTTIT colnames
SHOW TABLES
----
schema_name  table_name             type   owner  estimated_row_count  locality
public       regional_by_row_table  table  root   0                    REGIONAL BY ROW

query TI
INSERT INTO regional_by_row_table (pk, a, b) VALUES
(1, 2, 3), (4, 5, 6)
RETURNING crdb_region, pk
----
ap-southeast-2  1
ap-southeast-2  4

query TI
INSERT INTO regional_by_row_table (crdb_region, pk, a, b) VALUES
('ca-central-1', 7, 8, 9)
RETURNING crdb_region, pk
----
ca-central-1  7

query TI nodeidx=3
INSERT INTO multi_region_test_db.regional_by_row_table (pk, a, b) VALUES
(10, 11, 12)
RETURNING crdb_region, pk
----
ca-central-1  10

query TI nodeidx=6
INSERT INTO multi_region_test_db.regional_by_row_table (pk, a, b) VALUES
(20, 21, 22)
RETURNING crdb_region, pk
----
us-east-1  20

query TI
INSERT INTO regional_by_row_table (crdb_region, pk, a, b) VALUES
(gateway_region()::crdb_internal_region, 23, 24, 25)
RETURNING crdb_region, pk
----
ap-southeast-2  23

query TIII
SELECT crdb_region, pk, a, b FROM regional_by_row_table
ORDER BY pk
----
ap-southeast-2  1   2   3
ap-southeast-2  4   5   6
ca-central-1    7   8   9
ca-central-1    10  11  12
us-east-1       20  21  22
ap-southeast-2  23  24  25