sql: make partition names index-scoped, not table-scoped

[benesch]

Users are likely to be confused by the fact that partition names cannot be reused across indexes of the same table.

> CREATE TABLE t ... PARTITION BY (a) (
  PARTITION p1 VALUES IN (...)
)

> CREATE INDEX ON t (b) PARTITION BY (
  PARTITION p1 VALUES IN (...)
)
error name already in use

This was somewhat solved with a better error message, which tells you which other index is already using the name.

Still, the only reason that partition names are table scoped is because there's no good way to refer to an index-scoped name from the CLI. Right now,

./cockroach zone set a.b.c

unambiguously means database "a", table "b", and partition "c". If partitions become index-scoped, you'll be required to instead say:

./cockroach zone set a.b@index.c

This raises an annoying question: is a.b@primary.c different from a.b.c? Should a.b@index.c inherit from a.b.c?

Some of these questions will be solved when we ditch the zone CLI for the SQL zone config interface, so holding off until that interface stabilizes.

[rohany]

Since we have a way of manipulating partitions now in SQL, are there other things that depend on partitions having duplicate names across different indexes?

[solongordon]

I noticed that the ALTER PARTITION ... OF TABLE currently allows you to modify secondary index partitions as well. We'll probably need to remove that functionality and require users to use ALTER PARTITION ... OF INDEX instead.

Example:

root@:26257/defaultdb> CREATE TABLE t (
    x INT PRIMARY KEY,
    y INT,
    INDEX t_y_idx (y ASC) PARTITION BY LIST (y) (
        PARTITION a_idx VALUES IN (1)
    )
) PARTITION BY LIST (x) (
    PARTITION a VALUES IN (1)
);
CREATE TABLE

Time: 3.806ms

root@:26257/defaultdb> ALTER PARTITION a_idx OF TABLE t CONFIGURE ZONE USING constraints='[+region=us-east1]';
CONFIGURE ZONE 1

Time: 5.536ms

Once partition names aren't unique, users will instead need to run

root@:26257/defaultdb> ALTER PARTITION a_idx OF INDEX t@t_y_idx CONFIGURE ZONE USING constraints='[+region=us-east1]';
CONFIGURE ZONE 1

Time: 5.549ms

[solongordon]

I also expect we'll want to remove the deprecated cockroach zone commands to avoid dealing with the ambiguities discussed in the original issue.

[solongordon]

Fixed by #39332.