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

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. 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/