Re: [PATCH] Proposal for HIDDEN/INVISIBLE column

Le 15/10/2021 à 09:47, Aleksander Alekseev a écrit :
>
>>> 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
>> [...]
>>
>> I feel using EXCEPT would be a lot clearer, no one is likely to be
>> mislead into thinking that its is a security feature unlike 'HIDDEN'.
>> Also you know that SELECT * will select all columns.
>>
>> If this kind of feature were to be added, then I'd give a +1 to use the
>> EXCEPT syntax.
> +1 to that, personally I would love to have SELECT * EXCEPT ... syntax
> in PostgreSQL. Also, I discovered this feature was requested even
> earlier, in 2007 [1]
>
>> I don't think that the EXCEPT syntax will be adopted as it change the
>> SQL syntax for SELECT in a non standard way. This is not the case of the
>> hidden column feature which doesn't touch of the SELECT or INSERT syntax.
> HIDDEN columns affect SELECT and INSERT behaviour in the same
> non-standard way, although maybe without changing the syntax.
> Personally, I believe this is even worse. The difference is that with
> `SELECT * EXCEPT` you explicitly state what you want, while HIDDEN
> columns do this implicitly. Extending the syntax beyond standards in a
> reasonable way doesn't seem to be a problem. As a recent example in
> this thread [2] the community proposed to change the syntax in
> multiple places at the same time.
>
> `SELECT * EXCEPT` solves the same problem as HIDDEN columns, but is
> much easier to implement and maintain. Since it's a simple syntax
> sugar it doesn't affect the rest of the system.

That's not true, this is not the same feature. the EXCEPT clause will
not return column that you don't want in a specific request. I have
nothing against that but you have to explicitly name them. I think about
kind of bad design that we can find commonly like a table with
attribute1 ... attribute20. If we can use regexp with EXCEPT like
'attribute\d+' that could be helpful too. But this is another thread.

The hidden column feature hidden the column for all queries using the
wilcard on the concerned table. For example if I have to import a
database with OID enabled from an old dump and I want to prevent the OID
column to be returned through the star use, I can turn the column hidden
and I will not have to modify my old very good application. I caricature
but this is the kind of thing that could happen. I see several other
possible use of this feature with extensions that could use a technical
column that the user must not see using the wildcard. Also as Vik or
Dave mention being able to hide all tsvector columns from query without
having to specify it as exception in each query used can save some time.

IMHO this is definitively not the same feature.

--
Gilles Darold