Recording foreign key relationships for the system catalogs
Tom Lane <tgl@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
"Joel Jacobson" <joel@compiler.org>
Date: 2021-01-31T21:39:57Z
Lists: pgsql-hackers
Attachments
- add-catalog-foreign-key-info-1.patch (text/x-diff) patch
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 genbki.pl 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) and BKI_DEFAULT(PGUID) becomes BKI_DEFAULT(POSTGRES). I'm a bit 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 [1] https://www.postgresql.org/message-id/flat/dc5f44d9-5ec1-a596-0251-dadadcdede98%402ndquadrant.com
Commits
-
Remove special BKI_LOOKUP magic for namespace and role OIDs.
- ba0faf81c65a 14.0 landed
-
Retire findoidjoins.
- ef3d4613c020 14.0 landed
-
Build in some knowledge about foreign-key relationships in the catalogs.
- 62f34097c884 14.0 landed