On Sat, Oct 3, 2020 at 09:27:02PM +0800, Craig Ringer wrote: > > > On Sat, 3 Oct 2020, 14:40 Peter Eisentraut, <peter.eisentr...@2ndquadrant.com> > wrote: > > 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.
+1 > 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. +1 -- Bruce Momjian <br...@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
