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