Re: Add primary keys to system catalogs

On Sat, 3 Oct 2020, 14:40 Peter Eisentraut, <
peter(dot)eisentraut(at)2ndquadrant(dot)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.

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.