Re: How about to have relnamespace and relrole? - Mailing list pgsql-hackers

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.kyotaro@lab.ntt.co.jp> wrote in
<20150204.172914.52110711.horiguchi.kyotaro@lab.ntt.co.jp>
> Tue, 03 Feb 2015 10:12:12 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote in <2540.1422976332@sss.pgh.pa.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

I counted how often oids of system catalogs are referred to by
other system catalog/views. Among them, pg_stat_* views, are
excluded since they have text representations for all oid
references.

The result is like this. The full result of the counting is in
the Excel file but it's not at hand for now.. I'll show it here
if anyone wants to see it.

pg_class.oid:     27
pg_authid.oid:    33
pg_namespace.oid: 20
pg_proc.oid:      13
pg_type.oid:      15
pg_databse.oid:    5
pg_operator.oid:   5
pg_am.oid:         4
....

Among these, authid and namespace are apparently referred to
frequently but don't have their reg* types but referred to from
more points than proc, type, operator, am..

# By the way, I don't understand where the "reg" comes from,
# REGistered? Or other origin?

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.


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.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center