Re: [PATCH] Proposal for HIDDEN/INVISIBLE column

čt 14. 10. 2021 v 13:17 odesílatel Gilles Darold <gilles(at)migops(dot)com> napsal:
>
> Hi,
>
>
> Here is a proposal to implement HIDDEN columns feature in PostgreSQL.
>
> The user defined columns are always visible in the PostgreSQL. If user
> wants to hide some column(s) from a SELECT * returned values then the
> hidden columns feature is useful. Hidden column can always be used and
> returned by explicitly referring it in the query.
>
> I agree that views are done for that or that using a SELECT * is a bad
> practice
> but sometime we could need to "technically" prevent some columns to be part
> of a star expansion and nbot be forced to use view+rules.

Just to remind here, there was recently a proposal to handle this
problem another way - provide a list of columns to skip for "star
selection" aka "SELECT * EXCEPT col1...".

https://postgrespro.com/list/id/d51371a2-f221-1cf3-4a7d-b2242d4dafdb(at)gmail(dot)com

> For example when
> upgrading a database schema where a column have been added to a table,
> this will break any old version of the application that is using a
> SELECT * on
> this table. Being able to "hide" this column to such query will make
> migration
> easier.
>
> An other common use case for this feature is to implements temporal tables
> or row versionning. On my side I see a direct interest in Oracle to
> PostgreSQL
> migration to emulate the ROWID system column without the hassle of creating
> views, it will save lot of time.
>
> The other advantage over views is that the hidden column can still be used
> in JOIN, WHERE, ORDER BY or GROUP BY clause which is not possible otherwise.
> I don't talk about writing to complex view which would require a RULE.
>
> Hidden column is not part of the SQL standard but is implemented in all
> other
> RDBMS which is also called invisible columns [1] [2] [3] [4]. In all
> these RDBMS
> the feature is quite the same.
>
> [1] https://www.ibm.com/docs/en/db2/10.5?topic=concepts-hidden-columns
> [2] https://oracle-base.com/articles/12c/invisible-columns-12cr1
> [3]
> https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15
> [4] https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html
>
>
> Here is the full description of the proposal with a patch attached that
> implements
> the feature:
>
> 1) Creating hidden columns:
>
> A column visibility attribute is added to the column definition
> of CREATE TABLE and ALTER TABLE statements. For example:
>
> CREATE TABLE htest1 (a bigserial HIDDEN, b text);
>
> ALTER TABLE htest1 ADD COLUMN c integer HIDDEN;
>
> Columns are visible by default.
>
> 2) Altering column visibility attribute:
>
> The ALTER TABLE statement can be used to change hidden columns to not
> hidden and the opposite. Example:
>
> ALTER TABLE htest1 ALTER COLUMN c DROP HIDDEN;
>
> 3) Insert and hidden columns:
>
> If the column list of INSERT or COPY statements is empty
> then while expanding column list hidden columns are NOT
> included. DEFAULT or NULL values are inserted for hidden
> columns in this case. Hidden column should be explicitly
> referenced in the column list of INSERT and COPY statement
> to insert a value.
>
> Example:
>
> -- Value 'one' is stored in column b and 1 in hidden column.
> INSERT INTO t1 VALUES ('one');
>
> -- Value 2 is stored in hidden column and 'two' in b.
> INSERT INTO htest1 (a, b) VALUES (2, 'two');
>
> 4) Star expansion for SELECT * statements:
>
> Hidden columns are not included in a column list while
> expanding wild card '*' in the SELECT statement.
>
> Example:
>
> SELECT * FROM htest1;
> b
> ------
> one
> two
>
> Hidden columns are accessible when explicitly referenced
> in the query.
>
> Example:
> SELECT f1, f2 FROM t1;
> a | b
> ------+------
> 1 | one
> 2 | two
>
> 5) psql extended describe lists hidden columns.
>
> postgres=# \d+ htest1
> Table "public.htest1"
> Column | Type | Collation | Nullable | Default | Visible | ...
> --------+--------+-----------+----------+------------+---------+ ...
> a | bigint | | not null | nextval... | hidden | ...
> b | text | | | | | ...
>
> 6) When a column is flagged as hidden the attishidden column value of
> table pg_attribute is set to true.
>
> 7) For hidden attributes, column is_hidden of table
> information_schema.columns
> is set to YES. By default the column is visible and the value is 'NO'.
>
> For a complete description of the feature, see chapter "Hidden columns" in
> file doc/src/sgml/ddl.sgml after applying the patch.
>
>
> The patch is a full implementation of this feture except that I sill have to
> prevent a ALTER ... SET HIDDEN to be applied of there is no more visible
> columns in the table after the change. I will do that when I will recover
> more time.
>
> I have choose HIDDEN vs INVISIBLE but this could be a minor change or
> we could use NOT EXPANDABLE. Personnaly I prefer the HIDDEN attribute.
>
>
> Any though and interest in this feature?
>
> --
> Gilles Darold
> http://www.migops.com/
>