8000 release-20.2: sql: remove virtual index on information_schema.tables(table_name) by rafiss · Pull Request #55522 · cockroachdb/cockroach · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

release-20.2: sql: remove virtual index on information_schema.tables(table_name) #55522

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

Merged
merged 1 commit into from
Oct 13, 2020
Merged

release-20.2: sql: remove virtual index on information_schema.tables(table_name) #55522

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
21 changes: 0 additions & 21 deletions pkg/sql/information_schema.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -26,7 +26,6 @@ import (
"github.com/cockroachdb/cockroach/pkg/sql/catalog/catconstants"
"github.com/cockroachdb/cockroach/pkg/sql/catalog/dbdesc"
"github.com/cockroachdb/cockroach/pkg/sql/catalog/descpb"
"github.com/cockroachdb/cockroach/pkg/sql/catalog/resolver"
"github.com/cockroachdb/cockroach/pkg/sql/catalog/tabledesc"
"github.com/cockroachdb/cockroach/pkg/sql/catalog/typedesc"
"github.com/cockroachdb/cockroach/pkg/sql/pgwire/pgcode"
Expand Down Expand Up @@ -1494,26 +1493,6 @@ https://www.postgresql.org/docs/9.5/infoschema-tables.html`,
populate: func(ctx context.Context, p *planner, dbContext *dbdesc.Immutable, addRow func(...tree.Datum) error) error {
return forEachTableDesc(ctx, p, dbContext, virtualMany, addTablesTableRow(addRow))
},
indexes: []virtualIndex{
{
populate: func(ctx context.Context, constraint tree.Datum, p *planner, db *dbdesc.Immutable,
addRow func(...tree.Datum) error) (bool, error) {
// This index is on the TABLE_NAME column.
name := tree.MustBeDString(constraint)
flags := tree.ObjectLookupFlags{}
flags.DesiredTableDescKind = tree.ResolveAnyTableKind
desc, err := resolver.ResolveExistingTableObject(ctx, p, tree.NewUnqualifiedTableName(tree.Name(name)), flags)
if err != nil || desc == nil {
return false, err
}
sc, err := p.Descriptors().ResolveSchemaByID(ctx, p.txn, desc.GetParentSchemaID())
if err != nil {
return false, err
}
return true, addTablesTableRow(addRow)(db, sc.Name, desc)
},
},
},
}

