rfc: information_schema by nvb · Pull Request #7965 · cockroachdb/cockroach

nvb · 2016-07-21T18:05:02Z

Support the information_schema specification.

Rendered preview: https://github.com/nvanbenschoten/cockroach/blob/nvanbenschoten/isRFC/docs/RFCS/information_schema.md

This change is

tbg · 2016-07-21T21:32:02Z

docs/RFCS/information_schema.md

+and as long as they add concrete value. An example here would be a column in the
+`INFORMATION_SCHEMA.TABLES` table about column families. What other columns or tables would be
+valuable CockroachDB-extensions to the `information_schema`?
+


The content of the table could also not be up-to-date (i.e. it could omit a table that was created but hasn't been discovered by the local node yet).

Could it? The information_schema.tables table will be populated with a scan similar to

cockroach/sql/table.go

Line 304 in 3dbb3cb

// getTableNames implements the SchemaAccessor interface.

, which should avoid issues like that.

tbg · 2016-07-21T21:33:48Z

Sounds reasonable to me. Thanks for including all the details on how other DBs actually implement that table - TIL.

Reviewed 1 of 1 files at r1.
Review status: all files reviewed at latest revision, 1 unresolved discussion, some commit checks failed.

Comments from Reviewable

petermattis · 2016-07-21T21:40:27Z

Review status: all files reviewed at latest revision, 2 unresolved discussions, some commit checks failed.

docs/RFCS/information_schema.md, line 200 [r1] (raw file):

and as long as they add concrete value. An example here would be a column in the
`INFORMATION_SCHEMA.TABLES` table about column families. What other columns or tables would be
valuable CockroachDB-extensions to the `information_schema`?

Interleaved tables? STORING?

Comments from Reviewable

tbg · 2016-07-21T21:44:45Z

Review status: all files reviewed at latest revision, 2 unresolved discussions, some commit checks failed.

docs/RFCS/information_schema.md, line 200 [r1] (raw file):

Previously, petermattis (Peter Mattis) wrote…

Interleaved tables? STORING?

Column families?

Comments from Reviewable

bdarnell · 2016-07-23T20:58:10Z

Review status: all files reviewed at latest revision, 4 unresolved discussions, some commit checks failed.

docs/RFCS/information_schema.md, line 26 [r1] (raw file):

[documentation on the topic.](http://dev.mysql.com/doc/refman/5.7/en/information-schema.html#idm140648692050944)

More importantly, in order to perform schema reflection, a large majority of ORMs

Do you have a list of which ORMs use information_schema and which ones do not? It would be good to save that research somewhere so that we can gauge the importance of implementing the pg_* tables.

docs/RFCS/information_schema.md, line 143 [r1] (raw file):

follow MySQL's lead and turn `SHOW` statements into a wrapper around `information_schema`
queries. This will help eliminate duplicate implementations and simplify SHOW statement
handling significantly.

Should we consider removing/deprecating the SHOW commands completely, or are the information_schema queries verbose enough that the SHOW commands are useful shorthand?

Comments from Reviewable

tbg · 2016-07-23T22:51:02Z

#7974 should be able to use information_schema. I assume information_schema would magically only return whatever the user is allowed to see, right? These permissions issues deserve a little bit of comment.

tbg · 2016-07-23T23:10:38Z

Review status: all files reviewed at latest revision, 4 unresolved discussions, some commit checks failed.

docs/RFCS/information_schema.md, line 201 [r1] (raw file):

Previously, nvanbenschoten (Nathan VanBenschoten) wrote…

Could it? The information_schema.tables table will be populated with a scan similar to

cockroach/sql/table.go

Line 304 in 3dbb3cb

// getTableNames implements the SchemaAccessor interface.

, which should avoid issues like that.

You're right. Same for databases, right (i.e. information_schema will never rely on gossipped info)?

Comments from Reviewable

nvb · 2016-07-23T23:23:43Z

Added a section on privileges to address @tschottdorf's comment. I'm a little shocked that SHOW statements don't already do this, but I guess multi-tenancy hasn't been a major focus yet.

Review status: 0 of 1 files reviewed at latest revision, 4 unresolved discussions, some commit checks pending.

docs/RFCS/information_schema.md, line 26 [r1] (raw file):

Previously, bdarnell (Ben Darnell) wrote…

Do you have a list of which ORMs use information_schema and which ones do not? It would be good to save that research somewhere so that we can gauge the importance of implementing the pg_* tables.

https://docs.google.com/spreadsheets/d/17A0EflPqI9yhargK0n4tSw2WogQuVc5YeK-VFmKvXHM presents a pretty good overview of respective usages of meta tables in ORMs.

docs/RFCS/information_schema.md, line 143 [r1] (raw file):

Previously, bdarnell (Ben Darnell) wrote…

Should we consider removing/deprecating the SHOW commands completely, or are the information_schema queries verbose enough that the SHOW commands are useful shorthand?

I'm tempted to say that we should maintain support for `SHOW` statements, because MySQL maintains both approaches and because they are more straightforward to use from a SQL shell than a verbose query on `information_schema`. Are there any good reasons to deprecate them, especially since they should simply map to IS queries?

docs/RFCS/information_schema.md, line 200 [r1] (raw file):

Previously, tschottdorf (Tobias Schottdorf) wrote…

Column families?

Done.

docs/RFCS/information_schema.md, line 201 [r1] (raw file):

Previously, tschottdorf (Tobias Schottdorf) wrote…

You're right. Same for databases, right (i.e. information_schema will never rely on gossipped info)?

Correct, nothing that I can think of should rely on gossipped info because by definition it would imply inconsistency across nodes. I don't think any introspective information about a specific node deserves to be present in the `information_schema`, because it would break the illusion of a consistently persisted, real table.

Comments from Reviewable

tbg · 2016-07-24T00:14:48Z

Reviewed 1 of 1 files at r2.
Review status: all files reviewed at latest revision, 4 unresolved discussions, some commit checks failed.

Comments from Reviewable

tbg · 2016-07-24T00:16:58Z

Review status: all files reviewed at latest revision, 3 unresolved discussions, some commit checks failed.

docs/RFCS/information_schema.md, line 143 [r1] (raw file):

Previously, nvanbenschoten (Nathan VanBenschoten) wrote…

I'm tempted to say that we should maintain support for SHOW statements, because MySQL maintains both approaches and because they are more straightforward to use from a SQL shell than a verbose query on information_schema. Are there any good reasons to deprecate them, especially since they should simply map to IS queries?

We're trying to be Postgres-flavor, so wouldn't we really want to support `\l`, ... etc? No strong opinion on my part, just curious.

Comments from Reviewable

petermattis · 2016-07-24T01:31:20Z

Review status: all files reviewed at latest revision, 4 unresolved discussions, some commit checks failed.

docs/RFCS/information_schema.md, line 143 [r1] (raw file):

Previously, tschottdorf (Tobias Schottdorf) wrote…

We're trying to be Postgres-flavor, so wouldn't we really want to support \l, ... etc? No strong opinion on my part, just curious.

I'd like to be careful about us "trying to be a Postgres-flavor". I prefer to think we're targeting standard SQL and looking to Postgres as the best guide from the open source world. But where the standard is silent (e.g. extensions for a SQL shell), we should take note of Postgres but not be bound by it.

Comments from Reviewable

Support the `information_schema` specification.

nvb force-pushed the nvanbenschoten/isRFC branch from d37402a to 6b50712 Compare July 21, 2016 18:05

tbg reviewed Jul 21, 2016
View reviewed changes

tbg mentioned this pull request Jul 23, 2016

cli: allow dump to be used by any user with SELECT #7974

Merged

nvb force-pushed the nvanbenschoten/isRFC branch from 6b50712 to 73af988 Compare July 23, 2016 23:11

madelynnblue mentioned this pull request Jul 25, 2016

sql: use information_schema in dump #8009

Closed

nvb force-pushed the nvanbenschoten/isRFC branch from 73af988 to 65fbbf7 Compare July 26, 2016 13:35

This was referenced Jul 26, 2016

sql: Move remaining handling of DatabaseDescriptors into DatabaseAccessor #8037

Merged

sql: Introduce VirtualSchema infrastructure #8119

Merged

nvb added the docs-todo label Aug 1, 2016

nvb force-pushed the nvanbenschoten/isRFC branch from 65fbbf7 to 319071f Compare August 2, 2016 18:05

rfc: information_schema

f4bc2f6

Support the `information_schema` specification.

nvb force-pushed the nvanbenschoten/isRFC branch from 319071f to f4bc2f6 Compare August 2, 2016 18:34

nvb merged commit bbf106e into cockroachdb:master Aug 2, 2016

nvb deleted the nvanbenschoten/isRFC branch August 2, 2016 19:34

nvb mentioned this pull request Aug 4, 2016

sql: Add information_schema.columns table #8327

Merged

jseldess mentioned this pull request Aug 16, 2016

sql: Information Schemas cockroachdb/docs#549

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

rfc: information_schema#7965

rfc: information_schema#7965
nvb merged 1 commit intocockroachdb:masterfrom
nvb:nvanbenschoten/isRFC

nvb commented Jul 21, 2016 •

edited by petermattis

Loading

Uh oh!

tbg Jul 21, 2016

Uh oh!

nvb Jul 23, 2016

Uh oh!

tbg commented Jul 21, 2016

Uh oh!

petermattis commented Jul 21, 2016

Uh oh!

tbg commented Jul 21, 2016

Uh oh!

bdarnell commented Jul 23, 2016

Uh oh!

tbg commented Jul 23, 2016

Uh oh!

tbg commented Jul 23, 2016

Uh oh!

nvb commented Jul 23, 2016

Uh oh!

tbg commented Jul 24, 2016

Uh oh!

tbg commented Jul 24, 2016

Uh oh!

petermattis commented Jul 24, 2016

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Conversation

nvb commented Jul 21, 2016 • edited by petermattis Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

tbg Jul 21, 2016

Choose a reason for hiding this comment

Uh oh!

nvb Jul 23, 2016

Choose a reason for hiding this comment

Uh oh!

tbg commented Jul 21, 2016

Uh oh!

petermattis commented Jul 21, 2016

Uh oh!

tbg commented Jul 21, 2016

Uh oh!

bdarnell commented Jul 23, 2016

Uh oh!

tbg commented Jul 23, 2016

Uh oh!

tbg commented Jul 23, 2016

Uh oh!

nvb commented Jul 23, 2016

Uh oh!

tbg commented Jul 24, 2016

Uh oh!

tbg commented Jul 24, 2016

Uh oh!

petermattis commented Jul 24, 2016

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

nvb commented Jul 21, 2016 •

edited by petermattis

Loading