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.kyot...@lab.ntt.co.jp> wrote in
<20150204.172914.52110711.horiguchi.kyot...@lab.ntt.co.jp>
Tue, 03 Feb 2015 10:12:12 -0500, Tom Lane <t...@sss.pgh.pa.us> wrote in
<2540.1422976...@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
...
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
