[PATCH] Proposal for HIDDEN/INVISIBLE column

Hi Gilles,

Any though and interest in this feature?

Personally, I wouldn't call this feature particularly useful. `SELECT
*` is intended for people who are working with DBMS directly e.g. via
psql and want to see ALL columns. The applications should never use
`SELECT *`. So I can't see any real benefits of adding this feature to
PostgreSQL. It will only make the existing code and the existing user
interface even more complicated than they are now.

Also, every yet another feature is x N corner cases when this feature
works with other N features of PostgreSQL. How should it work with
partitioned or inherited tables? Or with logical replication? With
pg_dump? With COPY?

So all in all, -1. This being said, I very much appreciate your
attempt to improve PostgreSQL. However next time before writing the
code I suggest submitting an RFC first.

--
Best regards,
Aleksander Alekseev

Hi again,

So all in all, -1. [...]

Here is something I would like to add:

1. As far as I know, "all the rest of DBMS have this" was never a good
argument in the PostgreSQL community. Generally, using it will turn people
against you.
2. I recall there was a proposal of making the SQL syntax itself
extendable. To my knowledge, this is still a wanted feature [1]https://wiki.postgresql.org/wiki/Todo#Exotic_Features. In theory,
that would allow you to implement the feature you want in an extension.

[1]: https://wiki.postgresql.org/wiki/Todo#Exotic_Features

--
Best regards,
Aleksander Alekseev

On 10/14/21 1:47 PM, Aleksander Alekseev wrote:

Hi Gilles,

Any though and interest in this feature?

Personally, I wouldn't call this feature particularly useful. `SELECT
*` is intended for people who are working with DBMS directly e.g. via
psql and want to see ALL columns.

I disagree strongly with this. It is really annoying when working
interactively with psql on a table that has a PostGIS geometry column,
or any other large blobby type column.

I have not looked at the patch, but +1 for the feature.
--
Vik Fearing

Hi Vik,

I have not looked at the patch, but +1 for the feature.

Maybe you could describe your use case in a little more detail? How
did you end up working with PostGIS geometry via psql on regular
basis? What exactly do you find of annoyance? How will the proposed
patch help?

I find it great that we have people with polar opinions in the
discussion. But to reach any consensus you should make the opponent
understand your situation. Also, please don't simply discard the
disadvantages stated above. If you don't believe these are significant
disadvantages, please explain why do you think so.

--
Best regards,
Aleksander Alekseev

čt 14. 10. 2021 v 14:13 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 10/14/21 1:47 PM, Aleksander Alekseev wrote:

Hi Gilles,

Any though and interest in this feature?

Personally, I wouldn't call this feature particularly useful. `SELECT
*` is intended for people who are working with DBMS directly e.g. via
psql and want to see ALL columns.

I disagree strongly with this. It is really annoying when working
interactively with psql on a table that has a PostGIS geometry column,
or any other large blobby type column.

I have not looked at the patch, but +1 for the feature.

Cannot be better to redefine some strategies for output for some types.

I can agree so sometimes in some environments proposed features can be
nice, but it can be a strong footgun too.

Maybe some strange data can be filtered in psql and it can be better
solution. I agree, so usually print long geometry in psql is useless.

Regards

Pavel

--

On Thu, 14 Oct 2021 at 07:17, Gilles Darold <gilles@migops.com> wrote:

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.

It seems to me we've gone in the reverse direction recently. It used to be
that the oid columns of the system tables were hidden (hardcoded, as far as
I know), but as of Postgres 12 I believe there are no more hidden columns:
SELECT * from a table always gives all the columns.

I think a "select all columns except …" would be more useful; or another
approach would be to use a display tool that defaults to displaying only a
portion of large fields.

Le 14/10/2021 à 13:47, Aleksander Alekseev a écrit :

Hi Gilles,

Any though and interest in this feature?

Personally, I wouldn't call this feature particularly useful. `SELECT
*` is intended for people who are working with DBMS directly e.g. via
psql and want to see ALL columns. The applications should never use
`SELECT *`. So I can't see any real benefits of adding this feature to
PostgreSQL. It will only make the existing code and the existing user
interface even more complicated than they are now.

Thanks for your comments Aleksander. This was also my thougth at
begining but unfortunately there is cases where things are not so simple
and just relying on SELECT * is dirty or forbidden.  The hidden column
are not only useful for SELECT * but also for INSERT without column
list, but INSERT without column list is also a bad practice.

Also, every yet another feature is x N corner cases when this feature
works with other N features of PostgreSQL. How should it work with
partitioned or inherited tables? Or with logical replication? With
pg_dump? With COPY?

I recommand you to have look to my patch because the partitioned and
inherited case are covered, you can have a . For logical replication I
guess that any change in pg_attribute is also replicated so I I would
said that it is fully supported. But obviously I may miss something.
pg_dump and COPY are also supported.

Actually the patch only prevent an hidden column to be part of a star
expansion for the returned column, I don't think there is corner case
with the other part of the code outside that we need to prevent a table
to have all columns hidden. But I could miss something, I agree.

So all in all, -1. This being said, I very much appreciate your
attempt to improve PostgreSQL. However next time before writing the
code I suggest submitting an RFC first.

Don't worry about my time spent for the PG community, this patch is a
dust in my contribution to open source :-) If I have provided the patch
to show the concept and how it can be easily implemented.  Also it can
be used in some PostgreSQL forks if one is interested by this feature.

--

Gilles Darold

Le 14/10/2021 à 14:09, Aleksander Alekseev a écrit :

Hi again,

So all in all, -1. [...]

Here is something I would like to add:

1. As far as I know, "all the rest of DBMS have this" was never a good
argument in the PostgreSQL community. Generally, using it will turn
people against you.

I have cited the implementation in the other RDBMS because it helps to
understand the feature, it shows the state of the art on it and
illustrates my needs. If making references to other implementation turns
people against me I think that they have the wrong approach on this
proposal and if we refuse feature because they are implemented in other
RDBMS this is even worst. I'm not agree with this comment.

2. I recall there was a proposal of making the SQL syntax itself
extendable. To my knowledge, this is still a wanted feature [1]. In
theory, that would allow you to implement the feature you want in an
extension.

For what I've read in this thread
/messages/by-id/20210501072458.adqjoaqnmhg4l34l@nol
there is no real consensus in how implementing this feature should be
done. But I agree that if the implementation through an extension was
possible I would not write a patch to core but an extension, this is my
common behavior.

Best regards,

--
Gilles Darold
http://www.darold.net/

Le 14/10/2021 à 14:28, Pavel Stehule a écrit :

čt 14. 10. 2021 v 14:13 odesílatel Vik Fearing
<vik@postgresfriends.org <mailto:vik@postgresfriends.org>> napsal:

On 10/14/21 1:47 PM, Aleksander Alekseev wrote:

Hi Gilles,

Any though and interest in this feature?

Personally, I wouldn't call this feature particularly useful.

`SELECT

