Search Postgresql Archives

Re: pg_catalog tables don't have constraints?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Neil Anderson <neil@xxxxxxxxxxxxxxxxxxx> writes:
> I've been exploring the pg_catalog tables and pointed a couple of
> tools at it to extract an ER diagram for a blog post. At first I
> thought it was a bug in the drawing tool but it appears that the
> relationships between the pg_catalog tables are implicit rather than
> enforced by the database, is that correct?

Yep, there are no explicit FKs among the system catalogs.

> For example, pg_class has relnamespace which according to the
> documentation refers to pg_namespace.oid
> (https://www.postgresql.org/docs/current/static/catalog-pg-class.html),
> yet there is no FK there as far as I can see. Maybe I am missing
> something or there is an interesting story as to why?

It would be tough to do that without creating a lot of circularities.
>From the point of view of the low-level catalog manipulation code,
FKs are a high-level feature.

We don't have check constraints on system catalogs, either, for
largely similar reasons.  And while we do take the trouble to mark
some catalog columns NOT NULL, I'm pretty sure that's window
dressing: it's not actually checked on insertions driven from C code.

It'd be an interesting research project to see if such things could
be defined and enforced without getting into infinite recursions.
But even if it could be made to work, we'd probably only consider
enabling the constraints as a debug aid; in a production system,
testing them would just be overhead.

The bigger picture here is that catalog changes are supposed to be
executed by C code in response to DDL commands, and it's the C code
that is charged with maintaining catalog consistency.  Constraints
would be useful if we supported updating the catalogs with direct
SQL manipulations; but we don't really.  You can do that, if you're
a superuser who's willing to take risks, but our policy is that if
you break the catalogs that way you get to keep both pieces.

			regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux