sql: hide zone configuration from SHOW CREATE TABLE for multi-region tables

[otan]

At present, RBR tables should zone configuration:

[email protected]:26257/movr> SHOW CREATE TABLE rides;
  table_name |                                                                      create_statement
-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
  rides      | CREATE TABLE public.rides (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     vehicle_city VARCHAR NULL,
             |     rider_id UUID NULL,
             |     vehicle_id UUID NULL,
             |     start_address VARCHAR NULL,
             |     end_address VARCHAR NULL,
             |     start_time TIMESTAMP NULL,
             |     end_time TIMESTAMP NULL,
             |     revenue DECIMAL(10,2) NULL,
             |     crdb_region public.crdb_internal_region NOT VISIBLE NOT NULL DEFAULT default_to_database_primary_region(gateway_region())::public.crdb_internal_region,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     CONSTRAINT fk_city_ref_users FOREIGN KEY (city, rider_id) REFERENCES public.users(city, id),
             |     CONSTRAINT fk_vehicle_city_ref_vehicles FOREIGN KEY (vehicle_city, vehicle_id) REFERENCES public.vehicles(city, id),
             |     INDEX rides_auto_index_fk_city_ref_users (city ASC, rider_id ASC),
             |     INDEX rides_auto_index_fk_vehicle_city_ref_vehicles (vehicle_city ASC, vehicle_id ASC),
             |     FAMILY "primary" (id, city, vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time, revenue, crdb_region),
             |     CONSTRAINT check_vehicle_city_city CHECK (vehicle_city = city)
             | ) LOCALITY REGIONAL BY ROW;
             | ALTER PARTITION "europe-west1" OF INDEX movr.public.rides@primary CONFIGURE ZONE USING
             |     num_voters = 3,
             |     voter_constraints = '[+region=europe-west1]',
             |     lease_preferences = '[[+region=europe-west1]]';
             | ALTER PARTITION "europe-west1" OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users CONFIGURE ZONE USING
             |     num_voters = 3,
             |     voter_constraints = '[+region=europe-west1]',
             |     lease_preferences = '[[+region=europe-west1]]';
             | ALTER PARTITION "europe-west1" OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles CONFIGURE ZONE USING
             |     num_voters = 3,
             |     voter_constraints = '[+region=europe-west1]',
             |     lease_preferences = '[[+region=europe-west1]]';
             | ALTER PARTITION "us-east1" OF INDEX movr.public.rides@primary CONFIGURE ZONE USING
             |     num_voters = 3,
             |     voter_constraints = '[+region=us-east1]',
             |     lease_preferences = '[[+region=us-east1]]';
             | ALTER PARTITION "us-east1" OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users CONFIGURE ZONE USING
             |     num_voters = 3,
             |     voter_constraints = '[+region=us-east1]',
             |     lease_preferences = '[[+region=us-east1]]';
             | ALTER PARTITION "us-east1" OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles CONFIGURE ZONE USING
             |     num_voters = 3,
             |     voter_constraints = '[+region=us-east1]',
             |     lease_preferences = '[[+region=us-east1]]';
             | ALTER PARTITION "us-west1" OF INDEX movr.public.rides@primary CONFIGURE ZONE USING
             |     num_voters = 3,
             |     voter_constraints = '[+region=us-west1]',
             |     lease_preferences = '[[+region=us-west1]]';
             | ALTER PARTITION "us-west1" OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users CONFIGURE ZONE USING
             |     num_voters = 3,
             |     voter_constraints = '[+region=us-west1]',
             |     lease_preferences = '[[+region=us-west1]]';
             | ALTER PARTITION "us-west1" OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles CONFIGURE ZONE USING
             |     num_voters = 3,
             |     voter_constraints = '[+region=us-west1]',
             |     lease_preferences = '[[+region=us-west1]]'

We can choose to hide these, but then we need some way of nicely surfacing zone configuration data.

A user can manually inspect these by looking at SHOW ZONE CONFIGURATION FOR PARTITION x OF t@y but that does not handle recursiveness.

The current idea is SHOW ZONE CONFIGURATION ..., but that only shows the "inherited" config, and nothing recursively down: #56969.

[otan]

i have a prototype https://github.com/cockroachdb/cockroach/compare/master...otan-cockroach:alter_zone_display?expand=1 which deals with showing partition are correct in tests by SELECTing from crdb_internal.zones

[awoods187]

Can you post a screenshot of your proposal?

[awoods187]

I'm also into a SHOW CREATE TABLE VERBOSE that shows partitions/zone configurations as well as showing different output for multi-region databases.

[otan]

i'm not really for a SHOW CREATE TABLE VERBOSE showing these, as it would be showing the partitions when they are not necessary. i'm more in favour of a separate command that shows zone configs clearly...