sql: support automatically increment partitions for PARTITION BY ALL table

I thought of this when prototyping DROP PARTITION, which is considered a faster bulk deletion alternative to TTL. One idea is to have PARTITION BY ALL as a prerequisite to use DROP PARTITION, which means the key columns of the partition-to-drop is the prefix columns of all the secondary index of the table. This means the rows to drop belong to contiguous key span across all secondary indexes, so we can just tombstone these spans rather than doing point lookups. This is similar to how RBR table works with PARTITION BY ALL keying on the `crdb_region` column.

There're some restrictions with our current PARTITION ALL BY feature though:
- We only support setting PARTITION ALL BY when CREATE TABLE
- The bound of each partition has to be defined explicitly with constant ranges / values.

e.g.

```sql
CREATE TABLE public.events (
created_at TIMESTAMP NOT NULL,
bucket DATE NOT NULL AS (date_trunc('month':::STRING, created_at)::DATE) STORED,
k INT8 NOT NULL,
pad BYTES NOT NULL,
CONSTRAINT events_pkey PRIMARY KEY (bucket ASC, k ASC)
) PARTITION ALL BY LIST (bucket) (
PARTITION p_2026_06 VALUES IN (('2026-06-01')),
PARTITION p_2026_07 VALUES IN (('2026-07-01')),
PARTITION p_2026_08 VALUES IN (('2026-08-01'))
) WITH (schema_locked = true)
```

So these restrictions means:
1. When new rows being inserted with `created_at = now()` which is after `2026-08-01`, they won't be assigned to a partition, so there's no way to leverage drop partition to bulk delete them.
2. If the user would like to change the delete frequency from monthly to weekly, there's no easy way to do it.

Some high-level ideas that might work as solutions:
1. Have the partition creation automatically triggered when new distinct value is added for the key column(s) (such as `bucket` in this example). Maybe we can get some inspiration from how auto stats works. We will need to have a cap for the number of partitions supported. or
2. Pre-allocate partitions. If the frequency is deterministic (say monthly, weekly or daily), the new partitions can be pre-populated before the new rows are actually inserted. Maybe need some async gc to clean up empty partitions.
3. For changing the frequency, maybe a new computed column is required, and replace the PARTITION ALL BY key column. This is expected to be slow and disruptive, as it's essentially issuing ALTER TABLE ALTER INDEX across all the secondary indexes of the table. For reference, PG recently [merged](https://github.com/postgres/postgres/commit/4b3d173629f4cd7ab6cd700d1053af5d5c7c9e37) their support for `ALTER TABLE SPLIT PARTITION`.

Might be related: https://github.com/cockroachdb/cockroach/issues/58736

Start by reading issue #173875 and the related issue #58736, then compare the three proposed approaches for automatically creating or pre-allocating PARTITION ALL BY partitions. Done requires an agreed design that addresses new key values, partition-count limits, and changes in partition frequency; no implementation files or tests are named in the issue.

Written by the indexing model from the issue text.