sql: add proper support for hidden columns#26644
Closed
knz wants to merge 1 commit intocockroachdb:masterfrom
Closed
sql: add proper support for hidden columns#26644knz wants to merge 1 commit intocockroachdb:masterfrom
knz wants to merge 1 commit intocockroachdb:masterfrom
Conversation
Prior to this patch, CockroachDB already had full support for marking arbitrary columns as "hidden" internally: a hidden column can be used for queries but does not get automatically selected by a `*` in a SELECT clause, and does not get automatically inserted/upserted into when INSERT/UPSERT do not list any columns. However, no control was given to users to determine which columns would be hidden. The only column that could be hidden in practice was the implicit `rowid` column created when CREATE does not specify a primary key. This patch complements the existing thorough support for hidden column by properly exposing the "hidden" attribute through SQL: - any column can now be marked as hidden in CREATE using the column attribute `NOT VISIBLE`. - the attribute can now be changed with `ALTER TABLE .. ALTER COLUMN .. SET [NOT] VISIBLE`. This change is further motivated by the need to annotate non-PK columns in `pg_catalog` vtables, for compatibility with PostgreSQL (this will be actually done in a later patch). The output of `SHOW CREATE` now displays all columns with their optional `NOT VISIBLE` attribute, instead of skipping over hidden column as previously. Note: the choice for the syntax "NOT VISIBLE" as opposed to "HIDDEN" is mandated by the fact that the first word of an attribute must be a reserved keyword, and adding reserved keywords is disruptive. This way, the patch is properly backward-compatible. Release note (sql change): clients can now set the visibility of a column using the `NOT VISIBLE` attribute in `CREATE TABLE` or `SET VISIBLE` / `SET NOT VISIBLE` in `ALTER TABLE .. ALTER COLUMN`.
Member
|
This change is |
Member
|
This is cool, but I don't understand why it's necessary to add this feature to support pg_am and so on. Is there really no way to support those tables without introducing a major new schema feature that we'll necessarily have to support forever? Review status: Comments from Reviewable |
complete! 0 of 0 LGTMs obtained
Contributor
Author
There is a way (implement proper PKs in virtual tables) but it's expensive.
The feature was already there. It was so thoroughly there that we have the proper check |
Contributor
Author
|
Two more things.
- the hidden column in these vtables must be called "oid", not "rowid". Some other tables have a hidden column with a different name. So in any case we must provide a mechanism to specify the name of the hidden column(s).
- the previous output of SHOW CREATE was listing columns (rowid) in constraints, family definitions and interleave specs that were not clearly defined (there was no column def for them). It makes the schema harder to understand for a human, and it makes it easy to overlook that there's an extra column with a non trivial default. This patch corrects that.
…--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
|
6 tasks
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
3 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
(Needed to implement
pg_amandpg_opclassfor #26504.)Prior to this patch, CockroachDB already had full support for marking
arbitrary columns as "hidden" internally: a hidden column can be used
for queries but does not get automatically selected by a
*in aSELECT clause, and does not get automatically inserted/upserted into
when INSERT/UPSERT do not list any columns.
However, no control was given to users to determine which columns
would be hidden. The only column that could be hidden in practice was
the implicit
rowidcolumn created when CREATE does not specify aprimary key.
This patch complements the existing thorough support for hidden column
by properly exposing the "hidden" attribute through SQL:
attribute
NOT VISIBLE.ALTER TABLE .. ALTER COLUMN .. SET [NOT] VISIBLE.This change is further motivated by the need to annotate non-PK
columns in
pg_catalogvtables, for compatibility withPostgreSQL (this will be actually done in a later patch).
The output of
SHOW CREATEnow displays all columns with theiroptional
NOT VISIBLEattribute, instead of skipping over hiddencolumn as previously.
Note: the choice for the syntax "NOT VISIBLE" as opposed to "HIDDEN"
is mandated by the fact that the first word of an attribute must be a
reserved keyword, and adding reserved keywords is disruptive. This
way, the patch is properly backward-compatible.
Release note (sql change): clients can now set the visibility of a
column using the
NOT VISIBLEattribute inCREATE TABLEorSET VISIBLE/SET NOT VISIBLEinALTER TABLE .. ALTER COLUMN.