Thread: [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