sql: facilitate applying zone configs to same-named partitions

[solongordon]

Once #20880 is closed, it will be possible to use the same name for related partitions on a table and its indexes. Then it would be great to make it easy for users to apply a zone config to all partitions with the same name in a single statement. We could make this the default behavior for ALTER PARTITION north_america OF TABLE .... Or we could make the syntax more explicit, like ALTER ALL PARTITIONS north_america OF TABLE ....

[awoods187]

cc @andreimatei bc he was interested

[andreimatei]

I think a key thing here is to somehow tie together all the "north_america" partitions of different tables and have all of them point to a single "zone config". As it stands, zone configs are defined by a start and end key, so there's no such thing as different tables or partitions pointing to the same zone config. But there should be.
One has to be able to edit the constraints/attributes of all north_america partitions at once, and code that deals with zone (e.g. reporting) should be able to report only one zone for all the north_americas.

[awoods187]

@solongordon so with #39750 this now applies to all indexes. Where we thinking this could apply to the entire table + indexes in this issue with another pr? or is that now a separate issue?

[solongordon]

The terminology is slightly confusing but "all indexes" includes the primary index, so this already applies to the entire table plus indexes.

I didn't close this issue because this doesn't address @andreimatei's concerns about how to tie together analogous partitions across multiple tables, but that is a broader issue so perhaps it could use a fresh issue.

[awoods187]

Ah I see. I was able to do that via:

ALTER TABLE rides PARTITION BY LIST (city) ( PARTITION new_york VALUES IN ('new york'), PARTITION chicago VALUES IN ('chicago'), PARTITION seattle VALUES IN ('seattle') );
ALTER INDEX rides_auto_index_fk_city_ref_users PARTITION BY LIST (city) ( PARTITION new_york VALUES IN ('new york'), PARTITION chicago VALUES IN ('chicago'), PARTITION seattle VALUES IN ('seattle') );
ALTER INDEX rides_auto_index_fk_vehicle_city_ref_vehicles PARTITION BY LIST (vehicle_city) ( PARTITION new_york VALUES IN ('new york'), PARTITION chicago VALUES IN ('chicago'), PARTITION seattle VALUES IN ('seattle') );

Then:

show partitions from table rides;

  database_name | table_name | partition_name | parent_partition | column_names |                     index_name                      | partition_value | zone_config
+---------------+------------+----------------+------------------+--------------+-----------------------------------------------------+-----------------+-------------+
  movr          | rides      | new_york       | NULL             | city         | rides@primary                                       | ('new york')    | NULL
  movr          | rides      | chicago        | NULL             | city         | rides@primary                                       | ('chicago')     | NULL
  movr          | rides      | seattle        | NULL             | city         | rides@primary                                       | ('seattle')     | NULL
  movr          | rides      | new_york       | NULL             | city         | rides@rides_auto_index_fk_city_ref_users            | ('new york')    | NULL
  movr          | rides      | chicago        | NULL             | city         | rides@rides_auto_index_fk_city_ref_users            | ('chicago')     | NULL
  movr          | rides      | seattle        | NULL             | city         | rides@rides_auto_index_fk_city_ref_users            | ('seattle')     | NULL
  movr          | rides      | new_york       | NULL             | vehicle_city | rides@rides_auto_index_fk_vehicle_city_ref_vehicles | ('new york')    | NULL
  movr          | rides      | chicago        | NULL             | vehicle_city | rides@rides_auto_index_fk_vehicle_city_ref_vehicles | ('chicago')     | NULL
  movr          | rides      | seattle        | NULL             | vehicle_city | rides@rides_auto_index_fk_vehicle_city_ref_vehicles | ('seattle')     | NULL
(9 rows)

Using the new command:

ALTER PARTITION new_york OF INDEX movr.rides@* CONFIGURE ZONE USING constraints='[+region=us-east,+az=1]'; ALTER PARTITION chicago OF INDEX rides@* CONFIGURE ZONE USING constraints='[+region=us-central,+az=1]'; ALTER PARTITION seattle OF INDEX movr.rides@* CONFIGURE ZONE USING constraints='[+region=us-west,+az=2]';

And finally we can see that it works:

show partitions from table rides;
  database_name | table_name | partition_name | parent_partition | column_names |                     index_name                      | partition_value |                 zone_config
+---------------+------------+----------------+------------------+--------------+-----------------------------------------------------+-----------------+---------------------------------------------+
  movr          | rides      | new_york       | NULL             | city         | rides@primary                                       | ('new york')    | constraints = '[+region=us-east, +az=1]'
  movr          | rides      | chicago        | NULL             | city         | rides@primary                                       | ('chicago')     | constraints = '[+region=us-central, +az=1]'
  movr          | rides      | seattle        | NULL             | city         | rides@primary                                       | ('seattle')     | constraints = '[+region=us-west, +az=2]'
  movr          | rides      | new_york       | NULL             | city         | rides@rides_auto_index_fk_city_ref_users            | ('new york')    | constraints = '[+region=us-east, +az=1]'
  movr          | rides      | chicago        | NULL             | city         | rides@rides_auto_index_fk_city_ref_users            | ('chicago')     | constraints = '[+region=us-central, +az=1]'
  movr          | rides      | seattle        | NULL             | city         | rides@rides_auto_index_fk_city_ref_users            | ('seattle')     | constraints = '[+region=us-west, +az=2]'
  movr          | rides      | new_york       | NULL             | vehicle_city | rides@rides_auto_index_fk_vehicle_city_ref_vehicles | ('new york')    | constraints = '[+region=us-east, +az=1]'
  movr          | rides      | chicago        | NULL             | vehicle_city | rides@rides_auto_index_fk_vehicle_city_ref_vehicles | ('chicago')     | constraints = '[+region=us-central, +az=1]'
  movr          | rides      | seattle        | NULL             | vehicle_city | rides@rides_auto_index_fk_vehicle_city_ref_vehicles | ('seattle')     | constraints = '[+region=us-west, +az=2]'
(9 rows)

Time: 13.992ms

I think we should close this issue as addressed and open a new one on @andreimatei 's idea. Does that work for you?

[solongordon]

Created #40703 to track @andreimatei's suggestion.