Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: Add SQL command to view existing interleaved and cross database reference usage #58867

Closed
vy-ton opened this issue Jan 12, 2021 · 5 comments · Fixed by #61629
Closed

sql: Add SQL command to view existing interleaved and cross database reference usage #58867

vy-ton opened this issue Jan 12, 2021 · 5 comments · Fixed by #61629
Assignees
@fqazi
Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)

Comments

@vy-ton
Copy link
Contributor

vy-ton commented Jan 12, 2021

Before interleaved tables/indexes and cross-database references are no longer supported, users should be able to query for existing usage from SQL in 21.1. This can be a new SHOW command
@vy-ton vy-ton added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Jan 12, 2021
@vy-ton
Copy link
Contributor Author

vy-ton commented Feb 2, 2021 •
edited
Loading

@jordanlewis @lucy-zhang Can we get this for 21.1?

With the repeat questions on interleaved deprecation, having some introspection capability seems more necessary. It also allows users to do a precondition check before upgrading to 21.2

@vy-ton
Copy link
Contributor Author

vy-ton commented Feb 4, 2021

Thinking more, I think a crdb_internal table makes more sense than a SHOW command.

@thoszhang
Copy link
Contributor

How do you see this table being used? Any thoughts on what its schema should be and how structured the data should be? I know we've talked about this being displayed in the DB console, but is the output primarily meant to be read by human operators or processed in some automated way?

If it's meant to be read by human operators, on one end of the spectrum I could imagine having a crdb_internal table for deprecation warnings in general, where we just display textual error messages and maybe have a column for a list of descriptor IDs. If we have more structure in the table schema, we'll end up with new virtual tables that are extremely specific to this one deprecation cycle, but maybe that's fine.

@vy-ton
Copy link
Contributor Author

vy-ton commented Feb 9, 2021

We won't have DB console integration for 21.1, so the output should be human-readable.

I could imagine having a crdb_internal table for deprecation warnings in general

This could make sense since we have multiple deprecations. On the other hand, these feature removals have a deadline, so a specific table schema that we eventually remove is where I'm leaning. Also might allow us to display richer info specific to each feature being deprecated (Ex. for cross-database refs the referenced db not just the descriptor ID)

@vy-ton
Copy link
Contributor Author

vy-ton commented Feb 25, 2021

For interleaved tables, crdb_internal table should include

  • schema_name
  • table_name
  • parent_table

For interleave indexes, crdb_internal table should include

  • table_name
  • index_name
  • parent_index

For cross-database references, crdb_internal table should include

  • object_name - qualified name with database and schema
  • referenced_object_name - qualified name with database and schema
  • cross_database_reference_description

@jordanlewis jordanlewis assigned ajwerner and fqazi and unassigned ajwerner Mar 2, 2021
@ajwerner ajwerner mentioned this issue Mar 5, 2021
Closed
@fqazi fqazi mentioned this issue Mar 8, 2021
Merged
fqazi added a commit to fqazi/cockroach that referenced this issue Mar 9, 2021
@fqazi
sql: internal tables for cross db refs and interleaved indexes/tables
3d6a1f2 
Fixes: cockroachdb#58867

Previously, users had no way of determining which objects in
their database utilized either deprecated features like
interleaved indexes/tables or cross DB references. This was
inadequate since users need to know which object utilize
this functionality. To address this, this patch introduces
the crdb_internal tables: cross_db_references, interleaved_indexes,
interleaved_tables.

Release justification: Low risk internal tables for detecting
deprecated features.
Release note (sql change): Added crdb_internal tables cross_db_references,
interleaved_indexes, and interleaved_tables for detecting the
deprecated features cross db references and interleaved tables
/ indexes within a given database.
fqazi added a commit to fqazi/cockroach that referenced this issue Mar 9, 2021
@fqazi
sql: internal tables for cross db refs and interleaved indexes/tables
da064d2 
Fixes: cockroachdb#58867

Previously, users had no way of determining which objects in
their database utilized either deprecated features like
interleaved indexes/tables or cross DB references. This was
inadequate since users need to know which object utilize
this functionality. To address this, this patch introduces
the crdb_internal tables: cross_db_references, interleaved_indexes,
interleaved_tables.

Release justification: Low risk internal tables for detecting
deprecated features.
Release note (sql change): Added crdb_internal tables cross_db_references,
interleaved_indexes, and interleaved_tables for detecting the
deprecated features cross db references and interleaved tables
/ indexes within a given database.
fqazi added a commit to fqazi/cockroach that referenced this issue Mar 11, 2021
@fqazi
sql: internal tables for cross db refs and interleaved indexes/tables
cdbaed1 
Fixes: cockroachdb#58867

