sql: add ALTER TABLE/INDEX .. UNSPLIT AT ..

[jseldess]

PR: cockroachdb/cockroach#37603

From release notes:

Added the ALTER TABLE/INDEX ... UNSPLIT AT ... statement. [#37603][#37603] {% comment %}doc{% endcomment %}

• Update ALTER TABLE page
• Update ALTER INDEX page
• Add UNSPLIT AT page (similar to SPLIT AT page)
• Add UNSPLIT AT new diagram
• Add UNSPLIT AT examples

[ericharmeling]

@nvanbenschoten or @ajwerner (you were both reviewers on cockroachdb/cockroach#37603, and I assume @jeffrey-xiao is back in school)

Question:
UNSPLIT is meant to revert a manual SPLIT, correct? i.e. When I do a SPLIT, and then do a subsequent and identical UNSPLIT, CHECK RANGES should return the same output as it would before the initial SPLIT, correct?

e.g. (Using the movr database)

> SHOW RANGES FROM TABLE users;

start_key | end_key | range_id | replicas | lease_holder | locality
+-----------+---------+----------+----------+--------------+----------+
NULL      | NULL    |       21 | {1}      |            1 |
(1 row)

Then I split the users table ranges based on primary key values:

> ALTER TABLE users SPLIT AT VALUES ('chicago'), ('new york'), ('seattle');

              key              |         pretty         |       split_enforced_until
+------------------------------+------------------------+----------------------------------+
  \275\211\022chicago\000\001  | /Table/53/1/"chicago"  | 2262-04-11 23:47:16.854776+00:00
  \275\211\022new york\000\001 | /Table/53/1/"new york" | 2262-04-11 23:47:16.854776+00:00
  \275\211\022seattle\000\001  | /Table/53/1/"seattle"  | 2262-04-11 23:47:16.854776+00:00
(3 rows)

> SHOW RANGES FROM TABLE users;

   start_key  |   end_key   | range_id | replicas | lease_holder | locality
+-------------+-------------+----------+----------+--------------+----------+
  NULL        | /"chicago"  |       21 | {1}      |            1 |
  /"chicago"  | /"new york" |       27 | {1}      |            1 |
  /"new york" | /"seattle"  |       28 | {1}      |            1 |
  /"seattle"  | NULL        |       29 | {1}      |            1 |
(4 rows)

Now I unsplit the table:

> ALTER TABLE users UNSPLIT AT VALUES ('chicago'), ('new york'), ('seattle');

key              |         pretty
+------------------------------+------------------------+
\275\211\022chicago\000\001  | /Table/53/1/"chicago"
\275\211\022new york\000\001 | /Table/53/1/"new york"
\275\211\022seattle\000\001  | /Table/53/1/"seattle"
(3 rows)

> SHOW RANGES FROM TABLE users;

   start_key  |   end_key   | range_id | replicas | lease_holder | locality
+-------------+-------------+----------+----------+--------------+----------+
  NULL        | /"chicago"  |       21 | {1}      |            1 |
  /"chicago"  | /"new york" |       27 | {1}      |            1 |
  /"new york" | /"seattle"  |       28 | {1}      |            1 |
  /"seattle"  | NULL        |       29 | {1}      |            1 |
(4 rows)

This output is identical to the output after the SPLIT, without the UNSPLIT...

This is expected output:

start_key | end_key | range_id | replicas | lease_holder | locality
+-----------+---------+----------+----------+--------------+----------+
NULL      | NULL    |       21 | {1}      |            1 |
(1 row)

I think I am missing something here...

[nvanbenschoten]

UNSPLIT at doesn't manually merge ranges. It just allows the range to be merged in the future if the system decides it wants to merge the range.

Instead of running SHOW RANGES, take a look at SELECT * FROM crdb_internal.ranges. That includes a split_enforced_until column that shows off this state.

[ericharmeling]

After #2957 is completed, we can reference that doc from here.

[ericharmeling]

@nvanbenschoten Thanks!

A couple more questions that you might have an opinion on, if not an explicit answer to:

• Is crdb_internal.ranges the only place where this state is shown?
• How do we want to refer to "this state"? I'm calling it a "split enforcement" right now (e.g. "UNSPLIT AT removes a manual split enforcement at the key-value layer range."... but is there another term for the official state of a table/index re: enforced splits?
• Do we want to talk about "sticky bits"? (i.e. "Manually splitting ranges adds a sticky bit to the range descriptor. When you unsplit a range, this sticky bit is reset/removed, enabling the system to merge ranges.").

[nvanbenschoten]

Is crdb_internal.ranges the only place where this state is shown?

It's also included in crdb_internal.ranges_no_leases.

How do we want to refer to "this state"? I'm calling it a "split enforcement" right now (e.g. "UNSPLIT AT removes a manual split enforcement at the key-value layer range."... but is there another term for the official state of a table/index re: enforced splits?

Split enforcement sounds good to me.

Do we want to talk about "sticky bits"? (i.e. "Manually splitting ranges adds a sticky bit to the range descriptor. When you unsplit a range, this sticky bit is reset/removed, enabling the system to merge ranges.").

I wouldn't. That sounds mostly like an implementation detail to me. Split enforcement should be enough terminology.

[ericharmeling]

Awesome. Thank you!