Skip to content

Latest commit

 

History

History
151 lines (107 loc) · 5.85 KB

set-locality.md

File metadata and controls

151 lines (107 loc) · 5.85 KB
Raw
title summary toc
SET LOCALITY
The SET LOCALITY statement changes the locality of a table.
true

New in v21.1: The ALTER TABLE .. SET LOCALITY statement changes the table locality of a table in a multi-region database.

{{site.data.alerts.callout_info}} SET LOCALITY is a subcommand of ALTER TABLE {{site.data.alerts.end}}

Synopsis

{% include {{ page.version.version }}/sql/generated/diagrams/alter_table_locality.html %}

Parameters

| Parameter | Description | |--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | table_name | The table whose locality you are configuring. | | locality | The locality to apply to this table. Allowed values:

|

{{site.data.alerts.callout_info}} For more information about which table locality is right for your use case, see the following pages:

  • [Multi-region table localities](multiregion-overview.html#table-locality)
{{site.data.alerts.end}}

Required privileges

The user must be a member of the admin or owner roles, or have the CREATE privilege on the table.

Examples

Set the table locality to REGIONAL BY TABLE

To optimize read and write access to the data in a table from the primary region, use the following statement:

{% include copy-clipboard.html %}

ALTER TABLE {table} SET LOCALITY REGIONAL BY TABLE IN PRIMARY REGION;

To optimize read and write access to the data in a table from the us-east-1 region, use the following statement:

{% include copy-clipboard.html %}

ALTER TABLE {table} SET LOCALITY REGIONAL BY TABLE IN "us-east-1";

{{site.data.alerts.callout_info}} If no region is supplied, REGIONAL BY TABLE defaults to the primary region. {{site.data.alerts.end}}

For more information about how table localities work, see Regional tables.

Set the table locality to REGIONAL BY ROW

To make an existing table a regional by row table, use the following statement:

{% include copy-clipboard.html %}

ALTER TABLE {table} SET LOCALITY REGIONAL BY ROW;

Every row in a regional by row table has a hidden crdb_region column that represents the row's home region. To see a row's region, issue a statement like the following:

{% include copy-clipboard.html %}

SELECT crdb_region, id FROM {table};

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

{% include copy-clipboard.html %}

UPDATE {table} SET crdb_region = "eu-west" WHERE id IN (...)

To add a new row to a regional by row table, you must choose one of the following options.

  • Let CockroachDB set the row's home region automatically. It will use the region of the gateway node from which the row is inserted.

  • Set the home region explicitly using an INSERT statement like the following:

    {% include copy-clipboard.html %}

    INSERT INTO {table} (crdb_region, ...) VALUES ('us-east-1', ...);

This is necessary because every row in a regional by row table must have a home region.

For more information about how this table locality works, see Regional by row tables.

Note that you can use a name other than crdb_region for the hidden column by using the following statements:

{% include copy-clipboard.html %}

ALTER TABLE foo SET LOCALITY REGIONAL BY ROW AS bar;
SELECT bar, id FROM foo;
INSERT INTO foo (bar, ...) VALUES ('us-east-1', ...);

In fact, you can specify any column definition you like for the REGIONAL BY ROW AS column, as long as the column is of type crdb_internal_region. For example, you could modify the movr schema to have a region column generated as:

{% include copy-clipboard.html %}

ALTER TABLE rides ADD COLUMN region crdb_internal_region AS (
  CASE WHEN city = 'amsterdam' THEN 'europe-west1'
       WHEN city = 'paris' THEN 'europe-west1'
       WHEN city = 'rome' THEN 'europe-west1'
       WHEN city = 'new york' THEN 'us-east1'
       WHEN city = 'boston' THEN 'us-east1'
       WHEN city = 'washington dc' THEN 'us-east1'
       WHEN city = 'san francisco' THEN 'us-west1'
       WHEN city = 'seattle' THEN 'us-east1'
       WHEN city = 'los angeles' THEN 'us-east1'
  END
) STORED

Set the table locality to GLOBAL

To optimize read access to the data in a table from any region (that is, globally), use the following statement:

{% include copy-clipboard.html %}

ALTER TABLE {table} SET LOCALITY GLOBAL;
ALTER TABLE SET LOCALITY

For more information about how this table locality works, see Global tables.

See also