diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index fd6910ddbe..7089e3a0d2 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1412,6 +1412,18 @@ the column is null. + + + + attishidden bool + + + This column is hidden and not expandable in the resulting target list + of a SELECT * or an INSERT without + destination column list. An hidden column can still be used, but it + must be explicitly referenced. + + diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 94f745aed0..b4d3e84fe3 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -377,6 +377,164 @@ CREATE TABLE people ( + + Hidden Columns + + + hidden column + + + + An hidden column is just like a normal column except that it is not available + unless it is explicitly referenced. Columns with the HIDDEN + attribute implicitly hide these columns such that SELECT * FROM table doesn't + return a value for the columns. The same behavior applies to the COPY ... TO + statements when no columns are specified. + In order to be used, hidden columns must be explicitly included in the query. + Hidden column can always be referenced explicitly wherever a column name can + be specified, for example in a JOIN, a GROUP BY + or an ORDER BY clause. + + + When inserting data into a table, an INSERT statement without a target column + list does not expect values for any hidden columns. In such cases, if the + input includes a value for a hidden column, that value does not have a target + column and an error is returned. Because an INSERT statement without a + column list does not include values for hidden columns, any columns that are + defined as hidden and NOT NULL must have a default value. + The same behavior with hidden column applies to the COPY ... FROM + statements when no destination columns are specified. + + + To create an hidden column, use the HIDDEN clause + in CREATE TABLE, for example: + +CREATE TABLE employee ( + versionid bigint HIDDEN, + empno integer, + firstname text, + ... +); + + + + + The ALTER TABLE statement can be used to change hidden columns to not + hidden and the opposite. + +ALTER TABLE people ALTER COLUMN rowid DROP HIDDEN; +ALTER TABLE people ALTER COLUMN rowid SET HIDDEN; + + + + + If a table is created using a CREATE TABLE statement with the LIKE clause, + any hidden columns in the source table is copied to the new table but by + default the hidden attribute is not set. + +CREATE TABLE foo (LIKE t1); + + To copied the HIDDEN attribute it must be explicitely included. + +CREATE TABLE foo (LIKE t1 INCLUDING HIDDEN); + + + + + Since SELECT * does not return the hidden columns, new tables or views + created in this manner will have no trace of the hidden columns. If + explicitely referenced in the SELECT statement, the columns will be brought + into the view/new table, but the HIDDEN attribute will not. + +db=# \d t1 + Table "public.t1" + Column | Type | Collation | Nullable | Hidden | Default +--------+---------+-----------+----------+--------+--------- + col1 | integer | | | hidden | 13 + col2 | text | | not null | | + +test=# CREATE TABLE t2 AS SELECT * FROM t1; +SELECT 2 +db=# \d t2 + Table "public.t2" + Column | Type | Collation | Nullable | Hidden | Default +--------+------+-----------+----------+--------+--------- + col2 | text | | | | + + + + + Several other points apply to the definition of hidden columns and tables + involving hidden columns: + + + + Hidden columns are also supported in created temporary or unlogged tables + but not in foreign table. + + + + + Hidden columns support the usual column attributes as well as all + constraints. + + + + + An hidden column can be explicitly referenced in a + CREATE INDEX statement or ALTER TABLE + statement. + + + + + Foreign key constraints can be defined on hidden columns and hidden columns + can be referenced in foreign key constraints. + + + + + Virtual columns can be flagged hidden as well as identity columns. + + + + + A table can be partitioned by an hidden column. + + + + + User-defined types can not contain hidden attributes. + + + + + A table must have at least one visible column. + + + + + Hidden column are inherited. + + + + + Changing a column's visibility after defining a view that references the + column does not change the view behavior. + + + + + + + Information about whether a column is hidden or not is available from + the attishidden column of the + pg_attribute + catalog table or in the is_hidden column of the + information_schema.columns table. + + + Constraints diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index c5e68c175f..35125b412d 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -2074,6 +2074,16 @@ updatable, columns in views not necessarily) + + + + is_hidden yes_or_no + + + YES if the column is hidden, + NO if not (columns are visible) + + diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 81291577f8..2180f67f5c 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -46,6 +46,7 @@ ALTER TABLE [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression ALTER [ COLUMN ] column_name DROP DEFAULT ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL + ALTER [ COLUMN ] column_name { SET | DROP } HIDDEN ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ] ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...] @@ -245,6 +246,16 @@ WITH ( MODULUS numeric_literal, REM + + SET/DROP HIDDEN + + + These forms set or remove the hidden attribute for a column. The hidden behavior will only apply in subsequent SELECT or INSERT commands; it does not cause running queries behavior to change. + (See for more information on hidden column). + + + + DROP EXPRESSION [ IF EXISTS ] diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index cc484d5b39..88c4b0486a 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -633,9 +633,9 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] postgres=# \d tab Table "public.tab" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - col | integer | | | + Column | Type | Collation | Nullable | Hidden | Default +--------+---------+-----------+----------+----------------- + col | integer | | | | Indexes: "idx" btree (col) INVALID diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 473a0a4aeb..4873a526de 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ - { column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ] + { column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ] [ HIDDEN ] | table_constraint | LIKE source_table [ like_option ... ] } [, ... ] @@ -87,7 +87,7 @@ class="parameter">referential_action ] [ ON UPDATE and like_option is: -{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } +{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | HIDDEN | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } and partition_bound_spec is: @@ -313,6 +313,22 @@ WITH ( MODULUS numeric_literal, REM + + HIDDEN + + + When a column is defined with the HIDDEN attribute, + it is not available unless it is explicitly referenced. For example, + if a SELECT * FROM table is executed, hidden columns are not returned + in the resulting rows. Same, if an INSERT statement without a target + columns list is executed it does not expect values for any hidden columns. + An hidden column can always be referenced explicitly wherever a column + name can be specified, for example in an ORDER BY or a JOIN clause. + (See for more information on hidden column). + + + + INHERITS ( parent_table [, ... ] ) @@ -676,6 +692,16 @@ WITH ( MODULUS numeric_literal, REM + + INCLUDING HIDDEN + + + Any hidden attribute of copied column definitions will be + copied. By default, new columns will not be hidden. + + + + INCLUDING IDENTITY @@ -1349,7 +1375,6 @@ WITH ( MODULUS numeric_literal, REM - diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 14e0a4dbe3..34076bc5b9 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -4905,10 +4905,10 @@ CREATE TABLE testdb=> \d my_table Table "public.my_table" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - first | integer | | not null | 0 - second | text | | | + Column | Type | Collation | Nullable | Hidden | Default +--------+---------+-----------+----------+----------------- + first | integer | | not null | | 0 + second | text | | | | Now we change the prompt to something more interesting: diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c index 4c63bd4dc6..a62a869e51 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -133,6 +133,7 @@ CreateTupleDescCopy(TupleDesc tupdesc) att->atthasmissing = false; att->attidentity = '\0'; att->attgenerated = '\0'; + att->attishidden = false; } /* We can copy the tuple type identification, too */ @@ -463,6 +464,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2) return false; if (attr1->attcollation != attr2->attcollation) return false; + if (attr1->attishidden != attr2->attishidden) + return false; /* variable-length fields are not even present... */ } @@ -644,6 +647,7 @@ TupleDescInitEntry(TupleDesc desc, att->attstorage = typeForm->typstorage; att->attcompression = InvalidCompressionMethod; att->attcollation = typeForm->typcollation; + att->attishidden = false; ReleaseSysCache(tuple); } @@ -691,6 +695,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc, att->attisdropped = false; att->attislocal = true; att->attinhcount = 0; + att->attishidden = false; /* attacl, attoptions and attfdwoptions are not present in tupledescs */ att->atttypid = oidtypeid; @@ -839,6 +844,7 @@ BuildDescForRelation(List *schema) has_not_null |= entry->is_not_null; att->attislocal = entry->is_local; att->attinhcount = entry->inhcount; + att->attishidden = entry->is_hidden; } if (has_not_null) diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 5898203972..2d95269c00 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -789,7 +789,9 @@ InsertPgAttributeTuples(Relation pg_attribute_rel, slot[slotCount]->tts_values[Anum_pg_attribute_attisdropped - 1] = BoolGetDatum(attrs->attisdropped); slot[slotCount]->tts_values[Anum_pg_attribute_attislocal - 1] = BoolGetDatum(attrs->attislocal); slot[slotCount]->tts_values[Anum_pg_attribute_attinhcount - 1] = Int32GetDatum(attrs->attinhcount); + slot[slotCount]->tts_values[Anum_pg_attribute_attishidden - 1] = BoolGetDatum(attrs->attishidden); slot[slotCount]->tts_values[Anum_pg_attribute_attcollation - 1] = ObjectIdGetDatum(attrs->attcollation); + if (attoptions && attoptions[natts] != (Datum) 0) slot[slotCount]->tts_values[Anum_pg_attribute_attoptions - 1] = attoptions[natts]; else diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 11d9dd60c2..4e676089a3 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -770,7 +770,8 @@ CREATE VIEW columns AS CAST(CASE WHEN c.relkind IN ('r', 'p') OR (c.relkind IN ('v', 'f') AND pg_column_is_updatable(c.oid, a.attnum, false)) - THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable, + CAST(CASE WHEN a.attishidden THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_hidden FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum) JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 53f4853141..24f323b33b 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -637,7 +637,7 @@ ProcessCopyOptions(ParseState *pstate, * * The input attnamelist is either the user-specified column list, * or NIL if there was none (in which case we want all the non-dropped - * columns). + * and not hidden columns). * * We don't include generated columns in the generated full list and we don't * allow them to be specified explicitly. They don't make sense for COPY @@ -659,7 +659,7 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, List *attnamelist) for (i = 0; i < attr_count; i++) { - if (TupleDescAttr(tupDesc, i)->attisdropped) + if (TupleDescAttr(tupDesc, i)->attisdropped || TupleDescAttr(tupDesc, i)->attishidden) continue; if (TupleDescAttr(tupDesc, i)->attgenerated) continue; diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 1c2ebe1bf6..b9f658c5b9 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -602,6 +602,10 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx, static List *GetParentedForeignKeyRefs(Relation partition); static void ATDetachCheckNoForeignKeyRefs(Relation partition); static char GetAttributeCompression(Oid atttypid, char *compression); +static ObjectAddress ATExecDropHidden(Relation rel, const char *colName, + LOCKMODE lockmode); +static ObjectAddress ATExecSetHidden( Relation rel, const char *colName, + LOCKMODE lockmode); /* ---------------------------------------------------------------- @@ -647,6 +651,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, LOCKMODE parentLockmode; const char *accessMethod = NULL; Oid accessMethodId = InvalidOid; + bool has_visible_col = false; /* * Truncate relname to appropriate length (probably a waste of time, as @@ -897,11 +902,25 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, if (colDef->generated) attr->attgenerated = colDef->generated; + if (colDef->is_hidden) + attr->attishidden = true; + else + has_visible_col = true; + if (colDef->compression) attr->attcompression = GetAttributeCompression(attr->atttypid, colDef->compression); } + /* + * Verify that we have at least one visible column + * when there is hidden ones + */ + if (attnum > 0 && !has_visible_col) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("a table must have at least one visible column"))); + /* * If the statement hasn't specified an access method, but we're defining * a type of relation that needs one, use the default. @@ -2340,6 +2359,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence, coldef->cooked_default = restdef->cooked_default; coldef->constraints = restdef->constraints; coldef->is_from_type = false; + coldef->is_hidden = restdef->is_hidden; schema = list_delete_nth_cell(schema, restpos); } else @@ -2565,6 +2585,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("inherited column \"%s\" has a generation conflict", attributeName))); + /* Merge of HIDDEN attribute = OR 'em together */ + def->is_hidden |= attribute->attishidden; } else { @@ -2592,6 +2614,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence, pstrdup(GetCompressionMethodName(attribute->attcompression)); else def->compression = NULL; + def->is_hidden = attribute->attishidden; inhSchema = lappend(inhSchema, def); newattmap->attnums[parent_attno - 1] = ++child_attno; } @@ -2857,6 +2880,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence, def->is_local = true; /* Merge of NOT NULL constraints = OR 'em together */ def->is_not_null |= newdef->is_not_null; + /* Merge of HIDDEN attribute = OR 'em together */ + def->is_hidden |= newdef->is_hidden; /* * Check for conflicts related to generated columns. @@ -2951,6 +2976,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence, { found = true; coldef->is_not_null |= restdef->is_not_null; + coldef->is_hidden |= restdef->is_hidden; /* * Override the parent's default value for this column @@ -4173,6 +4199,8 @@ AlterTableGetLockLevel(List *cmds) case AT_SetIdentity: case AT_DropExpression: case AT_SetCompression: + case AT_DropHidden: + case AT_SetHidden: cmd_lockmode = AccessExclusiveLock; break; @@ -4461,6 +4489,17 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* This command never recurses */ pass = AT_PASS_DROP; break; + case AT_SetHidden: + ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); + /* This command never recurses */ + /* This should run after AddHidden, so do it in MISC pass */ + pass = AT_PASS_MISC; + break; + case AT_DropHidden: + ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); + /* This command never recurses */ + pass = AT_PASS_DROP; + break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE); ATPrepDropNotNull(rel, recurse, recursing); @@ -4859,6 +4898,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, case AT_DropIdentity: address = ATExecDropIdentity(rel, cmd->name, cmd->missing_ok, lockmode); break; + case AT_SetHidden: /* ALTER COLUMN SET HIDDEN */ + address = ATExecSetHidden(rel, cmd->name, lockmode); + break; + case AT_DropHidden: + address = ATExecDropHidden(rel, cmd->name, lockmode); + break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ address = ATExecDropNotNull(rel, cmd->name, lockmode); break; @@ -6101,6 +6146,10 @@ alter_table_type_to_string(AlterTableType cmdtype) return "ALTER COLUMN ... DROP IDENTITY"; case AT_ReAddStatistics: return NULL; /* not real grammar */ + case AT_DropHidden: + return "ALTER COLUMN ... DROP HIDDEN"; + case AT_SetHidden: + return "ALTER COLUMN ... SET HIDDEN"; } return NULL; @@ -6722,6 +6771,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, attribute.attisdropped = false; attribute.attislocal = colDef->is_local; attribute.attinhcount = colDef->inhcount; + attribute.attishidden = colDef->is_hidden; attribute.attcollation = collOid; /* attribute.attacl is handled by InsertPgAttributeTuples() */ @@ -7067,6 +7117,137 @@ ATPrepDropNotNull(Relation rel, bool recurse, bool recursing) } } +/* + * Return the address of the modified column. If the column was already + * visible, InvalidObjectAddress is returned. + */ +static ObjectAddress +ATExecDropHidden(Relation rel, const char *colName, LOCKMODE lockmode) +{ + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + Relation attr_rel; + ObjectAddress address; + + /* + * lookup the attribute + */ + attr_rel = table_open(AttributeRelationId, RowExclusiveLock); + + tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + colName, RelationGetRelationName(rel)))); + attTup = (Form_pg_attribute) GETSTRUCT(tuple); + attnum = attTup->attnum; + + /* Prevent them from altering a system attribute */ + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + colName))); + + /* If rel is partition, shouldn't drop HIDDEN if parent has the same */ + if (rel->rd_rel->relispartition) + { + Oid parentId = get_partition_parent(RelationGetRelid(rel), false); + Relation parent = table_open(parentId, AccessShareLock); + TupleDesc tupDesc = RelationGetDescr(parent); + AttrNumber parent_attnum; + + parent_attnum = get_attnum(parentId, colName); + if (TupleDescAttr(tupDesc, parent_attnum - 1)->attishidden) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("column \"%s\" is marked HIDDEN in parent table", + colName))); + table_close(parent, AccessShareLock); + } + + /* + * Okay, actually perform the catalog change ... if needed + */ + if (attTup->attishidden) + { + attTup->attishidden = false; + + CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple); + + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + } + else + address = InvalidObjectAddress; + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), attnum); + + table_close(attr_rel, RowExclusiveLock); + + return address; +} + +/* + * Return the address of the modified column. If the column was already + * HIDDEN, InvalidObjectAddress is returned. + */ +static ObjectAddress +ATExecSetHidden(Relation rel, const char *colName, LOCKMODE lockmode) +{ + HeapTuple tuple; + AttrNumber attnum; + Relation attr_rel; + ObjectAddress address; + + /* + * lookup the attribute + */ + attr_rel = table_open(AttributeRelationId, RowExclusiveLock); + + tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName); + + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + colName, RelationGetRelationName(rel)))); + + attnum = ((Form_pg_attribute) GETSTRUCT(tuple))->attnum; + + /* Prevent them from altering a system attribute */ + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + colName))); + + /* + * Okay, actually perform the catalog change ... if needed + */ + if (!((Form_pg_attribute) GETSTRUCT(tuple))->attishidden) + { + ((Form_pg_attribute) GETSTRUCT(tuple))->attishidden = true; + + CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple); + + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + } + else + address = InvalidObjectAddress; + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), attnum); + + table_close(attr_rel, RowExclusiveLock); + + return address; +} + /* * Return the address of the modified column. If the column was already * nullable, InvalidObjectAddress is returned. diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 08f1bf1031..ebf17f3af7 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -561,6 +561,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type TableConstraint TableLikeClause %type TableLikeOptionList TableLikeOption %type column_compression opt_column_compression +%type column_hidden %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr %type key_actions key_delete key_match key_update key_action @@ -665,7 +666,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING GROUPS - HANDLER HAVING HEADER_P HOLD HOUR_P + HANDLER HAVING HEADER_P HIDDEN HOLD HOUR_P IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P @@ -2232,6 +2233,22 @@ alter_table_cmd: n->name = $3; $$ = (Node *)n; } + /* ALTER TABLE ALTER [COLUMN] DROP HIDDEN */ + | ALTER opt_column ColId DROP HIDDEN + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_DropHidden; + n->name = $3; + $$ = (Node *)n; + } + /* ALTER TABLE ALTER [COLUMN] SET NOT NULL */ + | ALTER opt_column ColId SET HIDDEN + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetHidden; + n->name = $3; + $$ = (Node *)n; + } /* ALTER TABLE ALTER [COLUMN] DROP EXPRESSION */ | ALTER opt_column ColId DROP EXPRESSION { @@ -3469,7 +3486,7 @@ TypedTableElement: | TableConstraint { $$ = $1; } ; -columnDef: ColId Typename opt_column_compression create_generic_options ColQualList +columnDef: ColId Typename opt_column_compression create_generic_options ColQualList column_hidden { ColumnDef *n = makeNode(ColumnDef); n->colname = $1; @@ -3486,12 +3503,13 @@ columnDef: ColId Typename opt_column_compression create_generic_options ColQualL n->fdwoptions = $4; SplitColQualList($5, &n->constraints, &n->collClause, yyscanner); + n->is_hidden = $6; n->location = @1; $$ = (Node *)n; } ; -columnOptions: ColId ColQualList +columnOptions: ColId ColQualList column_hidden { ColumnDef *n = makeNode(ColumnDef); n->colname = $1; @@ -3506,6 +3524,7 @@ columnOptions: ColId ColQualList n->collOid = InvalidOid; SplitColQualList($2, &n->constraints, &n->collClause, yyscanner); + n->is_hidden = $3; n->location = @1; $$ = (Node *)n; } @@ -3529,6 +3548,11 @@ columnOptions: ColId ColQualList } ; +column_hidden: + HIDDEN { $$ = $1; } + | /*EMPTY*/ { $$ = NULL; } + ; + column_compression: COMPRESSION ColId { $$ = $2; } | COMPRESSION DEFAULT { $$ = pstrdup("default"); } @@ -3770,6 +3794,7 @@ TableLikeOption: | INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; } | STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; } | STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; } + | HIDDEN { $$ = CREATE_TABLE_LIKE_HIDDEN; } | ALL { $$ = CREATE_TABLE_LIKE_ALL; } ; @@ -15595,6 +15620,7 @@ unreserved_keyword: | GROUPS | HANDLER | HEADER_P + | HIDDEN | HOLD | HOUR_P | IDENTITY_P @@ -16146,6 +16172,7 @@ bare_label_keyword: | GROUPS | HANDLER | HEADER_P + | HIDDEN | HOLD | IDENTITY_P | IF_P diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index c5c3f26ecf..4bec918ea3 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -1256,6 +1256,12 @@ buildNSItemFromTupleDesc(RangeTblEntry *rte, Index rtindex, TupleDesc tupdesc) nscolumns[varattno].p_varcollid = attr->attcollation; nscolumns[varattno].p_varnosyn = rtindex; nscolumns[varattno].p_varattnosyn = varattno + 1; + /* + * For an hidden column, the entry will not + * be included in star expansion. + */ + if (attr->attishidden) + nscolumns[varattno].p_dontexpand = true; } /* ... and build the nsitem */ diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 6e8fbc4780..c3c7df0783 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -1029,7 +1029,7 @@ checkInsertTargets(ParseState *pstate, List *cols, List **attrnos) attr = TupleDescAttr(pstate->p_target_relation->rd_att, i); - if (attr->attisdropped) + if (attr->attisdropped || attr->attishidden) continue; col = makeNode(ResTarget); @@ -1304,7 +1304,6 @@ ExpandAllTables(ParseState *pstate, int location) Assert(!nsitem->p_lateral_only); /* Remember we found a p_cols_visible item */ found_table = true; - target = list_concat(target, expandNSItemAttrs(pstate, nsitem, diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 313d7b6ff0..d5702fa5dd 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1084,6 +1084,12 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla else def->storage = 0; + /* Likewise, copy hidden if requested */ + if (table_like_clause->options & CREATE_TABLE_LIKE_HIDDEN) + def->is_hidden = attribute->attishidden; + else + def->is_hidden = false; + /* Likewise, copy compression if requested */ if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 && CompressionMethodIsValid(attribute->attcompression)) @@ -1482,6 +1488,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename) n->collOid = attr->attcollation; n->constraints = NIL; n->location = -1; + n->is_hidden = false; cxt->columns = lappend(cxt->columns, n); } DecrTupleDescRefCount(tupdesc); diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 13d9994af3..756c85c019 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -3450,6 +3450,7 @@ RelationBuildLocalRelation(const char *relname, datt->attidentity = satt->attidentity; datt->attgenerated = satt->attgenerated; datt->attnotnull = satt->attnotnull; + datt->attishidden = satt->attishidden; has_not_null |= satt->attnotnull; } diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index a485fb2d07..f4312c5693 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -8712,6 +8712,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) int i_attoptions; int i_attcollation; int i_attcompression; + int i_attishidden; int i_attfdwoptions; int i_attmissingval; int i_atthasdef; @@ -8788,6 +8789,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) appendPQExpBuffer(q, "'' AS attcompression,\n"); + if (fout->remoteVersion >= 150000) + appendPQExpBuffer(q, + "a.attishidden,\n"); + else + appendPQExpBuffer(q, + "'f' AS attishidden,\n"); + if (fout->remoteVersion >= 90200) appendPQExpBufferStr(q, "pg_catalog.array_to_string(ARRAY(" @@ -8851,6 +8859,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attoptions = (char **) pg_malloc(ntups * sizeof(char *)); tbinfo->attcollation = (Oid *) pg_malloc(ntups * sizeof(Oid)); tbinfo->attcompression = (char *) pg_malloc(ntups * sizeof(char)); + tbinfo->attishidden = (bool *) pg_malloc(ntups * sizeof(bool)); tbinfo->attfdwoptions = (char **) pg_malloc(ntups * sizeof(char *)); tbinfo->attmissingval = (char **) pg_malloc(ntups * sizeof(char *)); tbinfo->notnull = (bool *) pg_malloc(ntups * sizeof(bool)); @@ -8875,6 +8884,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) i_attoptions = PQfnumber(res, "attoptions"); i_attcollation = PQfnumber(res, "attcollation"); i_attcompression = PQfnumber(res, "attcompression"); + i_attishidden = PQfnumber(res, "attishidden"); i_attfdwoptions = PQfnumber(res, "attfdwoptions"); i_attmissingval = PQfnumber(res, "attmissingval"); i_atthasdef = PQfnumber(res, "atthasdef"); @@ -8901,6 +8911,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, j, i_attoptions)); tbinfo->attcollation[j] = atooid(PQgetvalue(res, j, i_attcollation)); tbinfo->attcompression[j] = *(PQgetvalue(res, j, i_attcompression)); + tbinfo->attishidden[j] = (PQgetvalue(res, j, i_attishidden)[0] == 't'); tbinfo->attfdwoptions[j] = pg_strdup(PQgetvalue(res, j, i_attfdwoptions)); tbinfo->attmissingval[j] = pg_strdup(PQgetvalue(res, j, i_attmissingval)); tbinfo->attrdefs[j] = NULL; /* fix below */ @@ -16126,6 +16137,9 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) appendPQExpBuffer(q, " COLLATE %s", fmtQualifiedDumpable(coll)); } + if (tbinfo->attishidden[j]) + appendPQExpBufferStr(q, " HIDDEN"); + } } @@ -16547,6 +16561,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) qualrelname, fmtId(tbinfo->attnames[j]), tbinfo->attfdwoptions[j]); + } /* end loop over columns */ if (ftoptions) diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 29af845ece..a9e95316af 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -321,6 +321,7 @@ typedef struct _tableInfo char **attoptions; /* per-attribute options */ Oid *attcollation; /* per-attribute collation selection */ char *attcompression; /* per-attribute compression method */ + bool *attishidden; /* hidden column */ char **attfdwoptions; /* per-attribute fdw options */ char **attmissingval; /* per attribute missing value */ bool *notnull; /* NOT NULL constraints on attributes */ diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index ea4ca5c05c..1de0778b5f 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1628,6 +1628,7 @@ describeOneTableDetails(const char *schemaname, atttype_col = -1, attrdef_col = -1, attnotnull_col = -1, + attishidden_col = -1, attcoll_col = -1, attidentity_col = -1, attgenerated_col = -1, @@ -2091,6 +2092,14 @@ describeOneTableDetails(const char *schemaname, appendPQExpBufferStr(&buf, ",\n pg_catalog.col_description(a.attrelid, a.attnum)"); attdescr_col = cols++; } + + /* column visibility in a SELECT *, if relevant to relkind */ + if (tableinfo.relkind == RELKIND_RELATION || + tableinfo.relkind == RELKIND_PARTITIONED_TABLE) + { + appendPQExpBufferStr(&buf, ",\n a.attishidden AS attishidden"); + attishidden_col = cols++; + } } appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_attribute a"); @@ -2183,6 +2192,8 @@ describeOneTableDetails(const char *schemaname, headers[cols++] = gettext_noop("Nullable"); headers[cols++] = gettext_noop("Default"); } + if (attishidden_col >= 0) + headers[cols++] = gettext_noop("Visible"); if (isindexkey_col >= 0) headers[cols++] = gettext_noop("Key?"); if (indexdef_col >= 0) @@ -2215,7 +2226,7 @@ describeOneTableDetails(const char *schemaname, /* Type */ printTableAddCell(&cont, PQgetvalue(res, i, atttype_col), false, false); - /* Collation, Nullable, Default */ + /* Collation, Nullable, Hidden, Default */ if (show_column_details) { char *identity; @@ -2228,7 +2239,6 @@ describeOneTableDetails(const char *schemaname, printTableAddCell(&cont, strcmp(PQgetvalue(res, i, attnotnull_col), "t") == 0 ? "not null" : "", false, false); - identity = PQgetvalue(res, i, attidentity_col); generated = PQgetvalue(res, i, attgenerated_col); @@ -2258,6 +2268,12 @@ describeOneTableDetails(const char *schemaname, if (fdwopts_col >= 0) printTableAddCell(&cont, PQgetvalue(res, i, fdwopts_col), false, false); + /* Column visibility in SELECT *, if relevant */ + if (attishidden_col >= 0) + printTableAddCell(&cont, + strcmp(PQgetvalue(res, i, attishidden_col), "t") == 0 ? "hidden" : "", + false, false); + /* Storage mode, if relevant */ if (attstorage_col >= 0) { diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h index 5c1ec9313e..eb4fe3a1e7 100644 --- a/src/include/catalog/pg_attribute.h +++ b/src/include/catalog/pg_attribute.h @@ -167,8 +167,15 @@ CATALOG(pg_attribute,1249,AttributeRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(75, /* Number of times inherited from direct parent relation(s) */ int32 attinhcount BKI_DEFAULT(0); + /* + * This flag specifies whether this column is expendable in + * a SELECT *, an INSERT without column list, or not. It is true when + * a column is defined with the HIDDEN attribute, false otherwise. + */ + bool attishidden BKI_DEFAULT(f); + /* attribute's collation, if any */ - Oid attcollation BKI_LOOKUP_OPT(pg_collation); + Oid attcollation BKI_LOOKUP_OPT(pg_collation); #ifdef CATALOG_VARLEN /* variable-length fields start here */ /* NOTE: The following fields are not present in tuple descriptors. */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 3138877553..48b6c833fb 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -686,6 +686,8 @@ typedef struct ColumnDef List *constraints; /* other constraints on column */ List *fdwoptions; /* per-column FDW options */ int location; /* parse location, or -1 if none/unknown */ + bool is_hidden; /* column is not included in star expansion? + (hidden column) */ } ColumnDef; /* @@ -710,6 +712,7 @@ typedef enum TableLikeOption CREATE_TABLE_LIKE_INDEXES = 1 << 6, CREATE_TABLE_LIKE_STATISTICS = 1 << 7, CREATE_TABLE_LIKE_STORAGE = 1 << 8, + CREATE_TABLE_LIKE_HIDDEN = 1 << 9, CREATE_TABLE_LIKE_ALL = PG_INT32_MAX } TableLikeOption; @@ -1946,7 +1949,9 @@ typedef enum AlterTableType AT_AddIdentity, /* ADD IDENTITY */ AT_SetIdentity, /* SET identity column options */ AT_DropIdentity, /* DROP IDENTITY */ - AT_ReAddStatistics /* internal to commands/tablecmds.c */ + AT_ReAddStatistics, /* internal to commands/tablecmds.c */ + AT_DropHidden, /* alter column drop hidden */ + AT_SetHidden /* alter column set hidden */ } AlterTableType; typedef struct ReplicaIdentityStmt diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f836acf876..5b36033d93 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -192,6 +192,7 @@ PG_KEYWORD("groups", GROUPS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("handler", HANDLER, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("having", HAVING, RESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("header", HEADER_P, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("hidden", HIDDEN, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 4bee0c1173..cb2d7322cc 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2199,11 +2199,11 @@ where oid = 'test_storage'::regclass; create index test_storage_idx on test_storage (b, a); alter table test_storage alter column a set storage external; \d+ test_storage - Table "public.test_storage" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | external | | - b | integer | | | 0 | plain | | + Table "public.test_storage" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | external | | + b | integer | | | 0 | | plain | | Indexes: "test_storage_idx" btree (b, a) @@ -4187,10 +4187,10 @@ DROP TABLE part_rpd; -- works fine ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY; \d+ range_parted2 - Partitioned table "public.range_parted2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | + Partitioned table "public.range_parted2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | Partition key: RANGE (a) Number of partitions: 0 diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out index 1ce2962d55..9214b6b99f 100644 --- a/src/test/regress/expected/compression_1.out +++ b/src/test/regress/expected/compression_1.out @@ -6,10 +6,10 @@ CREATE TABLE cmdata(f1 text COMPRESSION pglz); CREATE INDEX idx ON cmdata(f1); INSERT INTO cmdata VALUES(repeat('1234567890', 1000)); \d+ cmdata - Table "public.cmdata" - Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+------+-----------+----------+---------+----------+-------------+--------------+------------- - f1 | text | | | | extended | pglz | | + Table "public.cmdata" + Column | Type | Collation | Nullable | Default | Visible | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+-------------+--------------+------------- + f1 | text | | | | | extended | pglz | | Indexes: "idx" btree (f1) @@ -47,10 +47,10 @@ LINE 1: SELECT SUBSTR(f1, 2000, 50) FROM cmdata1; -- copy with table creation SELECT * INTO cmmove1 FROM cmdata; \d+ cmmove1 - Table "public.cmmove1" - Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+------+-----------+----------+---------+----------+-------------+--------------+------------- - f1 | text | | | | extended | | | + Table "public.cmmove1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+-------------+--------------+------------- + f1 | text | | | | | extended | | | SELECT pg_column_compression(f1) FROM cmmove1; pg_column_compression @@ -134,41 +134,41 @@ DROP TABLE cmdata2; --test column type update varlena/non-varlena CREATE TABLE cmdata2 (f1 int); \d+ cmdata2 - Table "public.cmdata2" - Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+---------+-----------+----------+---------+---------+-------------+--------------+------------- - f1 | integer | | | | plain | | | + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+-------------+--------------+------------- + f1 | integer | | | | | plain | | | ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar; \d+ cmdata2 - Table "public.cmdata2" - Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- - f1 | character varying | | | | extended | | | + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+----------+-------------+--------------+------------- + f1 | character varying | | | | | extended | | | ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer; \d+ cmdata2 - Table "public.cmdata2" - Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+---------+-----------+----------+---------+---------+-------------+--------------+------------- - f1 | integer | | | | plain | | | + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+-------------+--------------+------------- + f1 | integer | | | | | plain | | | --changing column storage should not impact the compression method --but the data should not be compressed ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar; ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION pglz; \d+ cmdata2 - Table "public.cmdata2" - Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- - f1 | character varying | | | | extended | pglz | | + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+----------+-------------+--------------+------------- + f1 | character varying | | | | | extended | pglz | | ALTER TABLE cmdata2 ALTER COLUMN f1 SET STORAGE plain; \d+ cmdata2 - Table "public.cmdata2" - Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+-------------------+-----------+----------+---------+---------+-------------+--------------+------------- - f1 | character varying | | | | plain | pglz | | + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+---------+-------------+--------------+------------- + f1 | character varying | | | | | plain | pglz | | INSERT INTO cmdata2 VALUES (repeat('123456789', 800)); SELECT pg_column_compression(f1) FROM cmdata2; @@ -243,10 +243,10 @@ DETAIL: This functionality requires the server to be built with lz4 support. HINT: You need to rebuild PostgreSQL using --with-lz4. INSERT INTO cmdata VALUES (repeat('123456789', 4004)); \d+ cmdata - Table "public.cmdata" - Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+------+-----------+----------+---------+----------+-------------+--------------+------------- - f1 | text | | | | extended | pglz | | + Table "public.cmdata" + Column | Type | Collation | Nullable | Default | Visible | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+-------------+--------------+------------- + f1 | text | | | | | extended | pglz | | Indexes: "idx" btree (f1) @@ -259,10 +259,10 @@ SELECT pg_column_compression(f1) FROM cmdata; ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION default; \d+ cmdata2 - Table "public.cmdata2" - Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+-------------------+-----------+----------+---------+---------+-------------+--------------+------------- - f1 | character varying | | | | plain | | | + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+---------+-------------+--------------+------------- + f1 | character varying | | | | | plain | | | -- test alter compression method for materialized views ALTER MATERIALIZED VIEW compressmv ALTER COLUMN x SET COMPRESSION lz4; diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 5f3685e9ef..c428703cb8 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -513,10 +513,10 @@ begin end $$ language plpgsql immutable; alter table check_con_tbl add check (check_con_function(check_con_tbl.*)); \d+ check_con_tbl - Table "public.check_con_tbl" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | | | plain | | + Table "public.check_con_tbl" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + f1 | integer | | | | | plain | | Check constraints: "check_con_tbl_check" CHECK (check_con_function(check_con_tbl.*)) diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index a958b84979..3742bb2e1a 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -498,11 +498,11 @@ Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "C") Number of partitions: 0 \d+ partitioned2 - Partitioned table "public.partitioned2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | integer | | | | plain | | - b | text | | | | extended | | + Partitioned table "public.partitioned2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | integer | | | | | plain | | + b | text | | | | | extended | | Partition key: RANGE (((a + 1)), substr(b, 1, 5)) Number of partitions: 0 @@ -511,11 +511,11 @@ ERROR: no partition of relation "partitioned2" found for row DETAIL: Partition key of the failing row contains ((a + 1), substr(b, 1, 5)) = (2, hello). CREATE TABLE part2_1 PARTITION OF partitioned2 FOR VALUES FROM (-1, 'aaaaa') TO (100, 'ccccc'); \d+ part2_1 - Table "public.part2_1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | integer | | | | plain | | - b | text | | | | extended | | + Table "public.part2_1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | integer | | | | | plain | | + b | text | | | | | extended | | Partition of: partitioned2 FOR VALUES FROM ('-1', 'aaaaa') TO (100, 'ccccc') Partition constraint: (((a + 1) IS NOT NULL) AND (substr(b, 1, 5) IS NOT NULL) AND (((a + 1) > '-1'::integer) OR (((a + 1) = '-1'::integer) AND (substr(b, 1, 5) >= 'aaaaa'::text))) AND (((a + 1) < 100) OR (((a + 1) = 100) AND (substr(b, 1, 5) < 'ccccc'::text)))) @@ -552,11 +552,11 @@ select * from partitioned where partitioned = '(1,2)'::partitioned; (2 rows) \d+ partitioned1 - Table "public.partitioned1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | + Table "public.partitioned1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | + b | integer | | | | | plain | | Partition of: partitioned FOR VALUES IN ('(1,2)') Partition constraint: (((partitioned1.*)::partitioned IS DISTINCT FROM NULL) AND ((partitioned1.*)::partitioned = '(1,2)'::partitioned)) @@ -609,10 +609,10 @@ CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2); CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1)); CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); \d+ list_parted - Partitioned table "public.list_parted" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | + Partitioned table "public.list_parted" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | Partition key: LIST (a) Partitions: part_null FOR VALUES IN (NULL), part_p1 FOR VALUES IN (1), @@ -1057,21 +1057,21 @@ create table test_part_coll_cast2 partition of test_part_coll_posix for values f drop table test_part_coll_posix; -- Partition bound in describe output \d+ part_b - Table "public.part_b" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | not null | 1 | plain | | + Table "public.part_b" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | not null | 1 | | plain | | Partition of: parted FOR VALUES IN ('b') Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text)) -- Both partition bound and partition key in describe output \d+ part_c - Partitioned table "public.part_c" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | not null | 0 | plain | | + Partitioned table "public.part_c" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | not null | 0 | | plain | | Partition of: parted FOR VALUES IN ('c') Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text)) Partition key: RANGE (b) @@ -1079,11 +1079,11 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) -- a level-2 partition's constraint will include the parent's expressions \d+ part_c_1_10 - Table "public.part_c_1_10" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | not null | 0 | plain | | + Table "public.part_c_1_10" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | not null | 0 | | plain | | Partition of: part_c FOR VALUES FROM (1) TO (10) Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10)) @@ -1112,46 +1112,46 @@ Number of partitions: 4 (Use \d+ to list them.) CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c); CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE); \d+ unbounded_range_part - Table "public.unbounded_range_part" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | + Table "public.unbounded_range_part" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | + b | integer | | | | | plain | | + c | integer | | | | | plain | | Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE) Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL)) DROP TABLE unbounded_range_part; CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE); \d+ range_parted4_1 - Table "public.range_parted4_1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | + Table "public.range_parted4_1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | + b | integer | | | | | plain | | + c | integer | | | | | plain | | Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE) Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND (abs(a) <= 1)) CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE); \d+ range_parted4_2 - Table "public.range_parted4_2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | + Table "public.range_parted4_2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | + b | integer | | | | | plain | | + c | integer | | | | | plain | | Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE) Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7)))) CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE); \d+ range_parted4_3 - Table "public.range_parted4_3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | + Table "public.range_parted4_3" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | + b | integer | | | | | plain | | + c | integer | | | | | plain | | Partition of: range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE) Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9)) @@ -1183,11 +1183,11 @@ SELECT obj_description('parted_col_comment'::regclass); (1 row) \d+ parted_col_comment - Partitioned table "public.parted_col_comment" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+--------------- - a | integer | | | | plain | | Partition key - b | text | | | | extended | | + Partitioned table "public.parted_col_comment" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+--------------- + a | integer | | | | | plain | | Partition key + b | text | | | | | extended | | Partition key: LIST (a) Number of partitions: 0 @@ -1196,10 +1196,10 @@ DROP TABLE parted_col_comment; CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a); CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}'); \d+ arrlp12 - Table "public.arrlp12" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-----------+-----------+----------+---------+----------+--------------+------------- - a | integer[] | | | | extended | | + Table "public.arrlp12" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+-----------+-----------+----------+---------+---------+----------+--------------+------------- + a | integer[] | | | | | extended | | Partition of: arrlp FOR VALUES IN ('{1}', '{2}') Partition constraint: ((a IS NOT NULL) AND ((a = '{1}'::integer[]) OR (a = '{2}'::integer[]))) @@ -1209,10 +1209,10 @@ create table boolspart (a bool) partition by list (a); create table boolspart_t partition of boolspart for values in (true); create table boolspart_f partition of boolspart for values in (false); \d+ boolspart - Partitioned table "public.boolspart" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | boolean | | | | plain | | + Partitioned table "public.boolspart" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | boolean | | | | | plain | | Partition key: LIST (a) Partitions: boolspart_f FOR VALUES IN (false), boolspart_t FOR VALUES IN (true) diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 0ed94f1d2f..0683c930f9 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -327,32 +327,32 @@ CREATE TABLE ctlt4 (a text, c text); ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL; CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE); \d+ ctlt12_storage - Table "public.ctlt12_storage" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | - b | text | | | | extended | | - c | text | | | | external | | + Table "public.ctlt12_storage" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | not null | | | main | | + b | text | | | | | extended | | + c | text | | | | | external | | CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); \d+ ctlt12_comments - Table "public.ctlt12_comments" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | extended | | A - b | text | | | | extended | | B - c | text | | | | extended | | C + Table "public.ctlt12_comments" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | not null | | | extended | | A + b | text | | | | | extended | | B + c | text | | | | | extended | | C CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); NOTICE: merging column "a" with inherited definition NOTICE: merging column "b" with inherited definition NOTICE: merging constraint "ctlt1_a_check" with inherited definition \d+ ctlt1_inh - Table "public.ctlt1_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | A - b | text | | | | extended | | B + Table "public.ctlt1_inh" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | not null | | | main | | A + b | text | | | | | extended | | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) Inherits: ctlt1 @@ -366,12 +366,12 @@ SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_con CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); NOTICE: merging multiple inherited definitions of column "a" \d+ ctlt13_inh - Table "public.ctlt13_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | - b | text | | | | extended | | - c | text | | | | external | | + Table "public.ctlt13_inh" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | not null | | | main | | + b | text | | | | | extended | | + c | text | | | | | external | | Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) @@ -382,12 +382,12 @@ Inherits: ctlt1, CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); NOTICE: merging column "a" with inherited definition \d+ ctlt13_like - Table "public.ctlt13_like" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | A3 - b | text | | | | extended | | - c | text | | | | external | | C + Table "public.ctlt13_like" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | not null | | | main | | A3 + b | text | | | | | extended | | + c | text | | | | | external | | C Indexes: "ctlt13_like_expr_idx" btree ((a || c)) Check constraints: @@ -404,11 +404,11 @@ SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_con CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); \d+ ctlt_all - Table "public.ctlt_all" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | A - b | text | | | | extended | | B + Table "public.ctlt_all" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | not null | | | main | | A + b | text | | | | | extended | | B Indexes: "ctlt_all_pkey" PRIMARY KEY, btree (a) "ctlt_all_b_idx" btree (b) @@ -444,11 +444,11 @@ DETAIL: MAIN versus EXTENDED -- Check that LIKE isn't confused by a system catalog of the same name CREATE TABLE pg_attrdef (LIKE ctlt1 INCLUDING ALL); \d+ public.pg_attrdef - Table "public.pg_attrdef" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | A - b | text | | | | extended | | B + Table "public.pg_attrdef" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | not null | | | main | | A + b | text | | | | | extended | | B Indexes: "pg_attrdef_pkey" PRIMARY KEY, btree (a) "pg_attrdef_b_idx" btree (b) @@ -466,11 +466,11 @@ CREATE SCHEMA ctl_schema; SET LOCAL search_path = ctl_schema, public; CREATE TABLE ctlt1 (LIKE ctlt1 INCLUDING ALL); \d+ ctlt1 - Table "ctl_schema.ctlt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | A - b | text | | | | extended | | B + Table "ctl_schema.ctlt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | not null | | | main | | A + b | text | | | | | extended | | B Indexes: "ctlt1_pkey" PRIMARY KEY, btree (a) "ctlt1_b_idx" btree (b) diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 411d5c003e..a1d7915a31 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -266,10 +266,10 @@ explain (verbose, costs off) create rule silly as on delete to dcomptable do instead update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0; \d+ dcomptable - Table "public.dcomptable" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-----------+-----------+----------+---------+----------+--------------+------------- - d1 | dcomptype | | | | extended | | + Table "public.dcomptable" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+-----------+-----------+----------+---------+---------+----------+--------------+------------- + d1 | dcomptype | | | | | extended | | Indexes: "dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1) Rules: @@ -403,10 +403,10 @@ create rule silly as on delete to dcomptable do instead update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 where d1[1].i > 0; \d+ dcomptable - Table "public.dcomptable" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------------+-----------+----------+---------+----------+--------------+------------- - d1 | dcomptypea | | | | extended | | + Table "public.dcomptable" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+------------+-----------+----------+---------+---------+----------+--------------+------------- + d1 | dcomptypea | | | | | extended | | Indexes: "dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1) Rules: diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 426080ae39..7f04a4dac5 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -1389,12 +1389,12 @@ CREATE TABLE fd_pt1 ( CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Child tables: ft2 \d+ ft2 @@ -1410,12 +1410,12 @@ Inherits: fd_pt1 DROP FOREIGN TABLE ft2; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | CREATE FOREIGN TABLE ft2 ( c1 integer NOT NULL, @@ -1434,12 +1434,12 @@ FDW options: (delimiter ',', quote '"', "be quoted" 'value') ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Child tables: ft2 \d+ ft2 @@ -1477,12 +1477,12 @@ Child tables: ct3, ft3 \d+ ct3 - Table "public.ct3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.ct3" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Inherits: ft2 \d+ ft3 @@ -1502,17 +1502,17 @@ ALTER TABLE fd_pt1 ADD COLUMN c6 integer; ALTER TABLE fd_pt1 ADD COLUMN c7 integer NOT NULL; ALTER TABLE fd_pt1 ADD COLUMN c8 integer; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | - c4 | integer | | | | plain | | - c5 | integer | | | 0 | plain | | - c6 | integer | | | | plain | | - c7 | integer | | not null | | plain | | - c8 | integer | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | + c4 | integer | | | | | plain | | + c5 | integer | | | 0 | | plain | | + c6 | integer | | | | | plain | | + c7 | integer | | not null | | | plain | | + c8 | integer | | | | | plain | | Child tables: ft2 \d+ ft2 @@ -1534,17 +1534,17 @@ Child tables: ct3, ft3 \d+ ct3 - Table "public.ct3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | - c4 | integer | | | | plain | | - c5 | integer | | | 0 | plain | | - c6 | integer | | | | plain | | - c7 | integer | | not null | | plain | | - c8 | integer | | | | plain | | + Table "public.ct3" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | + c4 | integer | | | | | plain | | + c5 | integer | | | 0 | | plain | | + c6 | integer | | | | | plain | | + c7 | integer | | not null | | | plain | | + c8 | integer | | | | | plain | | Inherits: ft2 \d+ ft3 @@ -1576,17 +1576,17 @@ ALTER TABLE fd_pt1 ALTER COLUMN c1 SET (n_distinct = 100); ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STATISTICS -1; ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | - c4 | integer | | | 0 | plain | | - c5 | integer | | | | plain | | - c6 | integer | | not null | | plain | | - c7 | integer | | | | plain | | - c8 | text | | | | external | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | 10000 | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | + c4 | integer | | | 0 | | plain | | + c5 | integer | | | | | plain | | + c6 | integer | | not null | | | plain | | + c7 | integer | | | | | plain | | + c8 | text | | | | | external | | Child tables: ft2 \d+ ft2 @@ -1614,12 +1614,12 @@ ALTER TABLE fd_pt1 DROP COLUMN c6; ALTER TABLE fd_pt1 DROP COLUMN c7; ALTER TABLE fd_pt1 DROP COLUMN c8; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | 10000 | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Child tables: ft2 \d+ ft2 @@ -1651,12 +1651,12 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit -- child does not inherit NO INHERIT constraints \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | 10000 | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) @@ -1698,12 +1698,12 @@ ALTER FOREIGN TABLE ft2 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> ''); ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; -- child does not inherit NO INHERIT constraints \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | 10000 | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) @@ -1729,12 +1729,12 @@ ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk2 CASCADE; INSERT INTO fd_pt1 VALUES (1, 'fd_pt1'::text, '1994-01-01'::date); ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | 10000 | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID Child tables: ft2 @@ -1756,12 +1756,12 @@ Inherits: fd_pt1 -- VALIDATE CONSTRAINT need do nothing on foreign tables ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | 10000 | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) Child tables: ft2 @@ -1787,12 +1787,12 @@ ALTER TABLE fd_pt1 RENAME COLUMN c3 TO f3; -- changes name of a constraint recursively ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | not null | | plain | 10000 | - f2 | text | | | | extended | | - f3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + f1 | integer | | not null | | | plain | 10000 | + f2 | text | | | | | extended | | + f3 | date | | | | | plain | | Check constraints: "f2_check" CHECK (f2 <> ''::text) Child tables: ft2 @@ -1851,12 +1851,12 @@ CREATE TABLE fd_pt2 ( CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Partition key: LIST (c1) Partitions: fd_pt2_1 FOR VALUES IN (1) @@ -1896,12 +1896,12 @@ ERROR: table "fd_pt2_1" contains column "c4" not found in parent "fd_pt2" DETAIL: The new partition may contain only the columns present in parent. DROP FOREIGN TABLE fd_pt2_1; \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Partition key: LIST (c1) Number of partitions: 0 @@ -1923,12 +1923,12 @@ FDW options: (delimiter ',', quote '"', "be quoted" 'value') -- no attach partition validation occurs for foreign tables ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Partition key: LIST (c1) Partitions: fd_pt2_1 FOR VALUES IN (1) @@ -1951,12 +1951,12 @@ ERROR: cannot add column to a partition ALTER TABLE fd_pt2_1 ALTER c3 SET NOT NULL; ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> ''); \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | Partition key: LIST (c1) Partitions: fd_pt2_1 FOR VALUES IN (1) @@ -1981,12 +1981,12 @@ ERROR: column "c1" is marked NOT NULL in parent table ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | not null | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | not null | | | extended | | + c3 | date | | | | | plain | | Partition key: LIST (c1) Number of partitions: 0 @@ -2009,12 +2009,12 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | not null | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | not null | | | extended | | + c3 | date | | | | | plain | | Partition key: LIST (c1) Check constraints: "fd_pt2chk1" CHECK (c1 > 0) diff --git a/src/test/regress/expected/hidden.out b/src/test/regress/expected/hidden.out new file mode 100644 index 0000000000..bd62017393 --- /dev/null +++ b/src/test/regress/expected/hidden.out @@ -0,0 +1,499 @@ +-- sanity check of system catalog +SELECT attrelid, attname, attishidden FROM pg_attribute WHERE attishidden; + attrelid | attname | attishidden +----------+---------+------------- +(0 rows) + +CREATE TABLE htest0 (a int PRIMARY KEY, b text NOT NULL HIDDEN); +INSERT INTO htest0 (a, b) VALUES (1, 'htest0 one'); +INSERT INTO htest0 (a, b) VALUES (2, 'htest0 two'); +CREATE TABLE htest1 (a bigserial PRIMARY KEY HIDDEN, b text); +-- Insert without named column must exclude the hidden column +INSERT INTO htest1 VALUES ('htest1 one'); +INSERT INTO htest1 VALUES ('htest1 two'); +SELECT table_name, column_name, column_default, is_nullable, is_hidden FROM information_schema.columns WHERE table_name LIKE 'htest_' ORDER BY 1, 2; + table_name | column_name | column_default | is_nullable | is_hidden +------------+-------------+-----------------------------------+-------------+----------- + htest0 | a | | NO | NO + htest0 | b | | NO | YES + htest1 | a | nextval('htest1_a_seq'::regclass) | NO | YES + htest1 | b | | YES | NO +(4 rows) + +SELECT table_name, column_name, is_hidden FROM information_schema.columns WHERE is_hidden = 'YES' ORDER BY 1, 2; + table_name | column_name | is_hidden +------------+-------------+----------- + htest0 | b | YES + htest1 | a | YES +(2 rows) + +SELECT attrelid::regclass, attname, attishidden FROM pg_attribute WHERE attishidden; + attrelid | attname | attishidden +----------+---------+------------- + htest0 | b | t + htest1 | a | t +(2 rows) + +\d+ htest1 + Table "public.htest1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+--------+-----------+----------+-----------------------------------+---------+----------+--------------+------------- + a | bigint | | not null | nextval('htest1_a_seq'::regclass) | hidden | plain | | + b | text | | | | | extended | | +Indexes: + "htest1_pkey" PRIMARY KEY, btree (a) + +-- DROP/SET hidden attribute +ALTER TABLE htest0 ALTER COLUMN b DROP HIDDEN; +\d+ htest0 + Table "public.htest0" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | integer | | not null | | | plain | | + b | text | | not null | | | extended | | +Indexes: + "htest0_pkey" PRIMARY KEY, btree (a) + +ALTER TABLE htest0 ALTER COLUMN b SET HIDDEN; +-- Hidden column are not expandable and must not be returned +SELECT * FROM htest0; -- return only column a + a +--- + 1 + 2 +(2 rows) + +SELECT * FROM htest1; -- return only column b + b +------------ + htest1 one + htest1 two +(2 rows) + +-- CTEs based on SELECT * only have visible column returned +WITH foo AS (SELECT * FROM htest1) SELECT * FROM foo; -- Only column b is returned here + b +------------ + htest1 one + htest1 two +(2 rows) + +-- inheritance, the hidden attribute is inherited +CREATE TABLE htest1_1 () INHERITS (htest1); +SELECT * FROM htest1_1; + b +--- +(0 rows) + +\d htest1_1 + Table "public.htest1_1" + Column | Type | Collation | Nullable | Default +--------+--------+-----------+----------+----------------------------------- + a | bigint | | not null | nextval('htest1_a_seq'::regclass) + b | text | | | +Inherits: htest1 + +INSERT INTO htest1_1 VALUES ('htest1 three'); +SELECT * FROM htest1_1; + b +-------------- + htest1 three +(1 row) + +SELECT * FROM htest1; + b +-------------- + htest1 one + htest1 two + htest1 three +(3 rows) + +-- hidden column must be explicitely named to be returned +SELECT a,b FROM htest1_1; + a | b +---+-------------- + 3 | htest1 three +(1 row) + +SELECT a,b FROM htest1; + a | b +---+-------------- + 1 | htest1 one + 2 | htest1 two + 3 | htest1 three +(3 rows) + +DROP TABLE htest1_1; +-- Default CREATE TABLE ... LIKE includes hidden columns, and they are not hidden in the new table. +CREATE TABLE htest_like1 (LIKE htest1); +\d+ htest_like1 + Table "public.htest_like1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+--------+-----------+----------+---------+---------+----------+--------------+------------- + a | bigint | | not null | | | plain | | + b | text | | | | | extended | | + +-- CREATE TABLE ... LIKE includes hidden columns, and they are hidden if requested +CREATE TABLE htest_like2 (LIKE htest1 INCLUDING HIDDEN); +\d+ htest_like2 + Table "public.htest_like2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+--------+-----------+----------+---------+---------+----------+--------------+------------- + a | bigint | | not null | | hidden | plain | | + b | text | | | | | extended | | + +CREATE TABLE htest_like3 (LIKE htest1 INCLUDING ALL); +\d+ htest_like3 + Table "public.htest_like3" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+--------+-----------+----------+-----------------------------------+---------+----------+--------------+------------- + a | bigint | | not null | nextval('htest1_a_seq'::regclass) | hidden | plain | | + b | text | | | | | extended | | +Indexes: + "htest_like3_pkey" PRIMARY KEY, btree (a) + +DROP TABLE htest_like1, htest_like2, htest_like3; +-- Insert without named column with and a not null hidden column must have a default value +INSERT INTO htest0 VALUES (3); -- error +ERROR: null value in column "b" of relation "htest0" violates not-null constraint +DETAIL: Failing row contains (3, null). +ALTER TABLE htest0 ALTER COLUMN b SET DEFAULT 'unknown'; +INSERT INTO htest0 VALUES (3); +-- Same with COPY +COPY htest0 TO stdout; +1 +2 +3 +COPY htest0 (a, b) TO stdout; +1 htest0 one +2 htest0 two +3 unknown +COPY htest0 FROM stdin; +SELECT a,b FROM htest0; + a | b +---+------------ + 1 | htest0 one + 2 | htest0 two + 3 | unknown + 4 | unknown + 5 | unknown +(5 rows) + +-- same but with drop/add the column between hidden columns (virtual columns can be made hidden) +CREATE TABLE htest2 (a serial HIDDEN, b int, c int GENERATED ALWAYS AS (a * 2) STORED HIDDEN); +INSERT INTO htest2 VALUES (2); +SELECT a,b,c FROM htest2; + a | b | c +---+---+--- + 1 | 2 | 2 +(1 row) + +ALTER TABLE htest2 DROP COLUMN b; +ALTER TABLE htest2 ADD COLUMN b int; +INSERT INTO htest2 VALUES (4); +SELECT a,b,c FROM htest2; + a | b | c +---+---+--- + 1 | | 2 + 2 | 4 | 4 +(2 rows) + +DROP TABLE htest2 CASCADE; +-- a table can NOT have all columns hidden +CREATE TABLE htest3 (a serial HIDDEN, b int HIDDEN); -- error +ERROR: a table must have at least one visible column +-- inheritance with an additional single hidden column is possible +CREATE TABLE htest3 (a serial HIDDEN, b int); +SELECT * FROM htest3; + b +--- +(0 rows) + +CREATE TABLE htest3_1 (c int HIDDEN) INHERITS (htest3); +SELECT * FROM htest3_1; + b +--- +(0 rows) + +\d+ htest3_1 + Table "public.htest3_1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+-----------------------------------+---------+---------+--------------+------------- + a | integer | | not null | nextval('htest3_a_seq'::regclass) | hidden | plain | | + b | integer | | | | | plain | | + c | integer | | | | hidden | plain | | +Inherits: htest3 + +DROP TABLE htest3_1, htest3; +-- Ordering do not include the hidden column +CREATE TABLE t1 (col1 integer NOT NULL HIDDEN, col2 integer); +INSERT INTO t1 (col1, col2) VALUES (1, 6), (3, 4); +SELECT * FROM t1 ORDER BY 1 DESC; + col2 +------ + 6 + 4 +(2 rows) + +SELECT col1,col2 FROM t1 ORDER BY 2 DESC; + col1 | col2 +------+------ + 1 | 6 + 3 | 4 +(2 rows) + +-- unless it is called explicitly +SELECT * FROM t1 ORDER BY col1 DESC; + col2 +------ + 4 + 6 +(2 rows) + +DROP TABLE t1; +-- A table can be partitioned by an hidden column +CREATE TABLE measurement ( + city_id int not null, + logdate date not null hidden, + peaktemp int, + unitsales int +) PARTITION BY RANGE (logdate); +CREATE TABLE measurement_y2006m02 PARTITION OF measurement + FOR VALUES FROM ('2021-01-01') TO ('2021-03-01'); +CREATE TABLE measurement_y2006m03 PARTITION OF measurement + FOR VALUES FROM ('2021-03-01') TO ('2021-05-01'); +INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-02-28', 34, 4); +INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-04-12', 42, 6); +EXPLAIN VERBOSE SELECT * FROM measurement; + QUERY PLAN +---------------------------------------------------------------------------------------------------- + Append (cost=0.00..75.50 rows=3700 width=12) + -> Seq Scan on public.measurement_y2006m02 measurement_1 (cost=0.00..28.50 rows=1850 width=12) + Output: measurement_1.city_id, measurement_1.peaktemp, measurement_1.unitsales + -> Seq Scan on public.measurement_y2006m03 measurement_2 (cost=0.00..28.50 rows=1850 width=12) + Output: measurement_2.city_id, measurement_2.peaktemp, measurement_2.unitsales +(5 rows) + +SELECT * FROM measurement; + city_id | peaktemp | unitsales +---------+----------+----------- + 1 | 34 | 4 + 1 | 42 | 6 +(2 rows) + +SELECT city_id, logdate, peaktemp, unitsales FROM measurement; + city_id | logdate | peaktemp | unitsales +---------+------------+----------+----------- + 1 | 02-28-2021 | 34 | 4 + 1 | 04-12-2021 | 42 | 6 +(2 rows) + +DROP TABLE measurement CASCADE; +-- Same but unitsales is hidden instead of the partition key +CREATE TABLE measurement ( + city_id int not null, + logdate date not null, + peaktemp int, + unitsales int hidden +) PARTITION BY RANGE (logdate); +CREATE TABLE measurement_y2006m02 PARTITION OF measurement + FOR VALUES FROM ('2021-01-01') TO ('2021-03-01'); +CREATE TABLE measurement_y2006m03 PARTITION OF measurement + FOR VALUES FROM ('2021-03-01') TO ('2021-05-01'); +INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-02-28', 34, 4); +INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-04-12', 42, 6); +EXPLAIN VERBOSE SELECT * FROM measurement; + QUERY PLAN +---------------------------------------------------------------------------------------------------- + Append (cost=0.00..75.50 rows=3700 width=12) + -> Seq Scan on public.measurement_y2006m02 measurement_1 (cost=0.00..28.50 rows=1850 width=12) + Output: measurement_1.city_id, measurement_1.logdate, measurement_1.peaktemp + -> Seq Scan on public.measurement_y2006m03 measurement_2 (cost=0.00..28.50 rows=1850 width=12) + Output: measurement_2.city_id, measurement_2.logdate, measurement_2.peaktemp +(5 rows) + +SELECT * FROM measurement; + city_id | logdate | peaktemp +---------+------------+---------- + 1 | 02-28-2021 | 34 + 1 | 04-12-2021 | 42 +(2 rows) + +SELECT city_id, logdate, peaktemp, unitsales FROM measurement; + city_id | logdate | peaktemp | unitsales +---------+------------+----------+----------- + 1 | 02-28-2021 | 34 | 4 + 1 | 04-12-2021 | 42 | 6 +(2 rows) + +DROP TABLE measurement CASCADE; +-- Temporary tables can have invisible columns too. +CREATE TEMPORARY TABLE htest_tmp (col1 integer NOT NULL HIDDEN, col2 integer); +INSERT INTO htest_tmp (col1, col2) VALUES (1, 6), (3, 4); +SELECT * FROM htest_tmp ORDER BY 1 DESC; + col2 +------ + 6 + 4 +(2 rows) + +DROP TABLE htest_tmp; +-- composite types do not allow hidden column +CREATE TYPE compfoo AS (f1 int, f2 text hidden); -- error +ERROR: syntax error at or near "hidden" +LINE 1: CREATE TYPE compfoo AS (f1 int, f2 text hidden); + ^ +-- A table can use a composite type with a hidden column +CREATE TYPE compfoo AS (f1 int, f2 text); +CREATE TABLE htest4 ( + a int, + b compfoo HIDDEN +); +SELECT * FROM htest4; + a +--- +(0 rows) + +DROP TABLE htest4; +DROP TYPE compfoo; +-- Foreign key constraints can be defined on invisible columns, or invisible columns can be referenced. +CREATE TABLE t1 (col1 integer UNIQUE HIDDEN, col2 integer); +CREATE TABLE t2 (col1 integer PRIMARY KEY HIDDEN, col2 integer); +ALTER TABLE t1 ADD CONSTRAINT fk_t1_col1 FOREIGN KEY (col1) REFERENCES t2(col1); +ALTER TABLE t2 ADD CONSTRAINT fk_t2_col1 FOREIGN KEY (col1) REFERENCES t1(col1); +DROP TABLE t1, t2 CASCADE; +-- CHECK constraints can be defined on invisible columns. +CREATE TABLE t1 (col1 integer CHECK (col1 > 2) HIDDEN, col2 integer NOT NULL); +INSERT INTO t1 (col1, col2) VALUES (1, 6); -- error +ERROR: new row for relation "t1" violates check constraint "t1_col1_check" +DETAIL: Failing row contains (1, 6). +INSERT INTO t1 (col1, col2) VALUES (3, 6); +-- An index can reference a hidden column +CREATE INDEX ON t1 (col1); +ALTER TABLE t1 + ALTER COLUMN col1 TYPE bigint, + ADD COLUMN col3 int HIDDEN; +\d+ t1 + Table "public.t1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + col1 | bigint | | | | hidden | plain | | + col2 | integer | | not null | | | plain | | + col3 | integer | | | | | plain | | +Indexes: + "t1_col1_idx" btree (col1) +Check constraints: + "t1_col1_check" CHECK (col1 > 2) + +DROP TABLE t1; +-- View must not include the hidden column when not explicitly listed +CREATE VIEW viewt1 AS SELECT * FROM htest1; +\d viewt1 + View "public.viewt1" + Column | Type | Collation | Nullable | Default +--------+------+-----------+----------+--------- + b | text | | | + +SELECT * FROM viewt1; + b +------------ + htest1 one + htest1 two +(2 rows) + +-- If the hidden attribute on the column is removed the view result must not change +ALTER TABLE htest1 ALTER COLUMN a DROP HIDDEN; +SELECT * FROM viewt1; + b +------------ + htest1 one + htest1 two +(2 rows) + +ALTER TABLE htest1 ALTER COLUMN a SET HIDDEN; +DROP VIEW viewt1; +-- Materialized view must include the hidden column when explicitly listed +-- but the column is not hidden in the materialized view. +CREATE VIEW viewt1 AS SELECT a, b FROM htest1; +\d viewt1 + View "public.viewt1" + Column | Type | Collation | Nullable | Default +--------+--------+-----------+----------+--------- + a | bigint | | | + b | text | | | + +SELECT * FROM viewt1; + a | b +---+------------ + 1 | htest1 one + 2 | htest1 two +(2 rows) + +-- Materialized view must not include the hidden column when not explicitly listed +CREATE MATERIALIZED VIEW mviewt1 AS SELECT * FROM htest1; +\d mviewt1 + Materialized view "public.mviewt1" + Column | Type | Collation | Nullable | Default +--------+------+-----------+----------+--------- + b | text | | | + +REFRESH MATERIALIZED VIEW mviewt1; +SELECT * FROM mviewt1; + b +------------ + htest1 one + htest1 two +(2 rows) + +DROP MATERIALIZED VIEW mviewt1; +-- Materialized view must include the hidden column when explicitly listed +-- but the column is not hidden in the materialized view. +CREATE MATERIALIZED VIEW mviewt1 AS SELECT a, b FROM htest1; +\d mviewt1 + Materialized view "public.mviewt1" + Column | Type | Collation | Nullable | Default +--------+--------+-----------+----------+--------- + a | bigint | | | + b | text | | | + +REFRESH MATERIALIZED VIEW mviewt1; +SELECT * FROM mviewt1; + a | b +---+------------ + 1 | htest1 one + 2 | htest1 two +(2 rows) + +-- typed tables with hidden column is not supported +CREATE TYPE htest_type AS (f1 integer, f2 text, f3 bigint); +CREATE TABLE htest28 OF htest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED HIDDEN); -- error +ERROR: syntax error at or near "HIDDEN" +LINE 1: ...(f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED HIDDEN); + ^ +DROP TYPE htest_type CASCADE; +-- Prepared statements +PREPARE q1 AS SELECT * FROM htest1 WHERE a > $1; +EXECUTE q1(0); + b +------------ + htest1 one + htest1 two +(2 rows) + +ALTER TABLE htest1 ALTER COLUMN a DROP HIDDEN; +EXECUTE q1(0); -- error: cached plan change result type +ERROR: cached plan must not change result type +ALTER TABLE htest1 ALTER COLUMN a SET HIDDEN; +EXECUTE q1(0); + b +------------ + htest1 one + htest1 two +(2 rows) + +DEALLOCATE q1; +-- Cleanup +DROP TABLE htest0, htest1 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to view viewt1 +drop cascades to materialized view mviewt1 diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 99811570b7..0bfc0f1d20 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -498,14 +498,14 @@ TABLE itest8; (2 rows) \d+ itest8 - Table "public.itest8" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+----------------------------------+---------+--------------+------------- - f1 | integer | | | | plain | | - f2 | integer | | not null | generated always as identity | plain | | - f3 | integer | | not null | generated by default as identity | plain | | - f4 | bigint | | not null | generated always as identity | plain | | - f5 | bigint | | | | plain | | + Table "public.itest8" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+----------------------------------+---------+---------+--------------+------------- + f1 | integer | | | | | plain | | + f2 | integer | | not null | generated always as identity | | plain | | + f3 | integer | | not null | generated by default as identity | | plain | | + f4 | bigint | | not null | generated always as identity | | plain | | + f5 | bigint | | | | | plain | | \d itest8_f2_seq Sequence "public.itest8_f2_seq" diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 2d49e765de..391736779e 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1050,13 +1050,13 @@ ALTER TABLE inhts RENAME aa TO aaa; -- to be failed ERROR: cannot rename inherited column "aa" ALTER TABLE inhts RENAME d TO dd; \d+ inhts - Table "public.inhts" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - aa | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | - dd | integer | | | | plain | | + Table "public.inhts" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + aa | integer | | | | | plain | | + b | integer | | | | | plain | | + c | integer | | | | | plain | | + dd | integer | | | | | plain | | Inherits: inht1, inhs1 @@ -1069,14 +1069,14 @@ NOTICE: merging multiple inherited definitions of column "aa" NOTICE: merging multiple inherited definitions of column "b" ALTER TABLE inht1 RENAME aa TO aaa; \d+ inht4 - Table "public.inht4" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - aaa | integer | | | | plain | | - b | integer | | | | plain | | - x | integer | | | | plain | | - y | integer | | | | plain | | - z | integer | | | | plain | | + Table "public.inht4" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + aaa | integer | | | | | plain | | + b | integer | | | | | plain | | + x | integer | | | | | plain | | + y | integer | | | | | plain | | + z | integer | | | | | plain | | Inherits: inht2, inht3 @@ -1086,14 +1086,14 @@ ALTER TABLE inht1 RENAME aaa TO aaaa; ALTER TABLE inht1 RENAME b TO bb; -- to be failed ERROR: cannot rename inherited column "b" \d+ inhts - Table "public.inhts" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - aaaa | integer | | | | plain | | - b | integer | | | | plain | | - x | integer | | | | plain | | - c | integer | | | | plain | | - d | integer | | | | plain | | + Table "public.inhts" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + aaaa | integer | | | | | plain | | + b | integer | | | | | plain | | + x | integer | | | | | plain | | + c | integer | | | | | plain | | + d | integer | | | | | plain | | Inherits: inht2, inhs1 @@ -1133,33 +1133,33 @@ drop cascades to table inht4 CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2)); CREATE TABLE test_constraints_inh () INHERITS (test_constraints); \d+ test_constraints - Table "public.test_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+--------------+------------- - id | integer | | | | plain | | - val1 | character varying | | | | extended | | - val2 | integer | | | | plain | | + Table "public.test_constraints" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+----------+--------------+------------- + id | integer | | | | | plain | | + val1 | character varying | | | | | extended | | + val2 | integer | | | | | plain | | Indexes: "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2) Child tables: test_constraints_inh ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; \d+ test_constraints - Table "public.test_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+--------------+------------- - id | integer | | | | plain | | - val1 | character varying | | | | extended | | - val2 | integer | | | | plain | | + Table "public.test_constraints" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+----------+--------------+------------- + id | integer | | | | | plain | | + val1 | character varying | | | | | extended | | + val2 | integer | | | | | plain | | Child tables: test_constraints_inh \d+ test_constraints_inh - Table "public.test_constraints_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+--------------+------------- - id | integer | | | | plain | | - val1 | character varying | | | | extended | | - val2 | integer | | | | plain | | + Table "public.test_constraints_inh" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+----------+--------------+------------- + id | integer | | | | | plain | | + val1 | character varying | | | | | extended | | + val2 | integer | | | | | plain | | Inherits: test_constraints DROP TABLE test_constraints_inh; @@ -1170,27 +1170,27 @@ CREATE TABLE test_ex_constraints ( ); CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); \d+ test_ex_constraints - Table "public.test_ex_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------+-----------+----------+---------+---------+--------------+------------- - c | circle | | | | plain | | + Table "public.test_ex_constraints" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+--------+-----------+----------+---------+---------+---------+--------------+------------- + c | circle | | | | | plain | | Indexes: "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&) Child tables: test_ex_constraints_inh ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; \d+ test_ex_constraints - Table "public.test_ex_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------+-----------+----------+---------+---------+--------------+------------- - c | circle | | | | plain | | + Table "public.test_ex_constraints" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+--------+-----------+----------+---------+---------+---------+--------------+------------- + c | circle | | | | | plain | | Child tables: test_ex_constraints_inh \d+ test_ex_constraints_inh - Table "public.test_ex_constraints_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------+-----------+----------+---------+---------+--------------+------------- - c | circle | | | | plain | | + Table "public.test_ex_constraints_inh" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+--------+-----------+----------+---------+---------+---------+--------------+------------- + c | circle | | | | | plain | | Inherits: test_ex_constraints DROP TABLE test_ex_constraints_inh; @@ -1200,37 +1200,37 @@ CREATE TABLE test_primary_constraints(id int PRIMARY KEY); CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); \d+ test_primary_constraints - Table "public.test_primary_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id | integer | | not null | | plain | | + Table "public.test_primary_constraints" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + id | integer | | not null | | | plain | | Indexes: "test_primary_constraints_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) \d+ test_foreign_constraints - Table "public.test_foreign_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id1 | integer | | | | plain | | + Table "public.test_foreign_constraints" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + id1 | integer | | | | | plain | | Foreign-key constraints: "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) Child tables: test_foreign_constraints_inh ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; \d+ test_foreign_constraints - Table "public.test_foreign_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id1 | integer | | | | plain | | + Table "public.test_foreign_constraints" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + id1 | integer | | | | | plain | | Child tables: test_foreign_constraints_inh \d+ test_foreign_constraints_inh - Table "public.test_foreign_constraints_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id1 | integer | | | | plain | | + Table "public.test_foreign_constraints_inh" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + id1 | integer | | | | | plain | | Inherits: test_foreign_constraints DROP TABLE test_foreign_constraints_inh; diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index 5063a3dc22..c99c38fcfa 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -163,11 +163,11 @@ create rule irule3 as on insert to inserttest2 do also insert into inserttest (f4[1].if1, f4[1].if2[2]) select new.f1, new.f2; \d+ inserttest2 - Table "public.inserttest2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------+-----------+----------+---------+----------+--------------+------------- - f1 | bigint | | | | plain | | - f2 | text | | | | extended | | + Table "public.inserttest2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+--------+-----------+----------+---------+---------+----------+--------------+------------- + f1 | bigint | | | | | plain | | + f2 | text | | | | | extended | | Rules: irule1 AS ON INSERT TO inserttest2 DO INSERT INTO inserttest (f3.if2[1], f3.if2[2]) @@ -469,11 +469,11 @@ from hash_parted order by part; -- test \d+ output on a table which has both partitioned and unpartitioned -- partitions \d+ list_parted - Partitioned table "public.list_parted" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Partitioned table "public.list_parted" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition key: LIST (lower(a)) Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'), part_cc_dd FOR VALUES IN ('cc', 'dd'), @@ -491,10 +491,10 @@ drop table hash_parted; create table list_parted (a int) partition by list (a); create table part_default partition of list_parted default; \d+ part_default - Table "public.part_default" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | + Table "public.part_default" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | Partition of: list_parted DEFAULT No partition constraint @@ -874,11 +874,11 @@ create table mcrparted6_common_ge_10 partition of mcrparted for values from ('co create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue); create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue, maxvalue); \d+ mcrparted - Partitioned table "public.mcrparted" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Partitioned table "public.mcrparted" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition key: RANGE (a, b) Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE), mcrparted2_b FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE), @@ -890,74 +890,74 @@ Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVAL mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) \d+ mcrparted1_lt_b - Table "public.mcrparted1_lt_b" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted1_lt_b" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition of: mcrparted FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a < 'b'::text)) \d+ mcrparted2_b - Table "public.mcrparted2_b" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted2_b" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'b'::text) AND (a < 'c'::text)) \d+ mcrparted3_c_to_common - Table "public.mcrparted3_c_to_common" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted3_c_to_common" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'c'::text) AND (a < 'common'::text)) \d+ mcrparted4_common_lt_0 - Table "public.mcrparted4_common_lt_0" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted4_common_lt_0" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('common', MINVALUE) TO ('common', 0) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b < 0)) \d+ mcrparted5_common_0_to_10 - Table "public.mcrparted5_common_0_to_10" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted5_common_0_to_10" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('common', 0) TO ('common', 10) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 0) AND (b < 10)) \d+ mcrparted6_common_ge_10 - Table "public.mcrparted6_common_ge_10" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted6_common_ge_10" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 10)) \d+ mcrparted7_gt_common_lt_d - Table "public.mcrparted7_gt_common_lt_d" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted7_gt_common_lt_d" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a > 'common'::text) AND (a < 'd'::text)) \d+ mcrparted8_ge_d - Table "public.mcrparted8_ge_d" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted8_ge_d" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | integer | | | | | plain | | Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'd'::text)) diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 930ce8597a..cda1bf337d 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -2813,34 +2813,34 @@ CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap; CREATE VIEW view_heap_psql AS SELECT f1 from tbl_heap_psql; CREATE MATERIALIZED VIEW mat_view_heap_psql USING heap_psql AS SELECT f1 from tbl_heap_psql; \d+ tbl_heap_psql - Table "tableam_display.tbl_heap_psql" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+----------------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | | | plain | | - f2 | character(100) | | | | extended | | + Table "tableam_display.tbl_heap_psql" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+----------------+-----------+----------+---------+---------+----------+--------------+------------- + f1 | integer | | | | | plain | | + f2 | character(100) | | | | | extended | | \d+ tbl_heap - Table "tableam_display.tbl_heap" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+----------------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | | | plain | | - f2 | character(100) | | | | extended | | + Table "tableam_display.tbl_heap" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+----------------+-----------+----------+---------+---------+----------+--------------+------------- + f1 | integer | | | | | plain | | + f2 | character(100) | | | | | extended | | \set HIDE_TABLEAM off \d+ tbl_heap_psql - Table "tableam_display.tbl_heap_psql" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+----------------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | | | plain | | - f2 | character(100) | | | | extended | | + Table "tableam_display.tbl_heap_psql" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+----------------+-----------+----------+---------+---------+----------+--------------+------------- + f1 | integer | | | | | plain | | + f2 | character(100) | | | | | extended | | Access method: heap_psql \d+ tbl_heap - Table "tableam_display.tbl_heap" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+----------------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | | | plain | | - f2 | character(100) | | | | extended | | + Table "tableam_display.tbl_heap" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+----------------+-----------+----------+---------+---------+----------+--------------+------------- + f1 | integer | | | | | plain | | + f2 | character(100) | | | | | extended | | Access method: heap -- AM is displayed for tables, indexes and materialized views. diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 82bce9be09..117139c3bc 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -76,11 +76,11 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall (1 row) \d+ testpub_tbl2 - Table "public.testpub_tbl2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | plain | | - data | text | | | | extended | | + Table "public.testpub_tbl2" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+------------------------------------------+---------+----------+--------------+------------- + id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | | plain | | + data | text | | | | | extended | | Indexes: "testpub_tbl2_pkey" PRIMARY KEY, btree (id) Publications: @@ -213,22 +213,22 @@ ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1; ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk; ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1; \d+ pub_test.testpub_nopk - Table "pub_test.testpub_nopk" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - foo | integer | | | | plain | | - bar | integer | | | | plain | | + Table "pub_test.testpub_nopk" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + foo | integer | | | | | plain | | + bar | integer | | | | | plain | | Publications: "testpib_ins_trunct" "testpub_default" "testpub_fortbl" \d+ testpub_tbl1 - Table "public.testpub_tbl1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | - data | text | | | | extended | | + Table "public.testpub_tbl1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+------------------------------------------+---------+----------+--------------+------------- + id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | | plain | | + data | text | | | | | extended | | Indexes: "testpub_tbl1_pkey" PRIMARY KEY, btree (id) Publications: @@ -250,11 +250,11 @@ ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk; ERROR: relation "testpub_nopk" is not part of the publication \d+ testpub_tbl1 - Table "public.testpub_tbl1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | - data | text | | | | extended | | + Table "public.testpub_tbl1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+------------------------------------------+---------+----------+--------------+------------- + id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | | plain | | + data | text | | | | | extended | | Indexes: "testpub_tbl1_pkey" PRIMARY KEY, btree (id) Publications: diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out index 79002197a7..e9cb02eb01 100644 --- a/src/test/regress/expected/replica_identity.out +++ b/src/test/regress/expected/replica_identity.out @@ -153,13 +153,13 @@ SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; (1 row) \d+ test_replica_identity - Table "public.test_replica_identity" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | plain | | - keya | text | | not null | | extended | | - keyb | text | | not null | | extended | | - nonkey | text | | | | extended | | + Table "public.test_replica_identity" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------------------------------------------------+---------+----------+--------------+------------- + id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | | plain | | + keya | text | | not null | | | extended | | + keyb | text | | not null | | | extended | | + nonkey | text | | | | | extended | | Indexes: "test_replica_identity_pkey" PRIMARY KEY, btree (id) "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 89397e41f0..66220f48ee 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -938,14 +938,14 @@ CREATE POLICY pp1 ON part_document AS PERMISSIVE CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave USING (cid < 55); \d+ part_document - Partitioned table "regress_rls_schema.part_document" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------+---------+-----------+----------+---------+----------+--------------+------------- - did | integer | | | | plain | | - cid | integer | | | | plain | | - dlevel | integer | | not null | | plain | | - dauthor | name | | | | plain | | - dtitle | text | | | | extended | | + Partitioned table "regress_rls_schema.part_document" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +---------+---------+-----------+----------+---------+---------+----------+--------------+------------- + did | integer | | | | | plain | | + cid | integer | | | | | plain | | + dlevel | integer | | not null | | | plain | | + dauthor | name | | | | | plain | | + dtitle | text | | | | | extended | | Partition key: RANGE (cid) Policies: POLICY "pp1" diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2fa00a3c29..a744b84158 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3167,11 +3167,11 @@ select * from rules_log; create rule r3 as on delete to rules_src do notify rules_src_deletion; \d+ rules_src - Table "public.rules_src" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | | | plain | | - f2 | integer | | | | plain | | + Table "public.rules_src" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + f1 | integer | | | | | plain | | + f2 | integer | | | | | plain | | Rules: r1 AS ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) @@ -3187,11 +3187,11 @@ Rules: create rule r4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2; create rule r5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1; \d+ rules_src - Table "public.rules_src" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | | | plain | | - f2 | integer | | | | plain | | + Table "public.rules_src" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + f1 | integer | | | | | plain | | + f2 | integer | | | | | plain | | Rules: r1 AS ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) @@ -3218,11 +3218,11 @@ create rule rr as on update to rule_t1 do instead UPDATE rule_dest trgt SET (f2[1], f1, tag) = (SELECT new.f2, new.f1, 'updated'::varchar) WHERE trgt.f1 = new.f1 RETURNING new.*; \d+ rule_t1 - Table "public.rule_t1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | | | plain | | - f2 | integer | | | | plain | | + Table "public.rule_t1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + f1 | integer | | | | | plain | | + f2 | integer | | | | | plain | | Rules: rr AS ON UPDATE TO rule_t1 DO INSTEAD UPDATE rule_dest trgt SET (f2[1], f1, tag) = ( SELECT new.f2, diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index c60ba45aba..9fc0fbb8ed 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -151,11 +151,11 @@ SELECT stxname, stxdndistinct, stxddependencies, stxdmcv ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1; \d+ ab1 - Table "public.ab1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | + Table "public.ab1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | + b | integer | | | | | plain | | Statistics objects: "public.ab1_a_b_stats" ON a, b FROM ab1 diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 5d124cf96f..497c27bf7e 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -3476,10 +3476,10 @@ create trigger parenttrig after insert on child for each row execute procedure f(); alter trigger parenttrig on parent rename to anothertrig; \d+ child - Table "public.child" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | + Table "public.child" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | Triggers: parenttrig AFTER INSERT ON child FOR EACH ROW EXECUTE FUNCTION f() Inherits: parent diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index c809f88f54..2cab568340 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -743,14 +743,14 @@ DROP TRIGGER d15_insert_trig ON part_d_15_20; :init_range_parted; create table part_def partition of range_parted default; \d+ part_def - Table "public.part_def" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | bigint | | | | plain | | - c | numeric | | | | main | | - d | integer | | | | plain | | - e | character varying | | | | extended | | + Table "public.part_def" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+-------------------+-----------+----------+---------+---------+----------+--------------+------------- + a | text | | | | | extended | | + b | bigint | | | | | plain | | + c | numeric | | | | | main | | + d | integer | | | | | plain | | + e | character varying | | | | | extended | | Partition of: range_parted DEFAULT Partition constraint: (NOT ((a IS NOT NULL) AND (b IS NOT NULL) AND (((a = 'a'::text) AND (b >= '1'::bigint) AND (b < '10'::bigint)) OR ((a = 'a'::text) AND (b >= '10'::bigint) AND (b < '20'::bigint)) OR ((a = 'b'::text) AND (b >= '1'::bigint) AND (b < '10'::bigint)) OR ((a = 'b'::text) AND (b >= '10'::bigint) AND (b < '20'::bigint)) OR ((a = 'b'::text) AND (b >= '20'::bigint) AND (b < '30'::bigint))))) diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source index e7629d470e..795d9e2c30 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -330,10 +330,10 @@ Indexes: Number of partitions: 2 (Use \d+ to list them.) \d+ testschema.part - Partitioned table "testschema.part" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | + Partitioned table "testschema.part" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | Partition key: LIST (a) Indexes: "part_a_idx" btree (a), tablespace "regress_tblspace" @@ -350,10 +350,10 @@ Indexes: "part1_a_idx" btree (a), tablespace "regress_tblspace" \d+ testschema.part1 - Table "testschema.part1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | + Table "testschema.part1" + Column | Type | Collation | Nullable | Default | Visible | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+---------+--------------+------------- + a | integer | | | | | plain | | Partition of: testschema.part FOR VALUES IN (1) Partition constraint: ((a IS NOT NULL) AND (a = 1)) Indexes: diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 7be89178f0..aa34348b7e 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -58,7 +58,7 @@ test: create_index create_index_spgist create_view index_including index_includi # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse +test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse hidden # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/sql/hidden.sql b/src/test/regress/sql/hidden.sql new file mode 100644 index 0000000000..567479c534 --- /dev/null +++ b/src/test/regress/sql/hidden.sql @@ -0,0 +1,217 @@ +-- sanity check of system catalog +SELECT attrelid, attname, attishidden FROM pg_attribute WHERE attishidden; + + +CREATE TABLE htest0 (a int PRIMARY KEY, b text NOT NULL HIDDEN); +INSERT INTO htest0 (a, b) VALUES (1, 'htest0 one'); +INSERT INTO htest0 (a, b) VALUES (2, 'htest0 two'); +CREATE TABLE htest1 (a bigserial PRIMARY KEY HIDDEN, b text); +-- Insert without named column must exclude the hidden column +INSERT INTO htest1 VALUES ('htest1 one'); +INSERT INTO htest1 VALUES ('htest1 two'); + +SELECT table_name, column_name, column_default, is_nullable, is_hidden FROM information_schema.columns WHERE table_name LIKE 'htest_' ORDER BY 1, 2; + +SELECT table_name, column_name, is_hidden FROM information_schema.columns WHERE is_hidden = 'YES' ORDER BY 1, 2; + +SELECT attrelid::regclass, attname, attishidden FROM pg_attribute WHERE attishidden; + +\d+ htest1 + +-- DROP/SET hidden attribute +ALTER TABLE htest0 ALTER COLUMN b DROP HIDDEN; + +\d+ htest0 + +ALTER TABLE htest0 ALTER COLUMN b SET HIDDEN; + +-- Hidden column are not expandable and must not be returned +SELECT * FROM htest0; -- return only column a +SELECT * FROM htest1; -- return only column b + +-- CTEs based on SELECT * only have visible column returned +WITH foo AS (SELECT * FROM htest1) SELECT * FROM foo; -- Only column b is returned here + +-- inheritance, the hidden attribute is inherited +CREATE TABLE htest1_1 () INHERITS (htest1); +SELECT * FROM htest1_1; +\d htest1_1 +INSERT INTO htest1_1 VALUES ('htest1 three'); +SELECT * FROM htest1_1; +SELECT * FROM htest1; + +-- hidden column must be explicitely named to be returned +SELECT a,b FROM htest1_1; +SELECT a,b FROM htest1; +DROP TABLE htest1_1; + +-- Default CREATE TABLE ... LIKE includes hidden columns, and they are not hidden in the new table. +CREATE TABLE htest_like1 (LIKE htest1); +\d+ htest_like1 +-- CREATE TABLE ... LIKE includes hidden columns, and they are hidden if requested +CREATE TABLE htest_like2 (LIKE htest1 INCLUDING HIDDEN); +\d+ htest_like2 +CREATE TABLE htest_like3 (LIKE htest1 INCLUDING ALL); +\d+ htest_like3 +DROP TABLE htest_like1, htest_like2, htest_like3; + +-- Insert without named column with and a not null hidden column must have a default value +INSERT INTO htest0 VALUES (3); -- error +ALTER TABLE htest0 ALTER COLUMN b SET DEFAULT 'unknown'; +INSERT INTO htest0 VALUES (3); +-- Same with COPY +COPY htest0 TO stdout; +COPY htest0 (a, b) TO stdout; +COPY htest0 FROM stdin; +4 +5 +\. +SELECT a,b FROM htest0; + +-- same but with drop/add the column between hidden columns (virtual columns can be made hidden) +CREATE TABLE htest2 (a serial HIDDEN, b int, c int GENERATED ALWAYS AS (a * 2) STORED HIDDEN); +INSERT INTO htest2 VALUES (2); +SELECT a,b,c FROM htest2; +ALTER TABLE htest2 DROP COLUMN b; +ALTER TABLE htest2 ADD COLUMN b int; +INSERT INTO htest2 VALUES (4); +SELECT a,b,c FROM htest2; +DROP TABLE htest2 CASCADE; + +-- a table can NOT have all columns hidden +CREATE TABLE htest3 (a serial HIDDEN, b int HIDDEN); -- error + +-- inheritance with an additional single hidden column is possible +CREATE TABLE htest3 (a serial HIDDEN, b int); +SELECT * FROM htest3; +CREATE TABLE htest3_1 (c int HIDDEN) INHERITS (htest3); +SELECT * FROM htest3_1; +\d+ htest3_1 +DROP TABLE htest3_1, htest3; + +-- Ordering do not include the hidden column +CREATE TABLE t1 (col1 integer NOT NULL HIDDEN, col2 integer); +INSERT INTO t1 (col1, col2) VALUES (1, 6), (3, 4); +SELECT * FROM t1 ORDER BY 1 DESC; +SELECT col1,col2 FROM t1 ORDER BY 2 DESC; +-- unless it is called explicitly +SELECT * FROM t1 ORDER BY col1 DESC; +DROP TABLE t1; + +-- A table can be partitioned by an hidden column +CREATE TABLE measurement ( + city_id int not null, + logdate date not null hidden, + peaktemp int, + unitsales int +) PARTITION BY RANGE (logdate); +CREATE TABLE measurement_y2006m02 PARTITION OF measurement + FOR VALUES FROM ('2021-01-01') TO ('2021-03-01'); +CREATE TABLE measurement_y2006m03 PARTITION OF measurement + FOR VALUES FROM ('2021-03-01') TO ('2021-05-01'); +INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-02-28', 34, 4); +INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-04-12', 42, 6); +EXPLAIN VERBOSE SELECT * FROM measurement; +SELECT * FROM measurement; +SELECT city_id, logdate, peaktemp, unitsales FROM measurement; +DROP TABLE measurement CASCADE; +-- Same but unitsales is hidden instead of the partition key +CREATE TABLE measurement ( + city_id int not null, + logdate date not null, + peaktemp int, + unitsales int hidden +) PARTITION BY RANGE (logdate); +CREATE TABLE measurement_y2006m02 PARTITION OF measurement + FOR VALUES FROM ('2021-01-01') TO ('2021-03-01'); +CREATE TABLE measurement_y2006m03 PARTITION OF measurement + FOR VALUES FROM ('2021-03-01') TO ('2021-05-01'); +INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-02-28', 34, 4); +INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-04-12', 42, 6); +EXPLAIN VERBOSE SELECT * FROM measurement; +SELECT * FROM measurement; +SELECT city_id, logdate, peaktemp, unitsales FROM measurement; +DROP TABLE measurement CASCADE; + +-- Temporary tables can have invisible columns too. +CREATE TEMPORARY TABLE htest_tmp (col1 integer NOT NULL HIDDEN, col2 integer); +INSERT INTO htest_tmp (col1, col2) VALUES (1, 6), (3, 4); +SELECT * FROM htest_tmp ORDER BY 1 DESC; +DROP TABLE htest_tmp; + +-- composite types do not allow hidden column +CREATE TYPE compfoo AS (f1 int, f2 text hidden); -- error + +-- A table can use a composite type with a hidden column +CREATE TYPE compfoo AS (f1 int, f2 text); +CREATE TABLE htest4 ( + a int, + b compfoo HIDDEN +); +SELECT * FROM htest4; +DROP TABLE htest4; +DROP TYPE compfoo; + +-- Foreign key constraints can be defined on invisible columns, or invisible columns can be referenced. +CREATE TABLE t1 (col1 integer UNIQUE HIDDEN, col2 integer); +CREATE TABLE t2 (col1 integer PRIMARY KEY HIDDEN, col2 integer); +ALTER TABLE t1 ADD CONSTRAINT fk_t1_col1 FOREIGN KEY (col1) REFERENCES t2(col1); +ALTER TABLE t2 ADD CONSTRAINT fk_t2_col1 FOREIGN KEY (col1) REFERENCES t1(col1); +DROP TABLE t1, t2 CASCADE; + +-- CHECK constraints can be defined on invisible columns. +CREATE TABLE t1 (col1 integer CHECK (col1 > 2) HIDDEN, col2 integer NOT NULL); +INSERT INTO t1 (col1, col2) VALUES (1, 6); -- error +INSERT INTO t1 (col1, col2) VALUES (3, 6); +-- An index can reference a hidden column +CREATE INDEX ON t1 (col1); +ALTER TABLE t1 + ALTER COLUMN col1 TYPE bigint, + ADD COLUMN col3 int HIDDEN; +\d+ t1 +DROP TABLE t1; + +-- View must not include the hidden column when not explicitly listed +CREATE VIEW viewt1 AS SELECT * FROM htest1; +\d viewt1 +SELECT * FROM viewt1; +-- If the hidden attribute on the column is removed the view result must not change +ALTER TABLE htest1 ALTER COLUMN a DROP HIDDEN; +SELECT * FROM viewt1; +ALTER TABLE htest1 ALTER COLUMN a SET HIDDEN; +DROP VIEW viewt1; +-- Materialized view must include the hidden column when explicitly listed +-- but the column is not hidden in the materialized view. +CREATE VIEW viewt1 AS SELECT a, b FROM htest1; +\d viewt1 +SELECT * FROM viewt1; + +-- Materialized view must not include the hidden column when not explicitly listed +CREATE MATERIALIZED VIEW mviewt1 AS SELECT * FROM htest1; +\d mviewt1 +REFRESH MATERIALIZED VIEW mviewt1; +SELECT * FROM mviewt1; +DROP MATERIALIZED VIEW mviewt1; +-- Materialized view must include the hidden column when explicitly listed +-- but the column is not hidden in the materialized view. +CREATE MATERIALIZED VIEW mviewt1 AS SELECT a, b FROM htest1; +\d mviewt1 +REFRESH MATERIALIZED VIEW mviewt1; +SELECT * FROM mviewt1; + +-- typed tables with hidden column is not supported +CREATE TYPE htest_type AS (f1 integer, f2 text, f3 bigint); +CREATE TABLE htest28 OF htest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED HIDDEN); -- error +DROP TYPE htest_type CASCADE; + +-- Prepared statements +PREPARE q1 AS SELECT * FROM htest1 WHERE a > $1; +EXECUTE q1(0); +ALTER TABLE htest1 ALTER COLUMN a DROP HIDDEN; +EXECUTE q1(0); -- error: cached plan change result type +ALTER TABLE htest1 ALTER COLUMN a SET HIDDEN; +EXECUTE q1(0); +DEALLOCATE q1; + +-- Cleanup +DROP TABLE htest0, htest1 CASCADE;