sql: consider un-namespacing crdb_internal.table_indexes

[matthewtodd]

Describe the problem

The crdb_internal.tables virtual table lists all tables in all databases, but crdb_internal.table_indexes only lists indexes in the current database. I found this context-dependence surprising when writing queries to join these 2 tables for #61507.

To Reproduce

cockroach demo

See tables from all databases (here, system and movr) in crdb_internal.tables:

demo@127.0.0.1:26257/movr> select database_name, name from crdb_internal.tables where schema_name = 'public';
  database_name |              name
----------------+----------------------------------
  system        | namespace
  system        | descriptor
  system        | users
  system        | zones
  system        | settings
  system        | tenants
  system        | lease
  system        | eventlog
  system        | rangelog
  system        | ui
  system        | jobs
  system        | web_sessions
  system        | table_statistics
  system        | locations
  system        | role_members
  system        | comments
  system        | replication_constraint_stats
  system        | replication_critical_localities
  system        | replication_stats
  system        | reports_meta
  system        | namespace2
  system        | protected_ts_meta
  system        | protected_ts_records
  system        | role_options
  system        | statement_bundle_chunks
  system        | statement_diagnostics_requests
  system        | statement_diagnostics
  system        | scheduled_jobs
  system        | sqlliveness
  system        | migrations
  movr          | users
  movr          | vehicles
  movr          | rides
  movr          | vehicle_location_histories
  movr          | promo_codes
  movr          | user_promo_codes
(36 rows)

Time: 4ms total (execution 4ms / network 0ms)

See only indexes from the current database (first system, then movr) in crdb_internal.table_indexes:

demo@127.0.0.1:26257/movr> use system;
SET

Time: 0ms total (execution 0ms / network 0ms)

demo@127.0.0.1:26257/system> select descriptor_name, index_name from crdb_internal.table_indexes order by descriptor_name, index_id;
          descriptor_name         |               index_name
----------------------------------+-----------------------------------------
  comments                        | primary
  descriptor                      | primary
  eventlog                        | primary
  jobs                            | primary
  jobs                            | jobs_status_created_idx
  jobs                            | jobs_created_by_type_created_by_id_idx
  lease                           | primary
  locations                       | primary
  migrations                      | primary
  namespace                       | primary
  namespace2                      | primary
  protected_ts_meta               | primary
  protected_ts_records            | primary
  rangelog                        | primary
  replication_constraint_stats    | primary
  replication_critical_localities | primary
  replication_stats               | primary
  reports_meta                    | primary
  role_members                    | primary
  role_members                    | role_members_role_idx
  role_members                    | role_members_member_idx
  role_options                    | primary
  scheduled_jobs                  | primary
  scheduled_jobs                  | next_run_idx
  settings                        | primary
  sqlliveness                     | primary
  statement_bundle_chunks         | primary
  statement_diagnostics           | primary
  statement_diagnostics_requests  | primary
  statement_diagnostics_requests  | completed_idx
  table_statistics                | primary
  tenants                         | primary
  ui                              | primary
  users                           | primary
  web_sessions                    | primary
  web_sessions                    | web_sessions_expiresAt_idx
  web_sessions                    | web_sessions_createdAt_idx
  zones                           | primary
(38 rows)

Time: 4ms total (execution 4ms / network 0ms)

demo@127.0.0.1:26257/system> use movr;
SET

Time: 0ms total (execution 0ms / network 0ms)

demo@127.0.0.1:26257/movr> select descriptor_name, index_name from crdb_internal.table_indexes order by descriptor_name, index_id;
       descriptor_name       |                  index_name
-----------------------------+------------------------------------------------
  promo_codes                | primary
  rides                      | primary
  rides                      | rides_auto_index_fk_city_ref_users
  rides                      | rides_auto_index_fk_vehicle_city_ref_vehicles
  user_promo_codes           | primary
  users                      | primary
  vehicle_location_histories | primary
  vehicles                   | primary
  vehicles                   | vehicles_auto_index_fk_city_ref_users
(9 rows)

Time: 3ms total (execution 3ms / network 0ms)

Expected behavior

Given the stable contents of crdb_internal.tables, I expected crdb_internal.table_indexes to also be stable, that is, to not change based on the current database, but to include all indexes for all tables. Perhaps there are reasons we'd like to retain the current behavior? But I wanted to at least mention it here.

Jira issue: CRDB-7992

[ajwerner]

tl;dr check out "".crdb_internal.*

All of these "system catalogs" are namespaced for Postgres compatibility. Postgres doesn't know anything about stuff in other databases. Cockroach is in a somewhat different place because it tried to not be Postgres for a while and it originally just had a two level namespace. This turns out to have been a bad idea because compatibility is useful. Nevertheless, accessing global catalog information is also handy. Cockroach has a piece of somewhat bizarre syntax for this: the "" catalog. There are reasons why this thing might be regrettable, but it's what we've got.

[ajwerner]

Oh, upon reading again, I think it's a mistake that tables is not namespaced. We're an inconsistent mess.

[ajwerner]

At this point the reason to retain the behavior is backwards compat (😞). I'll do some spelunking to see if I can understand whether there was any rationale on this decision.

[matthewtodd]

Thanks, @ajwerner, "".crdb_internal.* will meet my current needs, and I suspect you're right about tables.

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!