Re: Add primary keys to system catalogs - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Add primary keys to system catalogs
Date
Msg-id 20201003172346.GA9097@momjian.us
Whole thread Raw
In response to Re: Add primary keys to system catalogs  (Craig Ringer <craig.ringer@enterprisedb.com>)
List 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.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




pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: vs formatting in the docs
Next
From: John Naylor
Date:
Subject: Re: Add primary keys to system catalogs