I saw someone ask once for a schema diagram of the system catalogs.
Things like that have occasionally been produced manually, but they are
not regularly updated. That made me wonder, why can't we add primary
and foreign keys to system catalogs and then have existing tools produce
such a schema diagram automatically?
Since we have ADD PRIMARY KEY USING INDEX, we can declare a primary key
for an existing index. So this doesn't have to affect the low-level
early bootstrapping. The attached patch adds those commands manually.
Another option might be to have the catalog generation Perl tooling
create those declarations automatically from some marker in the catalog
files. That would also allow declaring unique constraints for the
unique indexes that don't end up being the primary key.
Any thoughts on this direction?
I like the general idea a lot. I've used Pg for a long time and I still struggle to march up relationships sometimes. Especially given that some things use relation oid oid keys and some use named cols as keys.
So a big +1 from me for the idea. Especially if we ensure psql recognises when the relation 'oid' attribute has a declared PK and includes it in the column list.
I don't object to simply declaring them without any implementing triggers. You aren't supposed to mess with the catalogs anyway. I'd actually like it to be defended against more actively by default. So the FKs are implicitly always valid, because the implementation says so. Much the same way trigger based FKs are unless you break the rules and mess with the triggers.
Frankly I think we really need a way to mark FKs to be DISABLED or NOT CHECKED or something and a way to mark them as NOT VALID. Rsther than expecting uses to fiddle with the implementation triggers. But I don't think FKs on system catalogs require that, it's just be cosmetic there.