sql: Expand information in SHOW ZONE CONFIGURATION FROM TABLE

[awoods187]

Currently, SHOW ZONE CONFIGURATION FOR TABLE rides does not show all of the indexes and partitions, as well as their zone configurations. This makes it cumbersome to find out this information.

root@127.0.0.1:65332/movr> show zone configuration for table rides;
     target     |              raw_config_sql
----------------+-------------------------------------------
  RANGE default | ALTER RANGE default CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 90000,
                |     num_replicas = 3,
                |     constraints = '[]',
                |     lease_preferences = '[]'
(1 row)
Time: 2ms total (execution 2ms / network 0ms)

However, you can see it for a partition:

root@127.0.0.1:65332/movr> SHOW ZONE CONFIGURATION FOR PARTITION us_west OF INDEX rides@primary;
                   target                  |                           raw_config_sql
-------------------------------------------+----------------------------------------------------------------------
  PARTITION us_west OF INDEX rides@primary | ALTER PARTITION us_west OF INDEX rides@primary CONFIGURE ZONE USING
                                           |     range_min_bytes = 134217728,
                                           |     range_max_bytes = 536870912,
                                           |     gc.ttlseconds = 90000,
                                           |     num_replicas = 3,
                                           |     constraints = '[+region=us-west1]',
                                           |     lease_preferences = '[]'
(1 row)
Time: 4ms total (execution 4ms / network 0ms)

When we look at SHOW CREATE TABLE we can see all of the indexes as well as all of the zone configurations that apply to partitions or indexes.

root@127.0.0.1:65332/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,
             |     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) PARTITION BY LIST (city) (
             |         PARTITION us_west VALUES IN (('seattle'), ('san francisco'), ('los angeles')),
             |         PARTITION us_east VALUES IN (('new york'), ('boston'), ('washington dc')),
             |         PARTITION europe_west VALUES IN (('amsterdam'), ('paris'), ('rome'))
             |     ),
             |     INDEX rides_auto_index_fk_vehicle_city_ref_vehicles (vehicle_city ASC, vehicle_id ASC) PARTITION BY LIST (vehicle_city) (
             |         PARTITION us_west VALUES IN (('seattle'), ('san francisco'), ('los angeles')),
             |         PARTITION us_east VALUES IN (('new york'), ('boston'), ('washington dc')),
             |         PARTITION europe_west VALUES IN (('amsterdam'), ('paris'), ('rome'))
             |     ),
             |     FAMILY "primary" (id, city, vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time, revenue),
             |     CONSTRAINT check_vehicle_city_city CHECK (vehicle_city = city)
             | ) PARTITION BY LIST (city) (
             |     PARTITION us_west VALUES IN (('seattle'), ('san francisco'), ('los angeles')),
             |     PARTITION us_east VALUES IN (('new york'), ('boston'), ('washington dc')),
             |     PARTITION europe_west VALUES IN (('amsterdam'), ('paris'), ('rome'))
             | );
             | ALTER PARTITION us_west OF INDEX movr.public.rides@primary CONFIGURE ZONE USING
             |     constraints = '[+region=us-west1]';
             | ALTER PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users CONFIGURE ZONE USING
             |     constraints = '[+region=us-west1]';
             | ALTER PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles CONFIGURE ZONE USING
             |     constraints = '[+region=us-west1]';
             | ALTER PARTITION us_east OF INDEX movr.public.rides@primary CONFIGURE ZONE USING
             |     constraints = '[+region=us-east1]';
             | ALTER PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users CONFIGURE ZONE USING
             |     constraints = '[+region=us-east1]';
             | ALTER PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles CONFIGURE ZONE USING
             |     constraints = '[+region=us-east1]';
             | ALTER PARTITION europe_west OF INDEX movr.public.rides@primary CONFIGURE ZONE USING
             |     constraints = '[+region=europe-west1]';
             | ALTER PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users CONFIGURE ZONE USING
             |     constraints = '[+region=europe-west1]';
             | ALTER PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles CONFIGURE ZONE USING
             |     constraints = '[+region=europe-west1]'
(1 row)
Time: 19ms total (execution 18ms / network 0ms)

Instead, we should show recursively everything that applies to the table.

Jira issue: CRDB-2886