Re: How about to have relnamespace and relrole?

Hello,

At Tue, 31 Mar 2015 16:48:18 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in <26969(dot)1427834898(at)sss(dot)pgh(dot)pa(dot)us>
> Hmm. We can ignore pg_attribute and pg_pltemplate, which don't have OIDs
> and thus aren't candidates anyway. And we can ignore the ones
> corresponding to the already-existing regFOO types. That leaves
>
> > pg_am | amname
> > pg_authid | rolname (*)
> > pg_collation | collname
> > pg_constraint | conname
> > pg_conversion | conname
> > pg_database | datname
> > pg_event_trigger | evtname
> > pg_extension | extname
> > pg_foreign_data_wrapper | fdwname
> > pg_foreign_server | srvname
> > pg_language | lanname
> > pg_namespace | nspname (*)
> > pg_opclass | opcname
> > pg_opfamily | opfname
> > pg_policy | polname
> > pg_rewrite | rulename
> > pg_tablespace | spcname
> > pg_trigger | tgname
> > pg_ts_parser | prsname
> > pg_ts_template | tmplname
>
> of which the proposed patch covers the two starred ones.
>
> OTOH, looking at this list, there are already numerous cases where
> the object identity is more than just a name (eg, collations have
> schema-qualified names, opfamilies are not only schema-qualified
> but are per-index-AM as well, triggers and constraints are named
> per-table, etc). So it's clear that we've already been applying
> a "usefulness" criterion rather than just "does it have a
> multi-component name" when choosing which objects to provide
> regFOO types for.

As I wrote before, the criteria I selected for choosing these
ones was how often the oid is referred to. The attached excel
file shows the complehensive list of reference counts.

Each cells is marked 'x' if the catalog of the row referrs to the
oid of the catalog on the column. So the numbers in the row 2
represents how mane times the oid of the catalog on the column is
referred to from other catalogs. Adding all catalog having tuple
oid and sorting by the number they are ordered as below.

(The upper cased 'X' in the HASOID column indicates that the view
exposes the oid of underlying table and identifying the rows in
the view)

(-) in the list below is the regFOO types already exists and the
second column is the number of other catalogs refers to the oid.

> pg_authid | 33 | rolname (*)
+ pg_class | 27 | relname (-)
> pg_namespace | 20 | nspname (*)
+ pg_type | 15 | typname (-)
+ pg_proc | 13 | proname (-)
+ pg_operator | 5 | oprname (-)
> pg_database | 5 | datname
> pg_am | 4 | amname
> pg_collation | 4 | collname
> pg_tablespace | 4 | spcname
> pg_foreign_server | 3 | srvname
> pg_opfamily | 3 | opfname
> pg_opclass | 2 | opcname
> pg_constraint | 1 | conname
> pg_foreign_data_wrapper | 1 | fdwname
> pg_language | 1 | lanname
+ pg_largeobject_metadata | 1 | -
> pg_policy | 1 | polname
> pg_rewrite | 1 | rulename
+ pg_ts_config | 1 | cfgname (-)
+ pg_ts_dict | 1 | dictname (-)
> pg_ts_parser | 1 | prsname
> pg_ts_template | 1 | tmplname
+ pg_user_mapping | 1 | -
+ pg_aggregate | 0 | -

All of amop, amproc, attrdef, cast, conversion, default_acl,
enum, event_trigger, extension, group, roles, shadow, trigger,
user are not referred to from any other catalogs.

> In view of that, you could certainly argue that if someone's bothered
> to make a patch to add a new regFOO type, it's useful enough. I don't
> want to end up with thirtysomething of them, but we don't seem to be
> trending in that direction.

pg_authid and pg_namespace are obviously win the race but haven't
got the prize. database to tablespace are in a gray zone but I
think they need highly significant reason to have regFOO type for
themselves.

On the other hand, regconfig(pg_ts_config) and
regdictionary(pg_ts_dist) have far less significance but I don't
assert they should be removed since they are there now.

> Or in short, objection withdrawn. (As to the concept, anyway.
> I've not read the patch...)

Thank you for your acceptance.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center