System Tables
| This section will give you a short overview about PostgreSQL's system tables. As with most advanced database systems, PostgreSQL stores a major part of information concerning the database itself in system tables. This makes it very easy to access this data for analyzing, debugging, or any other purpose. This is very comfortable because the user can hardly mix up his own tables with PostgreSQL's system tables. The following is an overview of the system tables. We can get the following list by typing "\d pg_ and clicking the Tab button in the psql 's command line: persons=# \ d pg_ pg_aggregate pg_class pg_inheritproc pg_operator pg_statistic pg_am pg_database pg_inherits pg_proc pg_tables pg_amop pg_description pg_ipl pg_relcheck pg_trigger pg_amproc pg_group pg_language pg_rewrite pg_type pg_attrdef pg_index pg_listener pg_rules pg_user pg_attribute pg_indexes pg_opclass pg_shadow pg_views We have already dealt with some of the tables listed here, but let's go into detail now. In the next few sections, we will give you an overview of the most important system tables (this is NOT a complete reference). This will make you understand many things about PostgreSQL's behavior in a better way. pg_aggregatepg_aggregate is used to store all information about PostgreSQL's aggregate functions. The following is the definition of the table: persons=# \d pg_aggregate Table "pg_aggregate" Attribute Type Modifier ---------------+---------+---------- aggname name aggowner integer aggtransfn1 regproc aggtransfn2 regproc aggfinalfn regproc aggbasetype oid aggtranstype1 oid aggtranstype2 oid aggfinaltype oid agginitval1 text agginitval2 text Index: pg_aggregate_name_type_index You can see in the definition of the table that every aggregate function has a name and an owner. This is especially important when working in multiuser environments. The default value of all predefined aggregate functions is the user ID of user postgres . By default, PostgreSQL offers five aggregate functions. You can see that those functions can be found in pg_aggregate : persons=# SELECT DISTINCT aggname FROM pg_aggregate; aggname --------- avg count max min sum (5 rows) Users can define their own aggregate functions. If a user does, his functions will be listed in pg_aggregate too. pg_attributeAll columns stored in the database are listed in this table. If a user wants to have precise information about a specific column, he can query the pg_attribute table to find out more than with a simple \d tablename command. If you take a closer look at the data structure, you can see which attributes of a column are stored in this table: persons=# \d pg_attribute Table "pg_attribute" Attribute Type Modifier ---------------+----------+---------- attrelid oid attname name atttypid oid attdisbursion float4 attlen smallint attnum smallint attnelems integer attcacheoff integer atttypmod integer attbyval boolean attstorage char attisset boolean attalign char attnotnull boolean atthasdef boolean Indices: pg_attribute_relid_attnam_index, pg_attribute_relid_attnum_index Many important attributes are stored in this table. For example, attnotnull defines whether a table can contain NULL values. pg_classpg_class contains all classes defined in a database. In PostgreSQL, tables are classes, and information about tables is stored in pg_class . pg_class contains a lot of attributes of a class: persons=# \d pg_class Table "pg_class" Attribute Type Modifier --------------+-----------+---------- relname name reltype oid relowner integer relam oid relpages integer reltuples integer rellongrelid oid relhasindex boolean relisshared boolean relkind char relnatts smallint relchecks smallint reltriggers smallint relukeys smallint relfkeys smallint relrefs smallint relhaspkey boolean relhasrules boolean relacl aclitem[] Indices: pg_class_oid_index, pg_class_relname_index To give you an impression of how data is stored in this system table, we have selected some information from pg_class about the programmers table we have used before: persons=# SELECT relname, relowner, relpages, reltriggers FROM pg_class WHERE relname='programmers'; relname relowner relpages reltriggers -------------+----------+----------+------------- programmers 26 10 0 (1 row) You can see that PostgreSQL stores some very important attributes, such as the name of the class, the ID of the owner (in the previous example, the table belongs to user postgres ), the number of pages the classes need to be stored (a table is usually 8KB in PostgreSQL), and the number of triggers defined on a table. In the data structure, you can see that a class belongs to a certain type of classes. This flag is very useful because it offers developers great flexibility. pg_databasepg_database contains a list of all databases that can be accessed by the postmaster. Every postmaster running must have a separate data directory. We can see a list of all databases that are in the data directory we are currently accessing using a postmaster process. The data structure of pg_database is indeed an easy one. It contains only the name of the database, the ID of the database administrator (in our case, postgres ), information about encoding, and the path to the actual data within the base directory of the current data directory: persons=# \d pg_database Table "pg_database" Attribute Type Modifier -----------+---------+---------- datname name datdba integer encoding integer datpath text To make this description a little clearer, we have included a simple SELECT statement that shows all the databases we currently have on our system: persons=# SELECT * FROM pg_database ; datname datdba encoding datpath -----------+--------+----------+----------- template1 26 0 template1 persons 26 0 persons (2 rows) pg_descriptionIn PostgreSQL, objects can have a description. The use of descriptions is especially useful when database administrators and developers want to know how things work when they have to deal with data structures and code they have written a while ago. In general, documentation is very often seen as burden , but it isn't. Documentation can save you a lot of time and is extremely necessary, especially when multiple developers are working on one project. PostgreSQL, compared to other systems, is a very well documented piece of software. PostgreSQL's source code is structured clearly and contains a lot of documentation. One of Vienna's best C hackers (Erich Fr ¼hst ¼ck ”a true friend) said once that studying PostgreSQL server code is the best method to learn writing C code properly. I think he is right. If your data structures and your code is as clear as it should be, using and extending your code should be a rather easy task. But let's get back to pg_descritpption . The structure of the table is also very easy: persons=# \d pg_description Table "pg_description" Attribute Type Modifier -------------+------+---------- objoid oid description text Index: pg_description_objoid_index Every object can have a description. Objects are accessed via a unique ID and, because PostgreSQL is an object-relational database system, the list of objects stored in this table is truly a long one. For example, we can find the description of PostgreSQL's data types. The following is a short extract from the list: 600 geometric point '(x, y)' 601 geometric line segment '(pt1,pt2)' 602 geometric path '(pt1,...)' 603 geometric box '(lower left,upper right)' 604 geometric polygon '(pt1,...)' You can see that this extract contains the same text we can find in the list of PostgreSQL's data types (try \dT ) we have included earlier in the book. For example, if we add a description of a column to a table, the text would also appear in table pg_description . pg_grouppg_groups is used to manage the user groups of a database. Multiple users can be assigned to a group . persons=# \d pg_group Table "pg_group" Attribute Type Modifier -----------+-----------+---------- groname name grosysid integer grolist integer[] Indices: pg_group_name_index, pg_group_sysid_index pg_index and pg_indexesThe attributes of the indexes you are using in your database are stored in the pg_index table. pg_indexes offers a view that allows access to columns from pg_index and pg_class . In the following, we have compiled the data structure of pg_index and the definition of pg_indexes : persons=# \d pg_index Table "pg_index" Attribute Type Modifier ----------------+------------+---------- indexrelid oid indrelid oid indproc oid indkey int2vector indclass oidvector indisclustered boolean indislossy boolean indhaskeytype boolean indisunique boolean indisprimary boolean indreference oid indpred text Index: pg_index_indexrelid_index persons=# \d pg_indexes View "pg_indexes" Attribute Type Modifier -----------+------+---------- tablename name indexname name indexdef text View definition: SELECT c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(x.indexrelid) AS indexdef FROM pg_index x, pg_class c, pg_class i WHERE ((c.oid = x.indrelid) AND (i.oid = x.indexrelid)); As you can see, all important parameters of the CREATE INDEX command are nothing more and nothing less than the columns in the pg_index table. The view pg_indexes is used to perform a join of table pg_index and pg_class . pg_inherits , pg_inheritprocpg_inherits and pg_inheritoproc are used to store information about inheritance. pg_inherits stores the IDs of an object and the ID of the parent. If an object has multiple parents, it has multiple entries that are consecutively numbered in column inhseqno . The data structure of pg_inherits consists of only three columns: persons=# \d pg_inherits Table "pg_inherits" Attribute Type Modifier -----------+---------+---------- inhrelid oid inhparent oid inhseqno integer Index: pg_inherits_relid_seqno_index pg_inheritproc stores information about inheritance concerning functions. We have included the definition of the table: persons=# \d pg_inheritproc Table "pg_inheritproc" Attribute Type Modifier -------------+------+---------- inhproname name inhargrelid oid inhdefrelid oid inhproc oid pg_languagepg_language stores information about the languages a database supports. The following is an example of a PL/pgSQL-enabled database: persons=# SELECT * FROM pg_language; lanname lanispl lanpltrusted lanplcallfoid lancompiler ----------+---------+--------------+---------------+------------- internal f f 0 n/a C f f 0 /bin/cc sql f f 0 postgres plpgsql t t 37856 PL/pgSQL (4 rows) This example contains one record for PL/pgSQL; all other lines are default values. pg_operatorpg_operator is an extremely important table in a PostgreSQL system. It stores information about operators, as you can see in the following listing of the data structure: persons=# \d pg_operator Table "pg_operator" Attribute Type Modifier ------------+----------+---------- oprname name oprowner integer oprprec smallint oprkind char oprisleft boolean oprcanhash boolean oprleft oid oprright oid oprresult oid oprcom oid oprnegate oid oprlsortop oid oprrsortop oid oprcode regproc oprrest regproc oprjoin regproc Indices: pg_operator_oid_index, pg_operator_oprname_l_r_k_index We won't go too deeply into detail because pg_operator contains a lot of information about operators that would lead too far. We only want to point out how big the table is. This shows the power of PostgreSQL and how many operators are defined for nearly all demands a modern database system may have to face. The default installation of PostgreSQL (7.0.3) has 559 predefined records in pg_operator . 59 of these are used for the = operator. You can see that = is defined for a lot of different data types. pg_procpg_proc contains the list of functions supported by your database. A function can have multiple overloadings. The data structure of the pg_proc table looks as follows : persons=# \d pg_proc Table "pg_proc" Attribute Type Modifier ----------------+-----------+---------- proname name proowner integer prolang oid proisinh boolean proistrusted boolean proiscachable boolean pronargs smallint proretset boolean prorettype oid proargtypes oidvector probyte_pct integer properbyte_cpu integer propercall_cpu integer prooutin_ratio integer prosrc text probin bytea Indices: pg_proc_oid_index, pg_proc_proname_narg_type_index pg_shadowpg_shadow is the most important table when dealing with user accounts. In most cases, pg_shadow is referenced via pg_user , a view that contains the most important information. pg_shadow contains the following fields: persons=# \d pg_shadow Table "pg_shadow" Attribute Type Modifier -------------+---------+---------- usename name usesysid integer usecreatedb boolean usetrace boolean usesuper boolean usecatupd boolean passwd text valuntil abstime You can see that these fields contain all information usually required when adding a new user using the CREATE USER command. pg_statisticspg_statistics is an extremely important table for PostgreSQL's optimizer. With the help of this table, PostgreSQL tries to find the best way of executing a query. It is not necessary to understand every detail of pg_statistics , but it is important to see what data PostgreSQL uses to find its way through an SQL-query: persons=# \d pg_statistic Table "pg_statistic" Attribute Type Modifier ---------------+----------+---------- starelid oid staattnum smallint staop oid stanullfrac float4 stacommonfrac float4 stacommonval text staloval text stahival text Index: pg_statistic_relid_att_index pg_tablesAs you can see in the following definition, pg_tables is a view that allows comfortable access to data related to tables. persons=# \d pg_tables View "pg_tables" Attribute Type Modifier -------------+---------+---------- tablename name tableowner name hasindexes boolean hasrules boolean hastriggers boolean View definition: SELECT c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, (c.reltriggers > 0) AS hastriggers FROM pg_class c WHERE (((c.relkind = 'r'::"char") OR (c.relkind = 's'::"char")) AND (NOT (EXISTS (SELECT pg_rewrite.rulename FROM pg_rewrite WHERE ((pg_rewrite.ev_class = c.oid) AND (pg_rewrite.ev_type = '1'::"char")))))); pg_triggerInformation about triggers can be found in pg_trigger . The following is an overview of the data structure. persons=# \d pg_trigger Table "pg_trigger" Attribute Type Modifier ----------------+------------+---------- tgrelid oid tgname name tgfoid oid tgtype smallint tgenabled boolean tgisconstraint boolean tgconstrname name tgconstrrelid oid tgdeferrable boolean tginitdeferred boolean tgnargs smallint tgattr int2vector tgargs bytea Indices: pg_trigger_tgconstrname_index, pg_trigger_tgconstrrelid_index, pg_trigger_tgrelid_index  |
