PR #93644 - sql: enhance SHOW RANGES (1 of 6)

[cockroach-teamcity]

Exalate commented:

Related PR: cockroachdb/cockroach#93644
Commit: cockroachdb/cockroach@e67d2fd

---

Release note (backward-incompatible change): CockroachDB now supports
sharing storage ranges across multiple indexes/tables. As a result,
there is no more guarantee that there is at most one SQL object (e.g.
table/index/sequence/materialized view) per storage range.

Therefore, the columns table_id, database_name, schema_name,
table_name and index_name in crdb_internal.ranges and
.ranges_no_leases have become nonsensical: a range cannot be
attributed to a single table/index any more.

As a result:

• The aforementioned columns in the crdb_internal virtual tables
  have been removed. Existing code can use the SHOW RANGES
  statement instead, optionally using WITH KEYS to expose
  the raw start/end keys.

• SHOW RANGES FROM DATABASE continues to report one row per range,
  but stops returning the database / schema / table / index name.

• SHOW RANGES FROM TABLE continues to report one row per range,
  but stops returning the index name.

Suggested replacements:

• Instead of SELECT range_id FROM crdb_internal.ranges WHERE table_name = 'x'

  Use: SELECT range_id FROM [SHOW RANGES FROM TABLE x]

• Instead of SELECT range_id FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2
  (variable / unpredictable table name or ID)

  Use: SELECT range_id FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES] WHERE table_name = $1 OR table_id = $2

• Instead of SELECT start_key FROM crdb_internal.ranges WHERE table_name = 'x'

  Use: SELECT raw_start_key FROM [SHOW RANGES FROM TABLE x WITH KEYS]

• Instead of SELECT start_key FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2
  (unpredictable / variable table name or ID)

  Use: SELECT raw_start_key FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES, KEYS] WHERE table_name = $1 OR table_id = $2

Jira Issue: DOC-6468

[knz]

NB: The new behavior will be opt-in in v23.1, and the old behavior becomes deprecated.
See: cockroachdb/cockroach#98979