Neil Anderson <n...@postgrescompare.com> 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
