Recording foreign key relationships for the system catalogs

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>, "Joel Jacobson" <joel(at)compiler(dot)org>
Subject: Recording foreign key relationships for the system catalogs
Date: 2021-01-31 21:39:57
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Now that dfb75e478 is in, I looked into whether we can have some
machine-readable representation of the catalogs' foreign key
relationships. As per the previous discussion [1], it's not
practical to have "real" SQL foreign key constraints, because
the semantics we use aren't quite right (i.e., using 0 instead
of NULL in rows with no reference). Nonetheless it would be
nice to have the knowledge available in some form, and we agreed
that a set-returning function returning the data would be useful.

The attached patch creates that function, and rewrites the oidjoins.sql
regression test to use it, in place of the very incomplete info that's
reverse-engineered by findoidjoins. It's mostly straightforward.

My original thought had been to add DECLARE_FOREIGN_KEY() macros
for all references, but I soon realized that in a large majority of
the cases, that's redundant with the BKI_LOOKUP() annotations we
already have. So I taught to extract FK data from
BKI_LOOKUP() as well as the explicit DECLARE macros. That didn't
remove the work entirely, because it turned out that we hadn't
bothered to apply BKI_LOOKUP() labels to most of the catalogs that
have no hand-made data. A big chunk of the patch consists in
adding those as needed. Also, I had to make the BKI_LOOKUP()
mechanism a little more complete, because it failed on pg_namespace
and pg_authid references. (It will still fail on some other
cases such as BKI_LOOKUP(pg_foreign_server), but I think there's
no need to fill that in until/unless we have some built-in data
that needs it.)

There are various loose ends yet to be cleaned up:

* I'm unsure whether it's better for the SRF to return the
column names as textual names, or as column numbers. Names was
a bit easier for all the parts of the current patch so I did
it that way, but maybe there's a case for the other way.
Actually the whole API for the SRF is just spur-of-the-moment,
so maybe a different API would be better.

* It would now be possible to remove the PGNSP and PGUID kluges
entirely in favor of plain BKI_LOOKUP references to pg_namespace
and pg_authid. The catalog header usage would get a little
more verbose: BKI_DEFAULT(PGNSP) becomes BKI_DEFAULT(pg_catalog)
inclined to do it, simply to remove one bit of mechanism that has
to be documented; but it's material for a separate patch perhaps.

* src/tools/findoidjoins should be nuked entirely, AFAICS.
Again, that could be a follow-on patch.

* I've not touched the SGML docs. Certainly
pg_get_catalog_foreign_keys() should be documented, and some
adjustments in bki.sgml might be appropriate.

regards, tom lane


Attachment Content-Type Size
add-catalog-foreign-key-info-1.patch text/x-diff 151.5 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2021-01-31 22:19:17 Re: Thoughts on "killed tuples" index hint bits support on standby
Previous Message Alexander Korotkov 2021-01-31 20:55:38 Re: [HACKERS] [PATCH] Generic type subscripting