Re: [RFC] Removing "magic" oids

Hi,

On 2018-11-20 11:25:22 -0500, Stephen Frost wrote:
> Greetings,
>
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> > On Sun, Oct 14, 2018 at 6:35 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > Andres Freund <andres(at)anarazel(dot)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