Thread: DOC: catalog.sgml
I little bit enhanced overview catalog tables. I added two new columns.
First one is OID of catalog table and second one contains attributes
which determine if the table is bootstrap, with oid and global.
I'm not able generate doc from sgml :( and verify if changes are ok :(.
Zdenek
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.130
diff -c -r2.130 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 25 Aug 2006 04:06:44 -0000 2.130
--- doc/src/sgml/catalogs.sgml 1 Sep 2006 12:42:47 -0000
***************
*** 26,32 ****
<para>
<xref linkend="catalog-table"> lists the system catalogs.
! More detailed documentation of each catalog follows below.
</para>
<para>
--- 26,35 ----
<para>
<xref linkend="catalog-table"> lists the system catalogs.
! More detailed documentation of each catalog follows below. Attributes
! column contains information how table was created. A bootstrap tables
! has attribut B. Table which has OID field has O. Shared/global tables
! are mark with letter G.
</para>
<para>
***************
*** 39,49 ****
<table id="catalog-table">
<title>System Catalogs</title>
! <tgroup cols="2">
<thead>
<row>
<entry>Catalog Name</entry>
<entry>Purpose</entry>
</row>
</thead>
--- 42,54 ----
<table id="catalog-table">
<title>System Catalogs</title>
! <tgroup cols="4">
<thead>
<row>
<entry>Catalog Name</entry>
<entry>Purpose</entry>
+ <entry>Oid</entry>
+ <entry>Attributes</entry>
</row>
</thead>
***************
*** 51,216 ****
--- 56,287 ----
<row>
<entry><link linkend="catalog-pg-aggregate"><structname>pg_aggregate</structname></link></entry>
<entry>aggregate functions</entry>
+ <entry>2600</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-am"><structname>pg_am</structname></link></entry>
<entry>index access methods</entry>
+ <entry>2601</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-amop"><structname>pg_amop</structname></link></entry>
<entry>access method operators</entry>
+ <entry>2602</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-amproc"><structname>pg_amproc</structname></link></entry>
<entry>access method support procedures</entry>
+ <entry>2603</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-attrdef"><structname>pg_attrdef</structname></link></entry>
<entry>column default values</entry>
+ <entry>2604</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link></entry>
<entry>table columns (<quote>attributes</quote>)</entry>
+ <entry>1249</entry>
+ <entry>B--</entry>
</row>
<row>
<entry><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link></entry>
<entry>authorization identifiers (roles)</entry>
+ <entry>1260</entry>
+ <entry>-OG</entry>
</row>
<row>
<entry><link linkend="catalog-pg-auth-members"><structname>pg_auth_members</structname></link></entry>
<entry>authorization identifier membership relationships</entry>
+ <entry>1261</entry>
+ <entry>--G</entry>
</row>
<row>
<entry><link linkend="catalog-pg-autovacuum"><structname>pg_autovacuum</structname></link></entry>
<entry>per-relation autovacuum configuration parameters</entry>
+ <entry>1248</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-cast"><structname>pg_cast</structname></link></entry>
<entry>casts (data type conversions)</entry>
+ <entry>2605</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-class"><structname>pg_class</structname></link></entry>
<entry>tables, indexes, sequences, views (<quote>relations</quote>)</entry>
+ <entry>1259</entry>
+ <entry>BO-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-constraint"><structname>pg_constraint</structname></link></entry>
<entry>check constraints, unique constraints, primary key constraints, foreign key constraints</entry>
+ <entry>2606</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-conversion"><structname>pg_conversion</structname></link></entry>
<entry>encoding conversion information</entry>
+ <entry>2607</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-database"><structname>pg_database</structname></link></entry>
<entry>databases within this database cluster</entry>
+ <entry>1262</entry>
+ <entry>-OG</entry>
</row>
<row>
<entry><link linkend="catalog-pg-depend"><structname>pg_depend</structname></link></entry>
<entry>dependencies between database objects</entry>
+ <entry>2608</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-description"><structname>pg_description</structname></link></entry>
<entry>descriptions or comments on database objects</entry>
+ <entry>2609</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-index"><structname>pg_index</structname></link></entry>
<entry>additional index information</entry>
+ <entry>2610</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-inherits"><structname>pg_inherits</structname></link></entry>
<entry>table inheritance hierarchy</entry>
+ <entry>2611</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-language"><structname>pg_language</structname></link></entry>
<entry>languages for writing functions</entry>
+ <entry>2612</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-largeobject"><structname>pg_largeobject</structname></link></entry>
<entry>large objects</entry>
+ <entry>2613</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-listener"><structname>pg_listener</structname></link></entry>
<entry>asynchronous notification support</entry>
+ <entry>2614</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link></entry>
<entry>schemas</entry>
+ <entry>2615</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link></entry>
<entry>index access method operator classes</entry>
+ <entry>2616</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link></entry>
<entry>operators</entry>
+ <entry>2617</entry>
+ <entry>-0-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link></entry>
<entry>template data for procedural languages</entry>
+ <entry>1136</entry>
+ <entry>--G</entry>
</row>
<row>
<entry><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link></entry>
<entry>functions and procedures</entry>
+ <entry>1255</entry>
+ <entry>BO-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-rewrite"><structname>pg_rewrite</structname></link></entry>
<entry>query rewrite rules</entry>
+ <entry>2618</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-shdepend"><structname>pg_shdepend</structname></link></entry>
<entry>dependencies on shared objects</entry>
+ <entry>1214</entry>
+ <entry>--G</entry>
</row>
<row>
<entry><link linkend="catalog-pg-shdescription"><structname>pg_shdescription</structname></link></entry>
<entry>comments on shared objects</entry>
+ <entry>2396</entry>
+ <entry>--G</entry>
</row>
<row>
<entry><link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link></entry>
<entry>planner statistics</entry>
+ <entry>2619</entry>
+ <entry>---</entry>
</row>
<row>
<entry><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link></entry>
<entry>tablespaces within this database cluster</entry>
+ <entry>1213</entry>
+ <entry>-OG</entry>
</row>
<row>
<entry><link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link></entry>
<entry>triggers</entry>
+ <entry>2620</entry>
+ <entry>-O-</entry>
</row>
<row>
<entry><link linkend="catalog-pg-type"><structname>pg_type</structname></link></entry>
<entry>data types</entry>
+ <entry>1247</entry>
+ <entry>BO-</entry>
</row>
</tbody>
</tgroup>
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> I little bit enhanced overview catalog tables. I added two new columns.
> First one is OID of catalog table and second one contains attributes
> which determine if the table is bootstrap, with oid and global.
Why is this a good idea? It seems like mere clutter.
regards, tom lane
Tom Lane wrote: > Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes: > > I little bit enhanced overview catalog tables. I added two new columns. > > First one is OID of catalog table and second one contains attributes > > which determine if the table is bootstrap, with oid and global. > > Why is this a good idea? It seems like mere clutter. What's "global"? A maybe-useful flag would be telling that a table is shared. Is that it? Mind you, it's not useful to me because I know which tables are shared, but I guess for someone not so familiar with the catalogs it could have some use. The OIDs may be useful to people inspecting pg_depend, for example; but then, it's foolish not to be using regclass in that case. Whether a table is "bootstrap" or not doesn't seem useful to me. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Fri, Sep 01, 2006 at 12:36:11PM -0400, Alvaro Herrera wrote: > Tom Lane wrote: > > Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes: > > > I little bit enhanced overview catalog tables. I added two new columns. > > > First one is OID of catalog table and second one contains attributes > > > which determine if the table is bootstrap, with oid and global. > > > > Why is this a good idea? It seems like mere clutter. > > What's "global"? A maybe-useful flag would be telling that a table is > shared. Is that it? Mind you, it's not useful to me because I know > which tables are shared, but I guess for someone not so familiar with > the catalogs it could have some use. > > The OIDs may be useful to people inspecting pg_depend, for example; but > then, it's foolish not to be using regclass in that case. > > Whether a table is "bootstrap" or not doesn't seem useful to me. Something that might be handy would be a method to determine if an object is a system object or not (perhaps what the OP means by bootstrap). We spent quite some time figuring out how to handle that when we were working on newsysviews. In that case, we wanted the info because it's handy to be able to query a view that's not cluttered up with a bunch of system-defined stuff. Having a way to get a list of only user-defined functions, for example. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
"Jim C. Nasby" <jim@nasby.net> writes:
> On Fri, Sep 01, 2006 at 12:36:11PM -0400, Alvaro Herrera wrote:
>> Whether a table is "bootstrap" or not doesn't seem useful to me.
> Something that might be handy would be a method to determine if an
> object is a system object or not (perhaps what the OP means by
> bootstrap).
No, what the OP meant by bootstrap is "one of the four core
BKI_BOOTSTRAP catalogs, and the reason they're so core is that bootstrap
mode itself doesn't really work till they exist". (I agree with Alvaro
that by the time you get interested in what BKI_BOOTSTRAP does, you
probably don't need to have any hand-holding from catalogs.sgml; you're
already at least an apprentice wizard.)
But ever since 7.3 the convention for identifying system objects has
been pretty well-defined: anything that lives in one of the predefined
schemas. What problem were you having using that approach in
newsysviews?
regards, tom lane
On Sun, Sep 03, 2006 at 12:01:06AM -0400, Tom Lane wrote: > But ever since 7.3 the convention for identifying system objects has > been pretty well-defined: anything that lives in one of the predefined > schemas. What problem were you having using that approach in > newsysviews? It was just an issue of trawling through pg_dump to confirm that. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
On 2006-09-03, Tom Lane <tgl@sss.pgh.pa.us> wrote: > But ever since 7.3 the convention for identifying system objects has > been pretty well-defined: anything that lives in one of the predefined > schemas. What problem were you having using that approach in > newsysviews? Not every kind of object lives in a schema. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Tom Lane napsal(a):
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> I little bit enhanced overview catalog tables. I added two new columns.
>> First one is OID of catalog table and second one contains attributes
>> which determine if the table is bootstrap, with oid and global.
>
> Why is this a good idea? It seems like mere clutter.
>
I'm working on pg_upgrade and these information are important for me and
I think that They should be interest some else.
You can easy determine the file related to if you know the OID.
Specially when database is shutdown is good to have some information
source. If catalog table is global/share or local is very important and
it is not mentioned anywhere. If it is created with oid or bootstrap it
is not important for standard purpose, it is only for fullness.
I know that people who hacking postgres ten years know this, however it
is internals chapter and for newbies it should be useful. And by the way
it is documentation and this is completion of information. You can say
why we have page layout there because it is described in the source code
and so on...
Zdenek
Alvaro Herrera wrote:
> Tom Lane wrote:
>> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>>> I little bit enhanced overview catalog tables. I added two new columns.
>>> First one is OID of catalog table and second one contains attributes
>>> which determine if the table is bootstrap, with oid and global.
>> Why is this a good idea? It seems like mere clutter.
>
> What's "global"? A maybe-useful flag would be telling that a table is
> shared. Is that it? Mind you, it's not useful to me because I know
> which tables are shared, but I guess for someone not so familiar with
> the catalogs it could have some use.
Global means share table stored in global directory :-). Ok I change it.
Thanks for comment Zdenek
Zdenek Kotala wrote:
> Alvaro Herrera wrote:
>> What's "global"? A maybe-useful flag would be telling that a table is
>> shared. Is that it? Mind you, it's not useful to me because I know
>> which tables are shared, but I guess for someone not so familiar with
>> the catalogs it could have some use.
>
> Global means share table stored in global directory :-). Ok I change it.
>
There is new version of catalogs overview patch. This version add only
one column into overview table which contains Oid/Filename for each
catalog table. Oid information is important if someone need make
relation with filename on disk and related catalog table.
In this column shared table are marked as well. Other information like
bootstrap and with-oids are really useless and they is not mentioned.
Zdenek
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.134
diff -c -r2.134 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 22 Sep 2006 23:20:13 -0000 2.134
--- doc/src/sgml/catalogs.sgml 4 Oct 2006 12:17:49 -0000
***************
*** 33,47 ****
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.
</para>
<table id="catalog-table">
<title>System Catalogs</title>
! <tgroup cols="2">
<thead>
<row>
<entry>Catalog Name</entry>
<entry>Purpose</entry>
</row>
--- 33,49 ----
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 Oid/Filename column and in the descriptions
! of the individual catalogs.
</para>
<table id="catalog-table">
<title>System Catalogs</title>
! <tgroup cols="3">
<thead>
<row>
+ <entry>Oid/Filename</entry>
<entry>Catalog Name</entry>
<entry>Purpose</entry>
</row>
***************
*** 49,214 ****
--- 51,249 ----
<tbody>
<row>
+ <entry>2600</entry>
<entry><link linkend="catalog-pg-aggregate"><structname>pg_aggregate</structname></link></entry>
<entry>aggregate functions</entry>
</row>
<row>
+ <entry>2601</entry>
<entry><link linkend="catalog-pg-am"><structname>pg_am</structname></link></entry>
<entry>index access methods</entry>
</row>
<row>
+ <entry>2602</entry>
<entry><link linkend="catalog-pg-amop"><structname>pg_amop</structname></link></entry>
<entry>access method operators</entry>
</row>
<row>
+ <entry>2603</entry>
<entry><link linkend="catalog-pg-amproc"><structname>pg_amproc</structname></link></entry>
<entry>access method support procedures</entry>
</row>
<row>
+ <entry>2604</entry>
<entry><link linkend="catalog-pg-attrdef"><structname>pg_attrdef</structname></link></entry>
<entry>column default values</entry>
</row>
<row>
+ <entry>1249</entry>
<entry><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link></entry>
<entry>table columns (<quote>attributes</quote>)</entry>
</row>
<row>
+ <entry>1260 (shared)</entry>
<entry><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link></entry>
<entry>authorization identifiers (roles)</entry>
</row>
<row>
+ <entry>1261 (shared)</entry>
<entry><link linkend="catalog-pg-auth-members"><structname>pg_auth_members</structname></link></entry>
<entry>authorization identifier membership relationships</entry>
</row>
<row>
+ <entry>1248</entry>
<entry><link linkend="catalog-pg-autovacuum"><structname>pg_autovacuum</structname></link></entry>
<entry>per-relation autovacuum configuration parameters</entry>
</row>
<row>
+ <entry>2605</entry>
<entry><link linkend="catalog-pg-cast"><structname>pg_cast</structname></link></entry>
<entry>casts (data type conversions)</entry>
</row>
<row>
+ <entry>1259</entry>
<entry><link linkend="catalog-pg-class"><structname>pg_class</structname></link></entry>
<entry>tables, indexes, sequences, views (<quote>relations</quote>)</entry>
</row>
<row>
+ <entry>2606</entry>
<entry><link linkend="catalog-pg-constraint"><structname>pg_constraint</structname></link></entry>
<entry>check constraints, unique constraints, primary key constraints, foreign key constraints</entry>
</row>
<row>
+ <entry>2607</entry>
<entry><link linkend="catalog-pg-conversion"><structname>pg_conversion</structname></link></entry>
<entry>encoding conversion information</entry>
</row>
<row>
+ <entry>1262 (shared)</entry>
<entry><link linkend="catalog-pg-database"><structname>pg_database</structname></link></entry>
<entry>databases within this database cluster</entry>
</row>
<row>
+ <entry>2608</entry>
<entry><link linkend="catalog-pg-depend"><structname>pg_depend</structname></link></entry>
<entry>dependencies between database objects</entry>
</row>
<row>
+ <entry>2609</entry>
<entry><link linkend="catalog-pg-description"><structname>pg_description</structname></link></entry>
<entry>descriptions or comments on database objects</entry>
</row>
<row>
+ <entry>2610</entry>
<entry><link linkend="catalog-pg-index"><structname>pg_index</structname></link></entry>
<entry>additional index information</entry>
</row>
<row>
+ <entry>2611</entry>
<entry><link linkend="catalog-pg-inherits"><structname>pg_inherits</structname></link></entry>
<entry>table inheritance hierarchy</entry>
</row>
<row>
+ <entry>2612</entry>
<entry><link linkend="catalog-pg-language"><structname>pg_language</structname></link></entry>
<entry>languages for writing functions</entry>
</row>
<row>
+ <entry>2613</entry>
<entry><link linkend="catalog-pg-largeobject"><structname>pg_largeobject</structname></link></entry>
<entry>large objects</entry>
</row>
<row>
+ <entry>2614</entry>
<entry><link linkend="catalog-pg-listener"><structname>pg_listener</structname></link></entry>
<entry>asynchronous notification support</entry>
</row>
<row>
+ <entry>2615</entry>
<entry><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link></entry>
<entry>schemas</entry>
</row>
<row>
+ <entry>2616</entry>
<entry><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link></entry>
<entry>index access method operator classes</entry>
</row>
<row>
+ <entry>2617</entry>
<entry><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link></entry>
<entry>operators</entry>
</row>
<row>
+ <entry>1136 (shared)</entry>
<entry><link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link></entry>
<entry>template data for procedural languages</entry>
</row>
<row>
+ <entry>1255</entry>
<entry><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link></entry>
<entry>functions and procedures</entry>
</row>
<row>
+ <entry>2618</entry>
<entry><link linkend="catalog-pg-rewrite"><structname>pg_rewrite</structname></link></entry>
<entry>query rewrite rules</entry>
</row>
<row>
+ <entry>1214 (shared)</entry>
<entry><link linkend="catalog-pg-shdepend"><structname>pg_shdepend</structname></link></entry>
<entry>dependencies on shared objects</entry>
</row>
<row>
+ <entry>2396 (shared)</entry>
<entry><link linkend="catalog-pg-shdescription"><structname>pg_shdescription</structname></link></entry>
<entry>comments on shared objects</entry>
</row>
<row>
+ <entry>2619</entry>
<entry><link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link></entry>
<entry>planner statistics</entry>
</row>
<row>
+ <entry>1213 (shared)</entry>
<entry><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link></entry>
<entry>tablespaces within this database cluster</entry>
</row>
<row>
+ <entry>2620</entry>
<entry><link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link></entry>
<entry>triggers</entry>
</row>
<row>
+ <entry>1247</entry>
<entry><link linkend="catalog-pg-type"><structname>pg_type</structname></link></entry>
<entry>data types</entry>
</row>
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> There is new version of catalogs overview patch. This version add only
> one column into overview table which contains Oid/Filename for each
> catalog table. Oid information is important if someone need make
> relation with filename on disk and related catalog table.
I still say this is just confusing clutter. The proposed patch even
goes so far as to give the OID pride of place as the most important
item you could possibly want to know about a catalog, which is surely
silly.
People who actually want to know this information can look into the
pg_class catalog, which has the advantages of being complete (eg, it
covers indexes too), guaranteed up-to-date, and easily program-readable.
I really do not see the value of putting it in the sgml docs.
regards, tom lane
Tom Lane wrote:
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> There is new version of catalogs overview patch. This version add only
>> one column into overview table which contains Oid/Filename for each
>> catalog table. Oid information is important if someone need make
>> relation with filename on disk and related catalog table.
>
> I still say this is just confusing clutter. The proposed patch even
> goes so far as to give the OID pride of place as the most important
> item you could possibly want to know about a catalog, which is surely
> silly.
You have right that OID is not important information in many cases, but
how I said It is useful when you want know relation between filename and
catalog table.
> People who actually want to know this information can look into the
> pg_class catalog, which has the advantages of being complete (eg, it
> covers indexes too), guaranteed up-to-date, and easily program-readable.
> I really do not see the value of putting it in the sgml docs.
You can look into pg_class catalog only if database is running. If you
have some data corruption problem, OID should help during recovery. But
you have right, that pg_class have complex information and who want to
"play" with datafiles, he must know more than OID.
OK, thanks for response, forget to this patch
Zdenek