Previously, users had no way of determining which objects in
their database utilized either deprecated features like
interleaved indexes/tables or cross DB references. This was
inadequate since users need to know which object utilize
this functionality. To address this, this patch introduces
the crdb_internal tables: cross_db_references, interleaved_indexes,
interleaved_tables.

Release justification: Low risk internal tables for detecting
deprecated features.
Release note (sql change): Added crdb_internal tables cross_db_references,
interleaved_indexes, and interleaved_tables for detecting the
deprecated features cross db references and interleaved tables
/ indexes within a given database.
fqazi added a commit to fqazi/cockroach that referenced this issue Mar 11, 2021
@fqazi
sql: internal tables for cross db refs and interleaved indexes/tables
a1fc264 
Fixes: cockroachdb#58867

Previously, users had no way of determining which objects in
their database utilized either deprecated features like
interleaved indexes/tables or cross DB references. This was
inadequate since users need to know which object utilize
this functionality. To address this, this patch introduces
the crdb_internal tables: cross_db_references, interleaved,

Release justification: Low risk internal tables for detecting
deprecated features.
Release note (sql change): Added crdb_internal tables cross_db_references
and interleaved for detecting the deprecated features cross db
references and interleaved tables / indexes.
fqazi added a commit to fqazi/cockroach that referenced this issue Mar 11, 2021
@fqazi
sql: internal tables for cross db refs and interleaved indexes/tables
cd1a7bb 
Fixes: cockroachdb#58867

Previously, users had no way of determining which objects in
their database utilized either deprecated features like
interleaved indexes/tables or cross DB references. This was
inadequate since users need to know which object utilize
this functionality. To address this, this patch introduces
the crdb_internal tables: cross_db_references, interleaved,

Release justification: Low risk internal tables for detecting
deprecated features.
Release note (sql change): Added crdb_internal tables cross_db_references
and interleaved for detecting the deprecated features cross db
references and interleaved tables / indexes.
craig bot pushed a commit that referenced this issue Mar 12, 2021
@fqazi
Merge #61629
377add4 
61629: sql: add internal tables cross db refs and interleaved indexes/tables r=fqazi a=fqazi

Fixes: #58867

Previously, users had no way of determining which objects in
their database utilized either deprecated features like
interleaved indexes/tables or cross DB references. This was
inadequate since users need to know which object utilize
this functionality. To address this, this patch introduces
the crdb_internal tables: cross_db_references, interleaved_indexes,
interleaved_tables.

Release justification: Low risk internal tables for detecting
deprecated features.
Release note (sql change): Added crdb_internal tables cross_db_references,
interleaved_indexes, and interleaved_tables for detecting the
deprecated features cross db references and interleaved tables
/ indexes within a given database.

Co-authored-by: Faizan Qazi <[email protected]>
@craig craig bot closed this as completed in 52e45e0 Mar 12, 2021
@vy-ton vy-ton mentioned this issue May 14, 2021
Closed
fqazi added a commit to fqazi/cockroach that referenced this issue Nov 1, 2021
@fqazi
sql: internal tables for cross db refs and interleaved indexes/tables
ec92632 
Fixes: cockroachdb#58867

Previously, users had no way of determining which objects in
their database utilized either deprecated features like
interleaved indexes/tables or cross DB references. This was
inadequate since users need to know which object utilize
this functionality. To address this, this patch introduces
the crdb_internal tables: cross_db_references, interleaved,

Release justification: Low risk internal tables for detecting
deprecated features.
Release note (sql change): Added crdb_internal tables cross_db_references
and interleaved for detecting the deprecated features cross db
references and interleaved tables / indexes.
@fqazi fqazi mentioned this issue Nov 1, 2021
Merged
fqazi added a commit to fqazi/cockroach that referenced this issue Nov 1, 2021
@fqazi
sql: internal tables for cross db refs and interleaved indexes/tables
864b266 
Fixes: cockroachdb#58867

Previously, users had no way of determining which objects in
their database utilized either deprecated features like
interleaved indexes/tables or cross DB references. This was
inadequate since users need to know which object utilize
this functionality. To address this, this patch introduces
the crdb_internal tables: cross_db_references, interleaved,

Release justification: Low risk internal tables for detecting
deprecated features.
Release note (sql change): Added crdb_internal tables cross_db_references
and interleaved for detecting the deprecated features cross db
references and interleaved tables / indexes.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@fqazi fqazi

Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: add internal tables cross db refs and interleaved indexes/tables fqazi/cockroach
4 participants
@fqazi @ajwerner @thoszhang @vy-ton