Re: [HACKERS] v10beta pg_catalog diagrams

[Pavel Stehule]

2017-06-15 5:06 GMT+02:00 Andres Freund :

> On 2017-06-14 20:02:27 -0700, Andres Freund wrote:
> > On June 14, 2017 7:53:05 PM PDT, Pavel Stehule 
> wrote:
> > >2017-06-14 19:49 GMT+02:00 Andres Freund :
> > >> > These constraints can slowdown creating/dropping database objects -
> > >> mainly
> > >> > temp tables.
> > >>
> > >> How so?
> > >>
> > >
> > >execution RI triggers
> >
> > Those would obviously bit be fired, given Peter's description?
>
> Gah, stupid autocorrect.  *not* be fired.  Unless you mean something
> else than the speed of catalog manipulations themselves, in which case I
> still don't understand.
>

No, just this. I missed it.

Regards

Pavel


>
> - Andres
>

Re: [HACKERS] v10beta pg_catalog diagrams

[Andres Freund]

On 2017-06-14 20:02:27 -0700, Andres Freund wrote:
> On June 14, 2017 7:53:05 PM PDT, Pavel Stehule  
> wrote:
> >2017-06-14 19:49 GMT+02:00 Andres Freund :
> >> > These constraints can slowdown creating/dropping database objects -
> >> mainly
> >> > temp tables.
> >>
> >> How so?
> >>
> >
> >execution RI triggers
> 
> Those would obviously bit be fired, given Peter's description?

Gah, stupid autocorrect.  *not* be fired.  Unless you mean something
else than the speed of catalog manipulations themselves, in which case I
still don't understand.

- Andres


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

Re: [HACKERS] v10beta pg_catalog diagrams

[Pavel Stehule]

2017-06-15 5:02 GMT+02:00 Andres Freund :

>
>
> On June 14, 2017 7:53:05 PM PDT, Pavel Stehule 
> wrote:
> >2017-06-14 19:49 GMT+02:00 Andres Freund :
> >
> >> On 2017-06-14 06:05:24 +0200, Pavel Stehule wrote:
> >> > 2017-06-14 5:53 GMT+02:00 Peter Eisentraut <
> >> peter.eisentr...@2ndquadrant.com
> >> > >:
> >> >
> >> > > On 6/13/17 17:08, Andres Freund wrote:
> >> > > > I wondered before if we shouldn't introduce "information only"
> >> > > > unenforced foreign key constraints for the catalogs.  We kind
> >of
> >> > > > manually do that via oidjoins, it'd be nicer if we'd a function
> >> > > > rechecking fkeys, and the fkeys were in the catalog...
> >> > >
> >> > > I don't see why we couldn't just add a full complement of primary
> >and
> >> > > foreign key constraints (and unique constraints and perhaps some
> >check
> >> > > constraints).  The argument is that they wouldn't normally do
> >anything,
> >> > > but they would help with documentation and browsing tools, and
> >they
> >> > > wouldn't hurt anything.
> >>
> >> Well, unique constraints are a bit more complicated because they rely
> >on
> >> an index, and we wouldn't e.g. maintain indexes with WHERE clauses or
> >> other expressions correctly.  I'd be a bit wary of declaring such
> >> indexes as actually being fully valid, because we have planner logic
> >> that does planning based on various constraints now, it'd certainly
> >be
> >> annoying if some "re-check constraint" type queries would actually
> >have
> >> their joins optimized away or such...
> >>
> >> > These constraints can slowdown creating/dropping database objects -
> >> mainly
> >> > temp tables.
> >>
> >> How so?
> >>
> >
> >execution RI triggers
>
> Those would obviously bit be fired, given Peter's description?
>

ok


>
> Andres
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>

Re: [HACKERS] v10beta pg_catalog diagrams

[Andres Freund]

On June 14, 2017 7:53:05 PM PDT, Pavel Stehule  wrote:
>2017-06-14 19:49 GMT+02:00 Andres Freund :
>
>> On 2017-06-14 06:05:24 +0200, Pavel Stehule wrote:
>> > 2017-06-14 5:53 GMT+02:00 Peter Eisentraut <
>> peter.eisentr...@2ndquadrant.com
>> > >:
>> >
>> > > On 6/13/17 17:08, Andres Freund wrote:
>> > > > I wondered before if we shouldn't introduce "information only"
>> > > > unenforced foreign key constraints for the catalogs.  We kind
>of
>> > > > manually do that via oidjoins, it'd be nicer if we'd a function
>> > > > rechecking fkeys, and the fkeys were in the catalog...
>> > >
>> > > I don't see why we couldn't just add a full complement of primary
>and
>> > > foreign key constraints (and unique constraints and perhaps some
>check
>> > > constraints).  The argument is that they wouldn't normally do
>anything,
>> > > but they would help with documentation and browsing tools, and
>they
>> > > wouldn't hurt anything.
>>
>> Well, unique constraints are a bit more complicated because they rely
>on
>> an index, and we wouldn't e.g. maintain indexes with WHERE clauses or
>> other expressions correctly.  I'd be a bit wary of declaring such
>> indexes as actually being fully valid, because we have planner logic
>> that does planning based on various constraints now, it'd certainly
>be
>> annoying if some "re-check constraint" type queries would actually
>have
>> their joins optimized away or such...
>>
>> > These constraints can slowdown creating/dropping database objects -
>> mainly
>> > temp tables.
>>
>> How so?
>>
>
>execution RI triggers

