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

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@gmail.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