Re: How about to have relnamespace and relrole?

Thank you for your comment.

Sorry for the silly typo in the subject.

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>
> Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> writes:
> > Most of OID types has reg* OID types. Theses are very convenient
> > when looking into system catalog/views, but there aren't OID
> > types for userid and namespace id.
>
> > What do you think about having these new OID types?
>
> 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.

As the first step, catalogs which have oid are,

select relname from pg_class where relnamespace = 'pg_catalog'::regnamespace and relhasoids = true;
...
(34 rows)

# Yes, regnamespace is very usable here:)

I say that 34 is too many for sure to have reg* types.

From the viewpoint of human-readable names, the more it enters
DBA's sight, the more significance it could be said to have.

Among the 34, the rough list of from which catalog they are
referred to is following.

pg_authid(role): pg_class, pg_type, pg_database, and many, many.
pg_type: referred to from pg_operator, pg_proc, pg_cast, and many.
pg_namespace: referred to from many catalogs.
pg_class: pg_locks
pg_database: pg_locks
pg_ts_parser: pg_ts_config
pg_ts_template: pg_ts_template
pg_foreign_data_wrapper: pg_foreign_server
pg_foreign_server: pg_user_mapping

This is not an comprehensive counting but I think I can
confidently say that regrole has significant meaning. (and
namespace also could be said so). I would make the comprehensive
one if you or others think it's needed. (altough it would be a
labor)

What do you think about the point of view?

> The existing reg* types were chosen to handle the cases where objects have
> schema-qualified (and/or type-overloaded) names so that correct lookup is
> not merely a matter of (select oid from pg_foo where name = 'bar') or
> vice versa.
>
> I think the policy is, or should be, that we create reg* types for exactly
> the cases where lookup isn't that simple.

Yes, I have noticed that. And I agree that it is one reasonable
boundary. But the point mentioned above is also a reasonable
boundary.

> In particular, both of the cases you mention are hardly "new". They
> existed when we made the current set of reg* types and were consciously
> not added then.
>
> > SELECT relnamespace::regnamespace, relname, relowner::regrole
> > FROM pg_class WHERE relnamespace = 'public'::regnamespace;
>
> Two reasons this isn't terribly compelling are (1) it's creating a
> join in a place where the planner can't possibly see it and optimize
> it,

Maybe un-optimiz-ability is not a matter as far as they are used
in one-liners for administrative operations like I mentioned
above. (On the contrary, syscache is far faster than normal table
lookup for most cases, but it doesn't justify to use this in OLTP
jobs even ignoring the MVCC issue.)

> and (2) you risk MVCC anomalies because the reg* output routines
> would not be using the same snapshot as the calling query.
> We already have problem (2) with the existing reg* functions so I'm
> not that excited about doubling down on the concept.

Surely. Then the page for the reg* in the doc (datatype-oid.html)
*shoud* mention such a caveat, but the limitation would be
tolerable for user(DBA)s as the same as before.

Once it is stated clearly, althgouh I won't intend to make it an
excuse, I think some (or one) new reg* type can be acceptable.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center