Those would obviously bit be fired, given Peter's description?

Andres
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.


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

Re: [HACKERS] v10beta pg_catalog diagrams

[Pavel Stehule]

2017-06-14 19:49 GMT+02:00 Andres Freund :

> On 2017-06-14 06:05:24 +0200, Pavel Stehule wrote:
> > 2017-06-14 5:53 GMT+02:00 Peter Eisentraut <
> peter.eisentr...@2ndquadrant.com
> > >:
> >
> > > On 6/13/17 17:08, Andres Freund wrote:
> > > > I wondered before if we shouldn't introduce "information only"
> > > > unenforced foreign key constraints for the catalogs.  We kind of
> > > > manually do that via oidjoins, it'd be nicer if we'd a function
> > > > rechecking fkeys, and the fkeys were in the catalog...
> > >
> > > I don't see why we couldn't just add a full complement of primary and
> > > foreign key constraints (and unique constraints and perhaps some check
> > > constraints).  The argument is that they wouldn't normally do anything,
> > > but they would help with documentation and browsing tools, and they
> > > wouldn't hurt anything.
>
> Well, unique constraints are a bit more complicated because they rely on
> an index, and we wouldn't e.g. maintain indexes with WHERE clauses or
> other expressions correctly.  I'd be a bit wary of declaring such
> indexes as actually being fully valid, because we have planner logic
> that does planning based on various constraints now, it'd certainly be
> annoying if some "re-check constraint" type queries would actually have
> their joins optimized away or such...
>
> > These constraints can slowdown creating/dropping database objects -
> mainly
> > temp tables.
>
> How so?
>

execution RI triggers

Regards

Pavel


>
> - Andres
>

Re: [HACKERS] v10beta pg_catalog diagrams

[Andres Freund]

On 2017-06-14 06:05:24 +0200, Pavel Stehule wrote:
> 2017-06-14 5:53 GMT+02:00 Peter Eisentraut  >:
> 
> > On 6/13/17 17:08, Andres Freund wrote:
> > > I wondered before if we shouldn't introduce "information only"
> > > unenforced foreign key constraints for the catalogs.  We kind of
> > > manually do that via oidjoins, it'd be nicer if we'd a function
> > > rechecking fkeys, and the fkeys were in the catalog...
> >
> > I don't see why we couldn't just add a full complement of primary and
> > foreign key constraints (and unique constraints and perhaps some check
> > constraints).  The argument is that they wouldn't normally do anything,
> > but they would help with documentation and browsing tools, and they
> > wouldn't hurt anything.

Well, unique constraints are a bit more complicated because they rely on
an index, and we wouldn't e.g. maintain indexes with WHERE clauses or
other expressions correctly.  I'd be a bit wary of declaring such
indexes as actually being fully valid, because we have planner logic
that does planning based on various constraints now, it'd certainly be
annoying if some "re-check constraint" type queries would actually have
their joins optimized away or such...

> These constraints can slowdown creating/dropping database objects - mainly
> temp tables.

How so?

- Andres


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

Re: [HACKERS] v10beta pg_catalog diagrams

[Pavel Stehule]

2017-06-14 5:53 GMT+02:00 Peter Eisentraut :

> On 6/13/17 17:08, Andres Freund wrote:
> > I wondered before if we shouldn't introduce "information only"
> > unenforced foreign key constraints for the catalogs.  We kind of
> > manually do that via oidjoins, it'd be nicer if we'd a function
> > rechecking fkeys, and the fkeys were in the catalog...
>
> I don't see why we couldn't just add a full complement of primary and
> foreign key constraints (and unique constraints and perhaps some check
> constraints).  The argument is that they wouldn't normally do anything,
> but they would help with documentation and browsing tools, and they
> wouldn't hurt anything.
>
>
These constraints can slowdown creating/dropping database objects - mainly
temp tables.

Regards

Pavel


> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] v10beta pg_catalog diagrams

[Peter Eisentraut]

On 6/13/17 17:08, Andres Freund wrote:
> I wondered before if we shouldn't introduce "information only"
> unenforced foreign key constraints for the catalogs.  We kind of
> manually do that via oidjoins, it'd be nicer if we'd a function
> rechecking fkeys, and the fkeys were in the catalog...

