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

On 10/15/21 2:51 PM, Bruce Momjian wrote:

On Fri, Oct 15, 2021 at 11:32:53AM +0200, Laurenz Albe wrote:

On Thu, 2021-10-14 at 13:16 +0200, Gilles Darold wrote:

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.

When I read your proposal, I had strangely mixed feelings:
"This is cute!" versus "Do we need that?".  After some thinking, I think
that it boils down to the following:

That feature is appealing to people who type SQL statements into psql,
which is probably the majority of the readers on this list.  It is
immediately clear that this can be used for all kinds of nice things.

On the other hand: a relational database is not a spreadsheet, where
I want to hide or highlight columns.  Sure, the interactive user may
use it in that way, but that is not the target of a relational database.
Databases usually are not user visible, but used by an application.
So the appeal for the interactive user is really pretty irrelevant.

Now this patch makes certain things easier, but it adds no substantially
new functionality: I can exclude a column from display as it is, simply
by listing all the other columns.  Sure, that's a pain for the interactive
user, but it is irrelevant for a query in an application.

This together with the fact that it poses complicated questions when
we dig deeper, such as "what about whole-row references?", tilts my vote.
If it were for free, I would say +1.  But given the ratio of potential
headache versus added real-life benefit, I find myself voting -1.

I can see the usefulness of this, though UNEXPANDED seems clearer. 
However, it also is likely to confuse someone who does SELECT * and then
can't figure out why another query is showing a column that doesn't
appear in SELECT *.  I do think SELECT * EXCEPT is the better and less
confusing solution.  I can imagine people using different EXCEPT columns
for different queries, which HIDDEN/UNEXPANDED does not allow.  I
frankly can't think of a single case where output is specified at the
DDL level.

Why is this not better addressed by creating a view on the original
table, even perhaps renaming the original table and create a view using
the old table name.

That's pretty much my feeling. This seems a bit too cute.


I have a little function I use to create a skeleton query on tables with
lots of columns just so I can delete a few and leave the rest, a problem
that would be solved neatly by the EXCEPT proposal and not but the
HIDDEN proposal.

I have nothing against seeing the EXCEPT included into core except that this is a big sprain to the SQL standard and I doubt that personally I will used it for portability reason. Saying that, by this syntax we will also encourage the use of SELECT * which is in contradiction with the common opinion.

But again I don't think this is the same feature, the only thing where SELECT * EXCEPT is useful is for a single non portable statement. It does not help to extend PostgreSQL through extensions or can solves application migration issues. I'm a bit surprise by this confusion with the EXCEPT syntax.

-- 
Gilles Darold