Thread: evtfoid and evtowner missing in findoidjoins/README

"Joel Jacobson" <joel@compiler.org> writes:
> The below references are already properly documented in
>    https://www.postgresql.org/docs/current/catalog-pg-event-trigger.html
> but missing in src/tools/findoidjoins/README.
> Join pg_catalog.pg_event_trigger.evtowner => pg_catalog.pg_authid.oid
> Join pg_catalog.pg_event_trigger.evtfoid => pg_catalog.pg_proc.oid

Yup, no surprise given the way findoidjoins works: it could only
discover those relationships if pg_event_trigger had some entries in
the end state of the regression database.  Of course it doesn't, and
I'd be against leaving a live event trigger in place in that DB.
(I suspect there are other similar gaps in the coverage.)

I kind of wonder whether findoidjoins has outlived its purpose and
we should just remove it (along with the oidjoins test script).
IMO it was intended to find mistakes in the initial catalog data,
but given the current policy that the .dat files shall not contain
numeric OID references, that type of mistake is impossible anymore.
Certainly, it's been so long since that test script has caught
anything that it doesn't seem worth the annual-or-so maintenance
effort to update it.

A different line of thought would be to try to teach findoidjoins
to scrape info about catalog references out of catalogs.sgml, and
use that instead of or in addition to its current methods.  That
seems like a fair amount of work though, and again I can't get
excited that it'd be worth the trouble.

Also, I recall mutterings on -hackers about adding foreign-key
entries to pg_constraint to document the catalog reference
relationships.  (In my possibly-faulty recollection, the idea
was that these'd only be documentation and would lack enforcement
triggers; but perhaps we'd allow the planner to trust them for
purposes of optimizing multi-catalog queries.)  If we had those,
we could make findoidjoins use them instead of trawling the data,
or maybe throw away findoidjoins per se and let the oidjoins.sql
script read the FK entries to find out what to check dynamically.

            regards, tom lane

"Joel Jacobson" <joel@compiler.org> writes:
> A bit of background:
> I'm working on an extension where I need SQL access to this reference
> information.  My extension is successfully automatically helping me to
> find problems in extension update-scripts, where an update from a
> version to a version would give a different result than directly
> installing the to-version from scratch.

> Currently, I'm parsing findoidjoins/README and importing the "from
> column" and "to column" to a lookup-table, which is used by my
> extension.

Hmm.  That README was certainly never intended to be used that way ;-)

> So I think adding this as a lookup table in pg_catalog is the best solution,
> since extension writers could then use this information in various ways.

I'm definitely -1 on adding a catalog for that.  But it seems like the
idea of not-really-enforced FK entries in pg_constraint would serve your
purposes just as well (and it'd be better from the standpoint of getting
the planner to be aware of these relationships).

> The information is theoretically already available via catalogs.sgml,
> but a) it's not easy to parse, and b) it's not available from SQL.

Well, SGML is actually plenty easy to parse as long as you've got xml
tooling at hand.  We'd never want to introduce such a dependency in the
normal build process, but making something like findoidjoins depend on
such tools seems within reason.  I recall having whipped up some one-off
Perl scripts of that sort when I was doing that massive rewrite of the
func.sgml tables last year.  I didn't save them though, and in any case
I'm the world's worst Perl programmer, so it'd be better for someone
with more Perl-fu to take point if we decide to go that route.

            regards, tom lane