Re: How about to have relnamespace and relrole?

On 2/12/15 5:28 AM, Kyotaro HORIGUCHI wrote:
> Hello, I changed the subject.
>
> This mail is to address the point at hand, preparing for
> registering this commitfest.
>
> 15 17:29:14 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI
> <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in
> <20150204(dot)172914(dot)52110711(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
>> Tue, 03 Feb 2015 10:12:12 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in <2540(dot)1422976332(at)sss(dot)pgh(dot)pa(dot)us>
>>> I'm not really excited about that. That line of thought would imply
>>> that we should have "reg*" types for every system catalog, which is
>>> surely overkill.
>>
>> Mmm. I suppose "for every OID usage", not "every system catalog".
>> but I agree as the whole. There's no agreeable-by-all
>> boundary. Perhaps it depends on how often the average DBA looks
>> onto catalogs which have oids pointing another catalog which they
>> want to see in human-readable form, without joins if possible.
>>
>> I very roughly counted how often the oids of catalogs referred
>> from other catalogs.
>
> 1. Expected frequency of use
...
> For that reason, although the current policy of deciding whether
> to have reg* types seems to be whether they have schema-qualified
> names, I think regrole and regnamespace are valuable to have.

Perhaps schema qualification was the original intent, but I think at
this point everyone uses them for convenience. Why would I want to type
out JOIN pg_namespace n ON n.oid = blah.???namespace when I could simply
do ???namespace::regnamespace?

> 2. Anticipaed un-optimizability
>
> Tom pointed that these reg* types prevents planner from
> optimizing the query, so we should refrain from having such
> machinary. It should have been a long-standing issue but reg*s
> sees to be rather faster than joining corresponding catalogs
> for moderate number of the result rows, but this raises another
> more annoying issue.
>
>
> 3. Potentially breakage of MVCC
>
> The another issue Tom pointed is potentially breakage of MVCC by
> using these reg* types. Syscache is invalidated on updates so
> this doesn't seem to be a problem on READ COMMITTED mode, but
> breaks SERIALIZABLE mode. But IMHO it is not so serious problem
> as long as such inconsistency occurs only on system catalog and
> it is explicitly documented togethee with the un-optimizability
> issue. I'll add a patch for this later.

The way I look at it, all these arguments went out the window when
regclass was introduced.

The reality is that reg* is *way* more convenient than manual joins. The
arguments about optimization and MVCC presumably apply to all reg*
casts, which means that ship has long since sailed.

If we offered views that made it easier to get at this data then maybe
this wouldn't matter so much. But DBA's don't want to join 3 catalogs
together to try and answer a simple question.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com