Обсуждение: [HACKERS] "SELECT *" vs hidden columns and logical column order

Hi hackers,

I am aware of at three potential projects that would change the
meaning of "SELECT *":

1.  Incremental MATERIALIZED VIEW maintenance probably needs to be
able to use a hidden counter column which you can ask for by name but
will otherwise not show up to users:

https://www.postgresql.org/message-id/1371480075.55528.YahooMailNeo@web162901.mail.bf1.yahoo.com

2.  SQL:2011 temporal tables track system time and/or valid time with
columns that users create and then declare to be temporal control
columns.  I don't think they show up unless you name them directly (I
didn't check the standard but I noticed that it's that way in another
product), so I guess that's basically the same as (1).

3.  Logical column order aka ALTER COLUMN POSITION, a recurring topic
on pgsql-hackers for which patches have been written but nothing has
so far managed to stick:

https://www.postgresql.org/message-id/flat/20141209174146.GP1768%40alvh.no-ip.org

Suppose someone wanted to chip away at a small piece of incremental
matviews by inventing a way to declare 'hidden' columns: is there
really a dependency here as implied in the 2013 email above?  Is
anyone planning to revive logical column order?

-- 
Thomas Munro
http://www.enterprisedb.com

On Wed, Jun 28, 2017 at 11:52 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> I am aware of at three potential projects that would change the
> meaning of "SELECT *":
>
> 1.  Incremental MATERIALIZED VIEW maintenance probably needs to be
> able to use a hidden counter column which you can ask for by name but
> will otherwise not show up to users:
>
> https://www.postgresql.org/message-id/1371480075.55528.YahooMailNeo@web162901.mail.bf1.yahoo.com
>
> 2.  SQL:2011 temporal tables track system time and/or valid time with
> columns that users create and then declare to be temporal control
> columns.  I don't think they show up unless you name them directly (I
> didn't check the standard but I noticed that it's that way in another
> product), so I guess that's basically the same as (1).
>
> 3.  Logical column order aka ALTER COLUMN POSITION, a recurring topic
> on pgsql-hackers for which patches have been written but nothing has
> so far managed to stick:
>
> https://www.postgresql.org/message-id/flat/20141209174146.GP1768%40alvh.no-ip.org
>
> Suppose someone wanted to chip away at a small piece of incremental
> matviews by inventing a way to declare 'hidden' columns: is there
> really a dependency here as implied in the 2013 email above?  Is
> anyone planning to revive logical column order?

If someone were planning to revive logical column order, adding say a
column attdisplaypos which controlled how an asterisk in the target
list is expanded, then it would seem to make sense to use
attdisplaypos = 0, say, to mean "don't *-expand this column at all".

Of course, somebody could also propose to add attisdisplayed as a
Boolean column and refactor all the code that currently uses attnum
for that purpose to rely on attisdisplayed instead.  That code would
then presumably get refactored again if attdisplaypos ever showed up,
but probably the second refactoring would be pretty easy.  The really
hard part here is probably finding all of the places that are relying
on attnum < 0 as a proxy for whether the column should be displayed.

BTW, if we get either of these things, can I vote for, as a follow-on
patch, changing OID columns to be displayed by default, at least in
system catalogs?  I don't think that having the primary keys of our
system catalogs as a hidden column is particularly user-friendly.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 6/28/17 23:52, Thomas Munro wrote:
> 2.  SQL:2011 temporal tables track system time and/or valid time with
> columns that users create and then declare to be temporal control
> columns.  I don't think they show up unless you name them directly (I
> didn't check the standard but I noticed that it's that way in another
> product), so I guess that's basically the same as (1).

In my reading of the standard, those start/end time columns would show
up normally in SELECT *.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Sat, Jul 1, 2017 at 6:09 AM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> On 6/28/17 23:52, Thomas Munro wrote:
>> 2.  SQL:2011 temporal tables track system time and/or valid time with
>> columns that users create and then declare to be temporal control
>> columns.  I don't think they show up unless you name them directly (I
>> didn't check the standard but I noticed that it's that way in another
>> product), so I guess that's basically the same as (1).
>
> In my reading of the standard, those start/end time columns would show
> up normally in SELECT *.

Oh, well that's surprising.  I don't have a recent enough SQL standard
to hand, but I saw that a couple of popular RDBMSs with temporal
support understand GENERATED ALWAYS AS ROW { START | END } [[ IMPLICITLY ]
HIDDEN ] where that last bit controls whether SELECT * sees it.

-- 
Thomas Munro
http://www.enterprisedb.com