*` is intended for people who are working with DBMS directly

e.g. via

psql and want to see ALL columns.

I disagree strongly with this.  It is really annoying when working
interactively with psql on a table that has a PostGIS geometry column,
or any other large blobby type column.

I have not looked at the patch, but +1 for the feature.

Cannot be better to redefine some strategies for output for some types.

I can agree so sometimes in some environments proposed features can be
nice, but it can be a strong footgun too.

Maybe some strange data can be filtered in psql and it can be better
solution. I agree, so usually print long geometry in psql is useless.

Pavel this doesn't concern only output but input too, think about the
INSERT or COPY without a column list. We can add such filter in psql but
how about other clients? They all have to implement their own filtering
method. I think the HIDDEN attribute provide a common and basic way to
implement that in all client application.

--
Gilles Darold
http://www.darold.net/

On Thu, Oct 14, 2021 at 2:32 PM Gilles Darold <gilles@darold.net> wrote:

Le 14/10/2021 à 14:28, Pavel Stehule a écrit :

čt 14. 10. 2021 v 14:13 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 10/14/21 1:47 PM, Aleksander Alekseev wrote:

Hi Gilles,

Any though and interest in this feature?

Personally, I wouldn't call this feature particularly useful. `SELECT
*` is intended for people who are working with DBMS directly e.g. via
psql and want to see ALL columns.

I disagree strongly with this. It is really annoying when working
interactively with psql on a table that has a PostGIS geometry column,
or any other large blobby type column.

I have not looked at the patch, but +1 for the feature.

Cannot be better to redefine some strategies for output for some types.

I can agree so sometimes in some environments proposed features can be
nice, but it can be a strong footgun too.

Maybe some strange data can be filtered in psql and it can be better
solution. I agree, so usually print long geometry in psql is useless.

Pavel this doesn't concern only output but input too, think about the
INSERT or COPY without a column list. We can add such filter in psql but
how about other clients? They all have to implement their own filtering
method. I think the HIDDEN attribute provide a common and basic way to
implement that in all client application.

I like the idea - being able to hide computed columns such as tsvectors
from CRUD queries by default seems like it would be very nice for example.

--
Dave Page
Blog: https://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: https://www.enterprisedb.com

On Thu, 14 Oct 2021 at 07:16, Gilles Darold <gilles@migops.com> wrote:

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.

The behaviour of SELECT * is well defined and consistent across many
databases, so I don't like changing the behaviour of it.

I would be in favour of a different symbol which expands to a more
selective column set. Perhaps by default it picks up short textish columns;
skip bytea or long text fields for example but can be adjusted with HIDDEN.
Perhaps "SELECT +"?

--
Rod Taylor

On Thu, Oct 14, 2021 at 01:16:45PM +0200, Gilles Darold wrote:

Hi,

Here is a proposal to implement HIDDEN columns feature in PostgreSQL.

Great! Actually I found this very useful, especially for those people
using big fields (geometry, files, large texts).

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

An a common one, even if we want to think otherwise. I have found that
in almost every customer I have the bad luck to get to see code or
SELECTs.

Not counting that sometimes we have columns for optimization like Dave
saved about hidden a ts_vector column.

Another use case I can think of is not covered in this patch, but it
could be (I hope!) or even if not I would like opinions on this idea.
What about a boolean GUC log_hidden_column that throws a LOG message when
a hidden column is used directly?

The intention is to mark a to-be-deleted column as HIDDEN and then check
the logs to understand if is still being used somewhere. I know systems
where they carry the baggage of deprecated columns only because they
don't know if some system is still using them.

I know this would be extending your original proposal, and understand if
you decide is not a first patch material.

Anyway, a +1 to your proposal.

--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL

Le 14/10/2021 à 17:38, Jaime Casanova a écrit :

On Thu, Oct 14, 2021 at 01:16:45PM +0200, Gilles Darold wrote:

Hi,

Here is a proposal to implement HIDDEN columns feature in PostgreSQL.

Great! Actually I found this very useful, especially for those people
using big fields (geometry, files, large texts).

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

An a common one, even if we want to think otherwise. I have found that
in almost every customer I have the bad luck to get to see code or
SELECTs.

Not counting that sometimes we have columns for optimization like Dave
saved about hidden a ts_vector column.

Another use case I can think of is not covered in this patch, but it
could be (I hope!) or even if not I would like opinions on this idea.
What about a boolean GUC log_hidden_column that throws a LOG message when
a hidden column is used directly?

The intention is to mark a to-be-deleted column as HIDDEN and then check
the logs to understand if is still being used somewhere. I know systems
where they carry the baggage of deprecated columns only because they
don't know if some system is still using them.

I know this would be extending your original proposal, and understand if
you decide is not a first patch material.

Why not, I will add it if there is a consencus about logging hidden
column use, this is not a big work.

--
Gilles Darold

Gilles Darold <gilles@migops.com> writes:

Le 14/10/2021 à 17:38, Jaime Casanova a écrit :

On Thu, Oct 14, 2021 at 01:16:45PM +0200, Gilles Darold wrote:

Here is a proposal to implement HIDDEN columns feature in PostgreSQL.

Another use case I can think of is not covered in this patch, but it
could be (I hope!) or even if not I would like opinions on this idea.
What about a boolean GUC log_hidden_column that throws a LOG message when
a hidden column is used directly?

Why not, I will add it if there is a consencus about logging hidden
column use, this is not a big work.

This seems like a completely orthogonal idea. If you are trying
to figure out whether you have any applications that depend on
column X (without breaking anything), you should absolutely not
start by marking the column "hidden", because that'll break the
case where the apps are expecting "SELECT *" to return the column.
But if you're okay with breaking things, you might as well just
drop the column, or else revoke SELECT privilege on it, and see
what happens.

I'm not sure about the utility of logging explicit references to a
specific column --- seems like grepping the results of "log_statement"
would serve. But in any case I think it is not a good idea to tie
it to this proposal.

As for the proposal itself, I'm kind of allergic to the terminology
you've suggested, because the column is in no way hidden. It's
still visible in the catalogs, you can still select it explicitly,
etc. Anybody who thinks this is useful from a security standpoint
is mistaken, but these words suggest that it is. Perhaps some
terminology like "not expanded" or "unexpanded" would serve better
to indicate that "SELECT *" doesn't expand to include the column.
Or STAR versus NO STAR, maybe.

I also do not care for the syntax you propose: AFAICS the only reason
you've gotten away with making HIDDEN not fully reserved is that you
require it to be the last attribute of a column, which is something
that will trip users up all the time. Plus, it does not scale to the
next thing we might want to add. So if you can't make it a regular,
position-independent element of the ColQualList you shouldn't do it
at all.

What I think is actually important is the ALTER COLUMN syntax.
We could easily get away with having that be the only syntax for
this --- compare the precedent of ALTER COLUMN SET STATISTICS.

BTW, you do NOT get to add an information_schema column for
this. The information_schema is defined in the SQL standard.
Yes, I'm aware that mysql feels free to "extend" the standard
in that area; but our policy is that the only point of having the
information_schema views at all is if they're standard-compliant.

regards, tom lane

č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

On Thursday, October 14, 2021, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Gilles Darold <gilles@migops.com> writes:

Le 14/10/2021 à 17:38, Jaime Casanova a écrit :

On Thu, Oct 14, 2021 at 01:16:45PM +0200, Gilles Darold wrote:

Why not, I will add it if there is a consencus about logging hidden
column use, this is not a big work.

This seems like a completely orthogonal idea.

+1

As for the proposal itself, I'm kind of allergic to the terminology
you've suggested, because the column is in no way hidden. It's
still visible in the catalogs, you can still select it explicitly,
etc. Anybody who thinks this is useful from a security standpoint
is mistaken, but these words suggest that it is. Perhaps some
terminology like "not expanded" or "unexpanded" would serve better
to indicate that "SELECT *" doesn't expand to include the column.
Or STAR versus NO STAR, maybe.

Taking this a bit further, I dislike tying the suppression of the column
from the select-list star to the behavior of insert without a column list
provided. I’m not fully on board with having an attribute that is not
fundamental to the data model but rather an instruction about how that
column interacts with SQL; separating the two aspects, though, would help.
I accept the desire to avoid star expansion much more than default columns
for insert. Especially since the most compelling example of the later, not
having to specify generated columns on insert, would directly conflict with
the fact that it is those generated columns that are most likely to be
useful to display when specifying a star in the select query.

What I think is actually important is the ALTER COLUMN syntax.
We could easily get away with having that be the only syntax for
this --- compare the precedent of ALTER COLUMN SET STATISTICS.

+1

BTW, you do NOT get to add an information_schema column for
this.

FWIW, +1, though the project policy reminder does stand on its own.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Taking this a bit further, I dislike tying the suppression of the column
from the select-list star to the behavior of insert without a column list
provided. I’m not fully on board with having an attribute that is not
fundamental to the data model but rather an instruction about how that
column interacts with SQL; separating the two aspects, though, would help.
I accept the desire to avoid star expansion much more than default columns
for insert.

Yeah, me too. I think it would add a lot of clarity if we defined this
as "this affects the behavior of SELECT * and nothing else" ... although
even then, there are squishy questions about how much it affects the
behavior of composite datums that are using the column's rowtype.
But as soon as you want it to bleed into INSERT, you start having a
lot of questions about what else it should bleed into, as Aleksander
already mentioned.

regards, tom lane

Le 14/10/2021 à 19:44, Tom Lane a écrit :

As for the proposal itself, I'm kind of allergic to the terminology
you've suggested, because the column is in no way hidden. It's
still visible in the catalogs, you can still select it explicitly,
etc. Anybody who thinks this is useful from a security standpoint
is mistaken, but these words suggest that it is. Perhaps some
terminology like "not expanded" or "unexpanded" would serve better
to indicate that "SELECT *" doesn't expand to include the column.
Or STAR versus NO STAR, maybe.

Agree, I also had this feeling. I decide to use HIDDEN like in DB2 just
because UNEXPANDED looks to me difficult to understand by users and that
hidden or Invisible column are well known. This is a kind of "vendor
standard" now. But I agree that it can confuse uninformed people and
doesn't reflect the real feature. I will rename the keyword as
"UNEXPANDED", will do.

I also do not care for the syntax you propose: AFAICS the only reason
you've gotten away with making HIDDEN not fully reserved is that you
require it to be the last attribute of a column, which is something
that will trip users up all the time. Plus, it does not scale to the
next thing we might want to add. So if you can't make it a regular,
position-independent element of the ColQualList you shouldn't do it
at all.

Yes I have also noted that and wanted to improve this later if the
proposal was accepted.

What I think is actually important is the ALTER COLUMN syntax.
We could easily get away with having that be the only syntax for
this --- compare the precedent of ALTER COLUMN SET STATISTICS.

Ok great, I'm fine with that, especially for the previous point :-) I
will remove it from the CREATE TABLE syntax except in the INCLUDING like
option.

BTW, you do NOT get to add an information_schema column for
this. The information_schema is defined in the SQL standard.
Yes, I'm aware that mysql feels free to "extend" the standard
in that area; but our policy is that the only point of having the
information_schema views at all is if they're standard-compliant.

Ok, I will remove it.

--
Gilles Darold

I wrote:

Yeah, me too. I think it would add a lot of clarity if we defined this
as "this affects the behavior of SELECT * and nothing else" ... although
even then, there are squishy questions about how much it affects the
behavior of composite datums that are using the column's rowtype.

Re-reading that, I realize I probably left way too much unstated,
so let me spell it out.

Should this feature affect
SELECT * FROM my_table t;
? Yes, absolutely.

How about
SELECT t.* FROM my_table t;
? Yup, one would think so.

Now how about
SELECT row_to_json(t.*) FROM my_table t;
? All of a sudden, I'm a lot less sure --- not least because we *can't*
simply omit some columns, without the composite datum suddenly not being
of the table's rowtype anymore, which could have unexpected effects on
query semantics. In particular, if we have a user-defined function
that's defined to accept composite type my_table, I don't think we can
suppress columns in
SELECT myfunction(t.*) FROM my_table t;

And don't forget that these can also be spelled like
SELECT row_to_json(t) FROM my_table t;
without any star visible anywhere.

So the more I think about this, the squishier it gets. I'm now sharing
the fears expressed upthread about whether it's even possible to define
this in a way that won't have a lot of gotchas.

regards, tom lane