Re: Add primary keys to system catalogs

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Craig Ringer <craig(dot)ringer(at)enterprisedb(dot)com>
Cc: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Add primary keys to system catalogs
Date: 2020-10-03 17:23:46
Message-ID: 20201003172346.GA9097@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Sat, Oct 3, 2020 at 09:27:02PM +0800, Craig Ringer wrote:
>
>
> 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. 

+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(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message John Naylor 2020-10-03 17:31:51 Re: Add primary keys to system catalogs
Previous Message Bruce Momjian 2020-10-03 16:33:31 Re: <xref> vs <command> formatting in the docs