Example of adding a partition to geo-partitioning examples

[drewdeally]

Andrew Deally (drewdeally) commented:

Currently we do not have an example of adding a partition for new data

here is a working example

    id INT DEFAULT unique_rowid(),
    name STRING,
    email STRING,
    country STRING,
    expected_graduation_date DATE,
    PRIMARY KEY (country, id))
    PARTITION BY LIST (country)
      (PARTITION north_america VALUES IN ('CA','US'),
      PARTITION australia VALUES IN ('AU','NZ'),
      PARTITION DEFAULT VALUES IN (default));
CREATE TABLE

Time: 38.212ms

add a new partition south_america

      PARTITION australia VALUES IN ('AU','NZ'), PARTITION south_america  VALUES IN ('BRA','CHL','COL'),
      PARTITION DEFAULT VALUES IN (default));
ALTER TABLE

Time: 192.816ms

show create table students_by_list;
     table_name    |                             create_statement
-------------------+----------------------------------------------------------------------------
  students_by_list | CREATE TABLE students_by_list (
                   |     id INT8 NOT NULL DEFAULT unique_rowid(),
                   |     name STRING NULL,
                   |     email STRING NULL,
                   |     country STRING NOT NULL,
                   |     expected_graduation_date DATE NULL,
                   |     CONSTRAINT "primary" PRIMARY KEY (country ASC, id ASC),
                   |     FAMILY "primary" (id, name, email, country, expected_graduation_date)
                   | ) PARTITION BY LIST (country) (
                   |     PARTITION north_america VALUES IN (('CA'), ('US')),
                   |     PARTITION australia VALUES IN (('AU'), ('NZ')),
                   |     PARTITION south_america VALUES IN (('BZ'), ('EQ')),
                   |     PARTITION "default" VALUES IN ((DEFAULT))
                   | )
                   | -- Warning: Partitioned table with no zone configurations.
(1 row)

Time: 418.34ms

relocating rows

      PARTITION australia VALUES IN ('AU'), PARTITION south_america  VALUES IN ('BZ','EQ','NZ'),
      PARTITION DEFAULT VALUES IN (default));
ALTER TABLE

Time: 96.998ms

Jira Issue: DOC-470

[jseldess]

@rmloveland, @awoods187, this issue slipped through the cracks for a while. Curious if/how it might be address via 21.1 multi-region docs.

[awoods187]

I think we will cover adding and removing regions and how that affects regional by row tables (that use partitioning) but we wont be explicitly going into this in detail because we don't want people to use partitioning moving forward. I think we should keep this open to potentially add to our reference docs in the future for the people who want to use the primitives and not the abstractions but keep it fairly low priority.

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB docs!