Thread: [GENERAL] pg_catalog tables don't have constraints?

Hi,

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?

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?

Thanks,
Neil


--
Neil Anderson
neil@postgrescompare.com
https://www.postgrescompare.com

Neil Anderson <neil@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

> 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.

Ah! That makes sense. Constraints are there to help you when inserting
rows and when it comes to the catalogs you shouldn't be doing that
directly. I think for the diagram I'll add the links manually with the
caveat that they are not constraints, just edges in a graph.

Thanks for the details Tom!

Neil Anderson <neil@postgrescompare.com> wrote:

> 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?

Every time I have to dive into the pg_* tables, I really want such a diagram because
the relationships aren't obvious to me, so I've been looking for a diagram like that
and haven't found one. I've also considered trying to make one, but with all of the
custom types, my normal ERD tool isn't very cooperative.

Would you be willing to share the diagram with the list when you're done?

Would you be willing to share the names of the tools and process you used to create the diagram?

Thanks,
Kevin

kbrannen@pwhome.com schrieb am 09.06.2017 um 20:57:
> Neil Anderson <neil@postgrescompare.com> wrote:
>
>> 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?
>
> Every time I have to dive into the pg_* tables, I really want such a diagram because
> the relationships aren't obvious to me, so I've been looking for a diagram like that
> and haven't found one.

https://wiki.postgresql.org/wiki/Developer_FAQ#Is_there_a_diagram_of_the_system_catalogs_available.3F

>
> Of course. My plan is to copy the catalogs into a new schema so I can add fk
> constraints and then get something like navicat or datagrip to draw it.
>

Hi. I made some progress on this and I've added all the diagrams and
documentation I've been able to produce so far for v10beta as well as
the tools used here:

https://www.postgrescompare.com/2017/06/11/pg_catalog_constraints.html

I would like to try SchemaSpy but it doesn't seem to like constraints
on oid columns too much so I'll have to work around it somehow. Might
be worth it though since it seems to have a new lease of life at
schemaspy.org

Neil

Thomas Kellerer wrote:
> kbrannen@pwhome.com schrieb am 09.06.2017 um 20:57:
> > Neil Anderson <neil@postgrescompare.com> wrote:
> >
> >> 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?
> >
> > Every time I have to dive into the pg_* tables, I really want such a
> > diagram because the relationships aren't obvious to me, so I've been
> > looking for a diagram like that and haven't found one.
>
> https://wiki.postgresql.org/wiki/Developer_FAQ#Is_there_a_diagram_of_the_system_catalogs_available

Thanks Thomas! I wasn't aware of that. I will point out (for others) that the
PNG is for v8.3, a bit old, but still a good start. I'll also point out that
the SVG link is broken.


> Neil Anderson wrote:
> Hi. I made some progress on this and I've added all the diagrams and
> documentation I've been able to produce so far for v10beta as well as
> the tools used here:

That's a great start, thanks! You should put a pointer to that page in the Pg wiki.

I think the 1 thing that would really help it though is to show the actual
connection/relations of the columns. For example, a line between pg_index
and pg_class is not quite as informative as a line from pg_index.indexrelid
to pg_class.oid (something the PNG file in the image that Thomas pointed
out does). I suspect that's a limitation of the tool not your desire.

Is this process automated, or does it require you to lay it out?

In my perfect world, :) there would be a way to generate it automatically with
Graphviz or something similar. Hmm, perhaps an idea to pursue when I can find
some time.

Kevin

> That's a great start, thanks! You should put a pointer to that page in the Pg wiki.
>
> I think the 1 thing that would really help it though is to show the actual
> connection/relations of the columns. For example, a line between pg_index
> and pg_class is not quite as informative as a line from pg_index.indexrelid
> to pg_class.oid (something the PNG file in the image that Thomas pointed
> out does). I suspect that's a limitation of the tool not your desire.

I think that might be possible, I may have turned off the labels on the
edges.

>
> Is this process automated, or does it require you to lay it out?
>

The layouts were all built in the DataGrip luckily!

> In my perfect world, :) there would be a way to generate it automatically with
> Graphviz or something similar. Hmm, perhaps an idea to pursue when I can find
> some time.
>
> Kevin
>
>

I think SchemaSpy's new output looks really interesting and uses
Graphviz. After doing the work to add the relationships I realised that
SchemaSpy does not like the oid system columns that we use for keys so
it wouldn't work. I will go ahead and replace them all with something
else and see how it does but I haven't gotten back to it yet.


--
Neil Anderson
neil@postgrescompare.com
http://www.postgrescompare.com