Re: [PATCH] Proposal for HIDDEN/INVISIBLE column - Mailing list pgsql-hackers

čt 14. 10. 2021 v 13:17 odesílatel Gilles Darold <gilles@migops.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@gmail.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/
>