I don't see why we couldn't just add a full complement of primary and
foreign key constraints (and unique constraints and perhaps some check
constraints).  The argument is that they wouldn't normally do anything,
but they would help with documentation and browsing tools, and they
wouldn't hurt anything.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] v10beta pg_catalog diagrams

[Andres Freund]

Hi,

On 2017-06-12 11:28:39 -0400, Neil Anderson wrote:
> I'm cross posting from general. I did some work to diagram the relationships
> in pg_catalog for v10. I would like to add it to the developer FAQ here 
> https://wiki.postgresql.org/wiki/Developer_FAQ#Is_there_a_diagram_of_the_system_catalogs_available.3F
> but I thought I should check if people thought it appropriate and useful
> before I do?
> 
> https://www.postgrescompare.com/2017/06/11/pg_catalog_constraints.html

I wondered before if we shouldn't introduce "information only"
unenforced foreign key constraints for the catalogs.  We kind of
manually do that via oidjoins, it'd be nicer if we'd a function
rechecking fkeys, and the fkeys were in the catalog...

- Andres


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

Re: [HACKERS] v10beta pg_catalog diagrams

[Robert Haas]

On Tue, Jun 13, 2017 at 3:14 PM, Neil Anderson  wrote:
> There were a few relationships that I couldn't capture. Like where in
> pg_extension extconfig is an array of oids that refer to pg_class or where
> pg_depends could refer to basically any other system catalog, but it's
> mostly there and has all 62 tables from pg_catalog.

At the risk of tooting my own horn, if you happen to have a damaged
database where you think that the pseudo-foreign-key relationships
don't actually hold, you can run
https://github.com/EnterpriseDB/pg_catcheck to find the problems.  It
checks things like extconfig and pg_depend links, too.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] v10beta pg_catalog diagrams

[Neil Anderson]

On 2017-06-13 1:22 PM, Bruce Momjian wrote:

On Mon, Jun 12, 2017 at 04:07:35PM -0400, Peter Eisentraut wrote:

On 6/12/17 11:28, Neil Anderson wrote:

I'm cross posting from general. I did some work to diagram the
relationships in pg_catalog for v10. I would like to add it to the
developer FAQ here
https://wiki.postgresql.org/wiki/Developer_FAQ#Is_there_a_diagram_of_the_system_catalogs_available.3F
but I thought I should check if people thought it appropriate and useful
before I do?

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


Go for it.


Yeah, great.  We have been talking about adding diagrams to our
official docs but needed an updated toolchain, which I think we now
have, so there is a lot of opportunity for growth here.



Wonderful. I've added it.

There were a few relationships that I couldn't capture. Like where in 
pg_extension extconfig is an array of oids that refer to pg_class or 
where pg_depends could refer to basically any other system catalog, but 
it's mostly there and has all 62 tables from pg_catalog.


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


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

Re: [HACKERS] v10beta pg_catalog diagrams

[Bruce Momjian]

On Mon, Jun 12, 2017 at 04:07:35PM -0400, Peter Eisentraut wrote:
> On 6/12/17 11:28, Neil Anderson wrote:
> > I'm cross posting from general. I did some work to diagram the 
> > relationships in pg_catalog for v10. I would like to add it to the 
> > developer FAQ here 
> > https://wiki.postgresql.org/wiki/Developer_FAQ#Is_there_a_diagram_of_the_system_catalogs_available.3F
> >  
> > but I thought I should check if people thought it appropriate and useful 
> > before I do?
> > 
> > https://www.postgrescompare.com/2017/06/11/pg_catalog_constraints.html
> 
> Go for it.

Yeah, great.  We have been talking about adding diagrams to our
official docs but needed an updated toolchain, which I think we now
have, so there is a lot of opportunity for growth here.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


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

Re: [HACKERS] v10beta pg_catalog diagrams

[Peter Eisentraut]

On 6/12/17 11:28, Neil Anderson wrote:
> I'm cross posting from general. I did some work to diagram the 
> relationships in pg_catalog for v10. I would like to add it to the 
> developer FAQ here 
> https://wiki.postgresql.org/wiki/Developer_FAQ#Is_there_a_diagram_of_the_system_catalogs_available.3F
>  
> but I thought I should check if people thought it appropriate and useful 
> before I do?
> 
> https://www.postgrescompare.com/2017/06/11/pg_catalog_constraints.html

Go for it.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[HACKERS] v10beta pg_catalog diagrams

[Neil Anderson]

Hi,

I'm cross posting from general. I did some work to diagram the 
relationships in pg_catalog for v10. I would like to add it to the 
developer FAQ here 
https://wiki.postgresql.org/wiki/Developer_FAQ#Is_there_a_diagram_of_the_system_catalogs_available.3F 
but I thought I should check if people thought it appropriate and useful 
before I do?


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

Thanks,
Neil

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



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