Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jVuh4-0008Ru-1z for pgsql-docs@arkaria.postgresql.org; Tue, 05 May 2020 10:20:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jVuh2-0007xC-NL for pgsql-docs@arkaria.postgresql.org; Tue, 05 May 2020 10:20:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jVuh2-0007x5-GE for pgsql-docs@lists.postgresql.org; Tue, 05 May 2020 10:20:52 +0000 Received: from mout.kundenserver.de ([217.72.192.75]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jVugz-0003P3-Hz for pgsql-docs@lists.postgresql.org; Tue, 05 May 2020 10:20:52 +0000 Received: from [192.168.178.43] ([77.181.32.198]) by mrelayeu.kundenserver.de (mreue109 [212.227.15.145]) with ESMTPSA (Nemesis) id 1M1pk6-1jY6Y13IzI-002Hfh; Tue, 05 May 2020 12:20:45 +0200 Subject: Re: Documentation - chapter 52, system catalogs To: pgsql-docs@lists.postgresql.org, daelhithe@hotmail.com References: <158860579891.671.9854443057028888145@wrigleys.postgresql.org> From: =?UTF-8?Q?J=c3=bcrgen_Purtz?= Message-ID: Date: Tue, 5 May 2020 12:20:44 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.7.0 MIME-Version: 1.0 In-Reply-To: <158860579891.671.9854443057028888145@wrigleys.postgresql.org> Content-Type: multipart/mixed; boundary="------------D7EA49D67F40D5FB92EDB493" Content-Language: en-US X-Provags-ID: V03:K1:/7s4OiHY3SNieL7GQerRJ1dQ0qtr3t00+F1TCQkcIU8kiX1nLsq duK6SmbsaFXL2DdpC82l3Y0a3xFrFe/0sei/Bns8AxtEmgP1MzzuyB6QVUlrmJz6e4vDuUq alZgsGHzdlRsbG0n8W8EvOR9Q/j7l38ab9HlPbDwbAOr9xYv/S8o/QkbO9OQL213A1ytRnd jDKesChgO9aPtOB6guWRw== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:15KO3Fj227U=:8RbAw/jJ61jUp+eQmpEIlF SWv6pVD+37TZ5F8qpGIaTjnIIrJYfxZ5hkS0Sd38IvPttzwqHU0nyuOf/tRWjyoGvWQ2U5b7j sEQbRzB+UGhSvmJgZn6Rv3xl95gJ0D7kVncFSpuNwUkeBNBl1Y52jbc93ZTSdpG8FbuD36xTd h5pr0IbFYtfhoeW/Z5qHIRSSxxXfHTC9n4JWQ0Ywr9t+WK08wOrINSrjHBiD3B9NsrWLjP617 0KYNDU/DKJvDpY5Lkh9tgfgfKbS4EspYaoQ0PqwLC/9DVTQN/ITefl2yJZ+Up6LzoOZHiw4x6 Y5h0itUrr4CN/W6zvX4+ErBT7C75J3ae1uROYakF2HPhC9sn1nyM3IjAF4Q1mmtHOrmGsdm+v o74Kkpa8XmeMrnGCBGIjGUma5R+58udlQ62JAIH7CTsNWgtt+T7nqbLg1tUPNNf8pdzPvoKPS 7wJh0jfiGL8/0GeHjOnNmaUXa3M/mCxyZw/B8fvdMeDMR2RAleCzhcspymU2GGMjrFg3ZiXiX g+mfc9Oe4yE+pKD8sDvMwgRJi1NUGTH8P4kYrXXUJS4RSkoOA8ELJOvx5OEfdtW6RXCfk5tzK 0Lr3FKQsq2n6pXyXiUCVVcgCQlCSKD5X//FBDEp7wNCvYdXYpmao1ZbuhYH7sODk6A58/+SON Pqn4CUpoxt9hVq9uacJ5zHMy4+LRqj5RkE9b78CQH6Sg0sKuAFysuDGW6ZiWYqiljOLXc8sI8 1ktzOtWpwpfC7uPzp+DhsAFn2UK12p/1l7xt1lIcflU+rXFo7h5XBdtFvXFIuPZIqOjG/cXAd O8FWxYKaGvhCJNoGIGRFAZk5O71sKGuIGF4VJvxFVEET1G4JLorOZ4tjD0Da8ma6DSAa8ILnX 9SUQtw3B3pil/oHS923A== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------D7EA49D67F40D5FB92EDB493 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 04.05.20 17:23, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/11/catalogs-overview.html > Description: > > The documentation for chapter 52 does not clearly identify the schema > associated with the system catalogs in either the chapter header or overview > sections; had I not stumbled across a reference in the documentation for the > postgres_fdw to the pg_catalog search path, I would have been unable to > reference the content in those catalogs via foreign data wrappers, and > although the majority of the content has been exposed via the > information_schema views and tables, there remain a few elements of interest > that appear to only exist in the pg_catalog qualified content. I think that the topic "fdw" is only a symptom, not the real reason for the confusion. We can improve the chapters "System Catalog" and "Information Schema" in general by centralizing some already existing paragraphs in the "Overview" chapter and adding some more explanations. The attached patch contains: - for "System Catalog": moving paragraphs from bottom of 51. to top of 51.1. (in PG 11 it is chapter 52); explanation that "System Catalog" is a synonym for a concrete schema and its tables. - for "Information Schema": moving paragraphs from bottom of 36. to middle of 36.1. ; an explanation that it relies on the system catalog; change the title of 36.1. to "Overview" in correlation with "System Catalog". -- Jürgen Purtz --------------D7EA49D67F40D5FB92EDB493 Content-Type: text/x-patch; charset=UTF-8; name="0001-catalog.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-catalog.patch" diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index ce33df9e58..8854bda05f 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -6,6 +6,23 @@ System Catalogs + + Overview + + + The term system catalog is a widely used + synonym for the schema pg_catalog and + its tables. They are listed in + with links to more detailed documentation. + + + + Most system catalogs are copied from the template database during + database creation and are thereafter database-specific. A few + catalogs are physically shared across all databases in a cluster; + these are noted in the descriptions of the individual catalogs. + + The system catalogs are the place where a relational database management system stores schema metadata, such as information about @@ -23,21 +40,6 @@ of the system catalogs is ever decreasing. - - Overview - - - lists the system catalogs. - More detailed documentation of each catalog follows below. - - - - Most system catalogs are copied from the template database during - database creation and are thereafter database-specific. A few - catalogs are physically shared across all databases in a cluster; - these are noted in the descriptions of the individual catalogs. - - System Catalogs diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 7a995a1b64..ea8561d9e5 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -7,46 +7,8 @@ information schema - - The information schema consists of a set of views that contain - information about the objects defined in the current database. The - information schema is defined in the SQL standard and can therefore - be expected to be portable and remain stable — unlike the system - catalogs, which are specific to - PostgreSQL and are modeled after - implementation concerns. The information schema views do not, - however, contain information about - PostgreSQL-specific features; to inquire - about those you need to query the system catalogs or other - PostgreSQL-specific views. - - - - - When querying the database for constraint information, it is possible - for a standard-compliant query that expects to return one row to - return several. This is because the SQL standard requires constraint - names to be unique within a schema, but - PostgreSQL does not enforce this - restriction. PostgreSQL - automatically-generated constraint names avoid duplicates in the - same schema, but users can specify such duplicate names. - - - - This problem can appear when querying information schema views such - as check_constraint_routine_usage, - check_constraints, domain_constraints, and - referential_constraints. Some other views have similar - issues but contain the table name to help distinguish duplicate - rows, e.g., constraint_column_usage, - constraint_table_usage, table_constraints. - - - - - The Schema + Overview The information schema itself is a schema named @@ -57,6 +19,44 @@ the space savings achieved by that are minuscule). + + The information schema consists of a set of views to tables + in the schema pg_catalog, so it contains + information about the objects defined in the current database. The + information schema is defined in the SQL standard and can therefore + be expected to be portable and remain stable — unlike the system + catalogs, which are specific to + PostgreSQL and are modeled after + implementation concerns. The information schema views do not, + however, contain information about + PostgreSQL-specific features; to inquire + about those you need to query the system catalogs or other + PostgreSQL-specific views. + + + + + When querying the database for constraint information, it is possible + for a standard-compliant query that expects to return one row to + return several. This is because the SQL standard requires constraint + names to be unique within a schema, but + PostgreSQL does not enforce this + restriction. PostgreSQL + automatically-generated constraint names avoid duplicates in the + same schema, but users can specify such duplicate names. + + + + This problem can appear when querying information schema views such + as check_constraint_routine_usage, + check_constraints, domain_constraints, and + referential_constraints. Some other views have similar + issues but contain the table name to help distinguish duplicate + rows, e.g., constraint_column_usage, + constraint_table_usage, table_constraints. + + + By default, the information schema is not in the schema search path, so you need to access all objects in it through qualified @@ -65,6 +65,7 @@ should be careful if you want to put the information schema in the path. + --------------D7EA49D67F40D5FB92EDB493--