sql: SHOW PARTITIONS FOR <table> or <index> or <database>

[awoods187]

Table
It is not documented but users can run:

Select * from crdb_internal.partitions 
join crdb_internal.tables on partitions.table_id=tables.table_id 
where tables.name='mytable'

Database
It is not documented but users can run:

Select partitions.name from crdb_internal.partitions 
join crdb_internal.tables on partitions.table_id=tables.table_id 
where database_name='mydb

We should make SHOW statements that allow for users to see this information easily.

[awoods187]

We should also make sure that SHOW CREATE TABLE shows all partitions on tables and indexes

[awoods187]

So to clarify, we do add partitions to the SHOW CREATE TABLE:

CREATE TABLE "orders" (
  region STRING NOT NULL,
  id UUID DEFAULT gen_random_uuid() NOT NULL,
  total DECIMAL NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (region, id),
  UNIQUE INDEX orders_by_created_at (region, created_at, id) STORING (total),
  CHECK (region IN ('us-east1', 'us-west1', 'europe-west2'))
)
PARTITION BY LIST (region) (
  PARTITION us_east1 VALUES IN ('us-east1'),
  PARTITION us_west1 VALUES IN ('us-west1'),
  PARTITION europe_west2 VALUES IN ('europe-west2')
);

And then:

root@localhost:26257/defaultdb> show create table orders;
  table_name |                                                 create_statement
+------------+-------------------------------------------------------------------------------------------------------------------+
  orders     | CREATE TABLE orders (
             |     region STRING NOT NULL,
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     total DECIMAL NOT NULL,
             |     created_at TIMESTAMP NOT NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (region ASC, id ASC),
             |     UNIQUE INDEX orders_by_created_at (region ASC, created_at ASC, id ASC) STORING (total),
             |     FAMILY "primary" (region, id, total, created_at),
             |     CONSTRAINT check_region CHECK (region IN ('us-east1':::STRING, 'us-west1':::STRING, 'europe-west2':::STRING))
             | ) PARTITION BY LIST (region) (
             |     PARTITION us_east1 VALUES IN (('us-east1')),
             |     PARTITION us_west1 VALUES IN (('us-west1')),
             |     PARTITION europe_west2 VALUES IN (('europe-west2'))
             | )
(1 row)

Time: 5.989ms

But, if we add the zone constraints:

alter partition us_east1 OF TABLE orders configure zone using constraints='[+region=us-east1]';
CONFIGURE ZONE 1

Those will not show up:

root@localhost:26257/defaultdb> show create table orders;
  table_name |                                                 create_statement
+------------+-------------------------------------------------------------------------------------------------------------------+
  orders     | CREATE TABLE orders (
             |     region STRING NOT NULL,
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     total DECIMAL NOT NULL,
             |     created_at TIMESTAMP NOT NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (region ASC, id ASC),
             |     UNIQUE INDEX orders_by_created_at (region ASC, created_at ASC, id ASC) STORING (total),
             |     FAMILY "primary" (region, id, total, created_at),
             |     CONSTRAINT check_region CHECK (region IN ('us-east1':::STRING, 'us-west1':::STRING, 'europe-west2':::STRING))
             | ) PARTITION BY LIST (region) (
             |     PARTITION us_east1 VALUES IN (('us-east1')),
             |     PARTITION us_west1 VALUES IN (('us-west1')),
             |     PARTITION europe_west2 VALUES IN (('europe-west2'))
             | )
(1 row)

Time: 9.294ms

Show create table should show the zone constraints that apply to the table.

[rohany]

@awoods187 what kind of output are you expecting for the show create table to output here?

[awoods187]

For Movr, first we partition the users table:

ALTER TABLE users PARTITION BY LIST (city) ( PARTITION new_york VALUES IN ('new york'), PARTITION chicago VALUES IN ('chicago'), PARTITION seattle VALUES IN ('seattle') );

Then we add constraints:

ALTER PARTITION new_york OF TABLE movr.users CONFIGURE ZONE USING constraints='[+region=us-east1]'; ALTER PARTITION chicago OF TABLE movr.users CONFIGURE ZONE USING constraints='[+region=us-central1]'; ALTER PARTITION seattle OF TABLE movr.users CONFIGURE ZONE USING constraints='[+region=us-west1]';`) 

SHOW CREATE TABLE users currently displays:

root@localhost:26257/movr> show create table users;
  table_name |                      create_statement
+------------+-------------------------------------------------------------+
  users      | CREATE TABLE users (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     name VARCHAR NULL,
             |     address VARCHAR NULL,
             |     credit_card VARCHAR NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     FAMILY "primary" (id, city, name, address, credit_card)
             | ) PARTITION BY LIST (city) (
             |     PARTITION new_york VALUES IN (('new york')),
             |     PARTITION chicago VALUES IN (('chicago')),
             |     PARTITION seattle VALUES IN (('seattle'))
             | )
(1 row)

Time: 239.957759ms

Post adding in constraints I'm imagining:

root@localhost:26257/movr> show create table users;
  table_name |                      create_statement
+------------+-------------------------------------------------------------+
  users      | CREATE TABLE users (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     name VARCHAR NULL,
             |     address VARCHAR NULL,
             |     credit_card VARCHAR NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     FAMILY "primary" (id, city, name, address, credit_card)
             | ) PARTITION BY LIST (city) (
             |     PARTITION new_york VALUES IN (('new york')),
             |     PARTITION chicago VALUES IN (('chicago')),
             |     PARTITION seattle VALUES IN (('seattle'))
             | )
(1 row)

Time: 239.957759ms

With some in-line comments that show constraints.

Note, I also just realized that if you run SHOW CONSTRAINTS FROM users after adding constraints you will not see these either:

root@localhost:26257/movr> show constraints from users;
  table_name | constraint_name | constraint_type |            details             | validated
+------------+-----------------+-----------------+--------------------------------+-----------+
  users      | primary         | PRIMARY KEY     | PRIMARY KEY (city ASC, id ASC) |   true
(1 row)

Time: 239.074341ms

Splitting this off into #39076 and #39075