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

[Neil Anderson]

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
n...@postgrescompare.com
http://www.postgrescompare.com


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

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

Thomas Kellerer wrote:
> kbran...@pwhome.com schrieb am 09.06.2017 um 20:57:
> > Neil Anderson  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


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

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

[Neil Anderson]

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


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

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

[Neil Anderson]

On 9 Jun 2017 14:59,  wrote:

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


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.



Thanks,
Kevin


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

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

[Thomas Kellerer]

kbran...@pwhome.com schrieb am 09.06.2017 um 20:57:

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





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

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

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


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

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

[Neil Anderson]

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



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

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

[Tom Lane]

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