Re: [RFC] Removing "magic" oids - Mailing list pgsql-hackers

Hi,

On 2018-11-20 11:25:22 -0500, Stephen Frost wrote:
> Greetings,
> 
> * Robert Haas (robertmhaas@gmail.com) wrote:
> > On Sun, Oct 14, 2018 at 6:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > Andres Freund <andres@anarazel.de> writes:
> > > > Does anybody have engineering / architecture level comments about this
> > > > proposal?
> > >
> > > FWIW, I'm -1 on making OIDs be not-magic for SELECT purposes.  Yeah, it's
> > > a wart we wouldn't have if we designed the system today, but the wart is
> > > thirty years old.  I think changing that will break so many catalog
> > > queries that we'll have the villagers on the doorstep.  Most of the other
> > > things you're suggesting here could be done easily without making that
> > > change.
> > >
> > > Possibly we could make them not-magic from the storage standpoint (ie
> > > they're regular columns) but have a pg_attribute flag that says not
> > > to include them in "SELECT *" expansion.
> > 
> > I think such a flag would be a good idea; it seems to have other uses.
> > As Andres also noted, Kevin was quite interested in having a
> > hidden-by-default COUNT column to assist with materialized view
> > maintenance, and presumably this could also be used for that purpose.
> 
> Yeah, I like the idea of having this column too, it'd also be useful for
> people who are trying to use column-level privileges.

FWIW, leaving grammar bikeshedding aside, I don't think this is
particularly hard.  There certainly are a few corner cases I've not
touched here, but the attached implements the basic features for hidden
columns.


postgres[14805][1]=# CREATE TABLE blarg(id serial, data text, annotation text);
postgres[14805][1]=# INSERT INTO blarg (data, annotation) VALUES ('42', 'this is THE question');

postgres[14805][1]=# SELECT * FROM blarg;
┌────┬──────┬──────────────────────┐
│ id │ data │      annotation      │
├────┼──────┼──────────────────────┤
│  1 │ 42   │ this is THE question │
└────┴──────┴──────────────────────┘
(1 row)

postgres[14805][1]=# SELECT s.* FROM (SELECT * FROM blarg) s;
┌────┬──────┬──────────────────────┐
│ id │ data │      annotation      │
├────┼──────┼──────────────────────┤
│  1 │ 42   │ this is THE question │
└────┴──────┴──────────────────────┘
(1 row)

postgres[14805][1]=# SELECT s.* FROM (SELECT blarg FROM blarg) s;
┌───────────────────────────────┐
│             blarg             │
├───────────────────────────────┤
│ (1,42,"this is THE question") │
└───────────────────────────────┘
(1 row)


postgres[14805][1]=# SELECT s.annotation FROM (SELECT * FROM blarg) s;
┌──────────────────────┐
│      annotation      │
├──────────────────────┤
│ this is THE question │
└──────────────────────┘
(1 row)

postgres[14805][1]=# SELECT (s.blarg).annotation FROM (SELECT blarg FROM blarg) s;
┌──────────────────────┐
│      annotation      │
├──────────────────────┤
│ this is THE question │
└──────────────────────┘
(1 row)


-- update column to be hidden
postgres[14805][1]=# UPDATE pg_attribute SET attishidden = true WHERE attrelid = 'blarg'::regclass AND attname =
'annotation';

postgres[14805][1]=# SELECT * FROM blarg;
┌────┬──────┐
│ id │ data │
├────┼──────┤
│  1 │ 42   │
└────┴──────┘
(1 row)

postgres[14805][1]=# SELECT s.* FROM (SELECT * FROM blarg) s;
┌────┬──────┐
│ id │ data │
├────┼──────┤
│  1 │ 42   │
└────┴──────┘
(1 row)

postgres[14805][1]=# SELECT s.blarg FROM (SELECT blarg FROM blarg) s;
┌───────────────────────────────┐
│             blarg             │
├───────────────────────────────┤
│ (1,42,"this is THE question") │
└───────────────────────────────┘
(1 row)

postgres[14805][1]=# SELECT s.annotation FROM (SELECT * FROM blarg) s;
ERROR:  42703: column s.annotation does not exist
LINE 1: SELECT s.annotation FROM (SELECT * FROM blarg) s;
               ^
LOCATION:  errorMissingColumn, parse_relation.c:3319

postgres[14805][1]=# SELECT (s.blarg).annotation FROM (SELECT blarg FROM blarg) s;
┌──────────────────────┐
│      annotation      │
├──────────────────────┤
│ this is THE question │
└──────────────────────┘
(1 row)

postgres[14805][1]=# SELECT (s.blarg).* FROM (SELECT blarg FROM blarg) s;
┌────┬──────┐
│ id │ data │
├────┼──────┤
│  1 │ 42   │
└────┴──────┘
(1 row)


It's debatable if a wholerow-var select (without *, i.e the s.blarg case
above)) ought to include the hidden column, but to me that intuitively
makes sense. Otherwise you couldn't select it after a cast and such.

Greetings,

Andres Freund