func addTablesTableRow(
Expand Down
45 changes: 27 additions & 18 deletions pkg/sql/logictest/testdata/logic_test/information_schema
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -159,8 +159,7 @@ information_schema.tables CREATE TABLE information_schema.tables (
table_name STRING NOT NULL,
table_type STRING NOT NULL,
is_insertable_into STRING NOT NULL,
version INT8 NULL,
INDEX tables_table_name_idx (table_name ASC) STORING (table_catalog, table_schema, table_type, is_insertable_into, version)
version INT8 NULL
)

query TTBTTTB colnames
Expand Down Expand Up @@ -515,6 +514,16 @@ query T
SET DATABASE = test; SELECT table_name FROM information_schema.tables WHERE table_schema = 'other_db'
----

# Check that tables from other schemas are found
query TT rowsort
CREATE SCHEMA myschema;
CREATE TABLE myschema.abc();
CREATE TABLE abc();
SELECT table_schema, table_name FROM information_schema.tables WHERE table_name = 'abc';
----
myschema abc
public abc

# Check that filtering works.
query T
SELECT table_name FROM other_db.information_schema.tables WHERE table_name > 't' ORDER BY 1 DESC
Expand Down Expand Up @@ -1079,24 +1088,24 @@ SELECT *
FROM information_schema.table_constraints
ORDER BY TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
----
constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name constraint_type is_deferrable initially_deferred
constraint_db public 541687103_59_1_not_null constraint_db public t1 CHECK NO NO
constraint_db public c2 constraint_db public t1 CHECK NO NO
constraint_db public check_a constraint_db public t1 CHECK NO NO
constraint_db public primary constraint_db public t1 PRIMARY KEY NO NO
constraint_db public t1_a_key constraint_db public t1 UNIQUE NO NO
constraint_db public 541687103_60_2_not_null constraint_db public t2 CHECK NO NO
constraint_db public fk constraint_db public t2 FOREIGN KEY NO NO
constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name constraint_type is_deferrable initially_deferred
constraint_db public 3753077756_62_1_not_null constraint_db public t1 CHECK NO NO
constraint_db public c2 constraint_db public t1 CHECK NO NO
constraint_db public check_a constraint_db public t1 CHECK NO NO
constraint_db public primary constraint_db public t1 PRIMARY KEY NO NO
constraint_db public t1_a_key constraint_db public t1 UNIQUE NO NO
constraint_db public 3753077756_63_2_not_null constraint_db public t2 CHECK NO NO
constraint_db public fk constraint_db public t2 FOREIGN KEY NO NO

query TTTT colnames
SELECT *
FROM information_schema.check_constraints
ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_NAME
----
constraint_catalog constraint_schema constraint_name check_clause
constraint_db public 541687103_59_1_not_null p IS NOT NULL
constraint_db public c2 ((a < 99:::INT8))
constraint_db public check_a ((a > 4:::INT8))
constraint_catalog constraint_schema constraint_name check_clause
constraint_db public 3753077756_62_1_not_null p IS NOT NULL
constraint_db public c2 ((a < 99:::INT8))
constraint_db public check_a ((a > 4:::INT8))

query TTTTTTT colnames
SELECT *
Expand All @@ -1120,10 +1129,10 @@ USING (constraint_catalog, constraint_schema, constraint_name)
WHERE tc.table_schema in ('public')
ORDER BY tc.table_schema, tc.table_name, cc.constraint_name
----
table_schema table_name constraint_name check_clause
public t1 541687103_59_1_not_null p IS NOT NULL
public t1 c2 ((a < 99:::INT8))
public t1 check_a ((a > 4:::INT8))
table_schema table_name constraint_name check_clause
public t1 3753077756_62_1_not_null p IS NOT NULL
public t1 c2 ((a < 99:::INT8))
public t1 check_a ((a > 4:::INT8))

statement ok
DROP DATABASE constraint_db CASCADE
Expand Down
11 changes: 6 additions & 5 deletions pkg/sql/opt/exec/execbuilder/testdata/information_schema
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -12,11 +12,12 @@ virtual table · · (catalog_name, schem
query TTT
EXPLAIN SELECT * FROM system.information_schema.tables WHERE table_name='foo'
----
· distribution local
· vectorized false
virtual table · ·
· table tables@tables_table_name_idx
· spans [/'foo' - /'foo']
· distribution local
· vectorized false
filter · ·
│ filter table_name = 'foo'
└── virtual table · ·
· table tables@primary

statement error use of crdb_internal_vtable_pk column not allowed
SELECT crdb_internal_vtable_pk FROM system.information_schema.schemata
Expand Down
29 changes: 16 additions & 13 deletions pkg/sql/opt/exec/execbuilder/testdata/virtual
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -27,23 +27,27 @@ filter · ·
query TTT
EXPLAIN SELECT * FROM information_schema.tables WHERE table_name = 'blah'
----
· distribution local
· vectorized false
virtual table · ·
· table tables@tables_table_name_idx
· spans [/'blah' - /'blah']
· distribution local
· vectorized false
filter · ·
│ filter table_name = 'blah'
└── virtual table · ·
· table tables@primary

# Make sure that if we need an ordering on one of the virtual indexes we
# provide it using a sortNode even though the optimizer expects the virtual
# index to provide it "naturally".
query TTT
EXPLAIN SELECT * FROM information_schema.tables WHERE table_name > 'blah' ORDER BY table_name
----
· distribution local
· vectorized false
virtual table · ·
· table tables@tables_table_name_idx
· spans [/e'blah\x00' - ]
· distribution local
· vectorized false
sort · ·
│ order +table_name
└── filter · ·
│ filter table_name > 'blah'
└── virtual table · ·
· table tables@primary

# Make sure that we properly push down just part of a filter on two columns
# where only one of them is satisfied by the virtual index.
Expand All @@ -53,10 +57,9 @@ EXPLAIN SELECT * FROM information_schema.tables WHERE table_name = 'blah' AND ta
· distribution local
· vectorized false
filter · ·
│ filter table_type = 'foo'
│ filter (table_name = 'blah') AND (table_type = 'foo')
└── virtual table · ·
· table tables@tables_table_name_idx
· spans [/'blah' - /'blah']
· table tables@primary

# Lookup joins into virtual indexes.

Expand Down
3 changes: 1 addition & 2 deletions pkg/sql/vtable/information_schema.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -151,6 +151,5 @@ CREATE TABLE information_schema.tables (
TABLE_NAME STRING NOT NULL,
TABLE_TYPE STRING NOT NULL,
IS_INSERTABLE_INTO STRING NOT NULL,
VERSION INT,
INDEX(TABLE_NAME)
VERSION INT
)`
0