Chapter 18. System Catalogs
Overview
The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information. PostgreSQL's system catalogs are regular tables. You can drop and recreate these tables, add columns to them, and insert and update values. However, this can cause severe system damage and data loss. Normally one never has to change the system catalogs by hand; there are always SQL commands to do that. (For example, CREATE DATABASE both inserts a row into the pg_database catalog and creates the database on disk.) However, there are some exceptions for esoteric operations, such as adding index access methods.
Table 18-1. System Catalogs
| Catalog Name | Purpose |
|---|---|
| pg_aggregate | aggregate functions |
| pg_am | index access methods |
| pg_amop | access method operators |
| pg_amproc | access method support procedures |
| pg_attrdef | column default values |
| pg_attribute | table columns (attributes, fields) |
| pg_class | tables, indexes, sequences ("relations") |
| pg_database | databases |
| pg_description | descriptions or comments on database objects |
| pg_group | user groups |
| pg_index | additional index information |
| pg_inheritproc | (not used) |
| pg_inherits | table inheritance hierarchy |
| pg_ipl | (not used) |
| pg_language | languages for writing functions |
| pg_largeobject | large objects |
| pg_listener | asynchronous notification |
| pg_opclass | index access method operator classes |
| pg_operator | operators |
| pg_proc | functions and procedures |
| pg_relcheck | check constraints |
| pg_rewrite | query rewriter rules |
| pg_shadow | database users |
| pg_statistic | optimizer statistics |
| pg_trigger | triggers |
| pg_type | data types |
More detailed documentation of most catalogs follow.
In the description of the catalog tables there are two fixed-length character types that are reserved for use only in the system catalog. These are the "char" and name types. The name type exists only for storage of internal catalog names and is not intended for use by the general user. Its length is currently defined as 32 bytes (31 characters plus terminator) but should be referenced using the macro NAMEDATALEN. The length is set at compile time (and is therefore adjustable for special uses); the default maximum length may change in a future release. The type "char" (note the quotes) is different from char(1) in that it uses only 1 byte of storage. It is internally used in the system catalogs as an enumeration type.
Table 18-2. Specialty Character Types
| Type Name | Storage | Description |
|---|---|---|
| "char" | 1 byte | Single-character internal type |
| name | 32 bytes | Thirty-one character internal type |
pg_aggregate
pg_aggregate stores information about aggregate functions. An aggregate function is a function that operates on a set of values (typically one column from each the row that matches a query condition) and returns a single value computed from all these values. Typical aggregate functions are sum, count, and max.
Table 18-3. pg_aggregate Columns
| Name | Type | References | Description |
|---|---|---|---|
| aggname | name | Name of the aggregate function | |
| aggowner | int4 | pg_shadow.usesysid | Owner (creator) of the aggregate function |
| aggtransfn | regproc (function) | Transition function | |
| aggfinalfn | regproc (function) | Final function | |
| aggbasetype | oid | pg_type.oid | The type on which this function operates when invoked from SQL |
| aggtranstype | oid | pg_type.oid | The type of the aggregate function's internal transition (state) data |
| aggfinaltype | oid | pg_type.oid | The type of the result |
| agginitval | text | The initial value of the transition state. This is a text field that will be cast to the type of aggtranstype. |
New aggregate functions are registered with the CREATE AGGREGATE command.
An aggregate function is identified through name and argument type. Hence aggname and aggname are the composite primary key.
pg_attrdef
pg_attrdef stores column default values. The main information about columns is stored in pg_attribute (see below). Only columns that explicitly specify a default value (when the table is created or the column is added) have an entry here.
Table 18-4. pg_attrdef Columns
| Name | Type | References | Description |
|---|---|---|---|
| adrelid | oid | pg_class.oid | The table to which this column belongs |
| adnum | int2 | The number of the column; see pg_attribute.pg_attnum | |
| adbin | text | An internal representation of the column default value | |
| adsrc | text | A human-readable representation of the default value |
pg_attribute
pg_attribute stores information about table columns. There will be exactly one pg_attribute row for every column in every table in the database. (There will also be attribute entries for indexes and other objects. See pg_class.)
The term attribute is equivalent to column and is used for historical reasons.
Table 18-5. pg_attribute Columns
| Name | Type | References | Description |
|---|---|---|---|
| attrelid | oid | pg_class.oid | The table to which this column belongs |
| attname | name | Column name | |
| atttypid | oid | pg_type.oid | The data type of this column |
| attdispersion | float4 | Statistical dispersion. See "Notes on pg_attribute columns", which follows this table. | |
| attlen | int2 | This is a copy of the pg_type.typlen for this column's type. | |
| attnum | int2 | The column number. See "Notes on pg_attribute columns", which follows this table. | |
| attnelems | int4 | Number of dimensions, if the column is an array | |
| attcacheoff | int4 | Code offset. See "Notes on pg_attribute columns", which follows this table. | |
| atttypmod | int4 | Type modifier. See "Notes on pg_attribute columns", which follows this table. | |
| attbyval | bool | A copy of pg_type.typbyval of this column's type | |
| attstorage | char | A copy of pg_type.typstorage of this column's type | |
| attisset | bool | This attribute is a set. See "Notes on pg_attribute columns", which follows this table. | |
| attalign | char | A copy of pg_type.typalign of this column's type | |
| attnotnull | bool | This represents a NOT NULL constraint. It is possible to change this field to enable or disable the constraint. | |
| atthasdef | bool | This column has a default value, in which case there will be a corresponding entry in the pg_attrdef catalog that actually defines the value. |
Notes on pg_attribute Columns
- attdispersion
attdispersion is the dispersion statistic of the column (0.0 to 1.0), or zero if the statistic has not been calculated, or -1.0 if VACUUM found that the column contains no duplicate entries (in which case the dispersion should be taken as 1.0/numberOfRows for the current table size).
- attnum
Ordinary columns are numbered from 1 up. System columns, such as oid, have (arbitrary) negative numbers.
- attcacheoff
The code offset is always -1 in storage, but when loaded into a tuple descriptor in memory this may be updated cache the offset of the attribute within the tuple.
- atttypmod
atttypmod records type-specific data supplied at table creation time (for example, the maximum length of a varchar column). It is passed to type-specific input and output functions as the third argument. The value will generally be -1 for types that do not need typmod.
- attiset
When true, attiset indicates that this attribute is a set. In that case, what is really stored in the attribute is the OID of a tuple in the pg_proc catalog. The pg_proc tuple contains the query string that defines this set (that is, the query to run to get the set). So the atttypid (see above) refers to the type returned by this query, but the actual length of this attribute is the length (size) of an oid.
pg_class
pg_class catalogs tables and mostly everything else that has columns or is otherwise similar to a table. This includes indexes, sequences, views, and some kinds of special relation kinds. Not all fields are meaningful for all relation types.
Table 18-6. pg_class Columns
| Name | Type | References | Description |
|---|---|---|---|
| relname | name | Name of the table, index, view, etc. | |
| reltype | oid | pg_type.oid | The data type that corresponds to this table (not functional, only set for system tables) |
| relowner | int4 | pg_shadow.usesysid | Owner of the relation |
| relam | oid | pg_am.oid | If this is an index, the access method used (btree, hash, etc.) |
| relfilenode | oid | Name of the on-disk file of this relation | |
| relpages | int4 | Size of the on-disk representation of this table in pages (size BLCKSZ). This is only an approximate value which is calculated during VACUUM. | |
| reltuples | int4 | Number of tuples in the table. This is only an estimate used by the planner, updated by VACUUM. | |
| reltoastrelid | oid | pg_class.oid | Oid of the TOAST table associated with this table, 0 if none. The TOAST table stores large attributes "out of line" in a secondary table. |
| reltoastidxid | oid | pg_class.oid | Oid of the index on the TOAST table for this table, 0 if none |
| relhasindex | bool | True if this is a table and it has at least one index | |
| relisshared | bool | Reserved. (This is not what it may appear to be.) | |
| relkind | char | Table type. Possible vales are: 'r' = ordinary table, 'i' = index, 'S' = sequence, 'v' = view, 's' = special, 't' = secondary TOAST table | |
| relnatts | int2 | Number of columns in the relation, besides system columns. There must be this many corresponding entries in pg_attribute. See also pg_attribute.attnum. | |
| relchecks | int2 | Number of check constraints on the table; see pg_relcheck catalog | |
| reltriggers | int2 | Number of triggers on the table; see pg_trigger catalog | |
| relukeys | int2 | Reserved. (This is not what it may appear to be.) | |
| relfkeys | int2 | Number foreign keys on the table | |
| relhaspkey | bool | Reserved; currently unused. | |
| relhasrules | bool | Whether a table has rules. | |
| relhassubclass | bool | True if at least one table inherits this one. | |
| relacl | aclitem[] | Access permissions. See the descriptions of GRANT and REVOKE for details. |
pg_database
pg_database stores information about the available databases. The pg_database table is shared between all databases of a cluster.
Table 18-7. pg_database Columns
| Name | Type | References | Description |
|---|---|---|---|
| datname | name | Database name | |
| datdba | int4 | pg_shadow.usesysid | Owner of the database, initially set to whichever user created it. |
| encoding | int4 | Character/multibyte encoding for this database | |
| datistemplate | bool | If true, this database can be used in the "TEMPLATE" clause of CREATE DATABASE to create the new database as a clone of this one. | |
| datallowconn | bool | If false then no one can connect to this database. This is used to protect the template0 database from being altered. | |
| datlastsysoid | oid | Last oid in existence after the database was created; useful particularly to pg_dump | |
| datpath | text | If the database is stored at an alternative location then this records the location. It is either an environment variable name or an absolute path, depending how it was entered. |
pg_description
pg_description stores an optional description or comment for each database object. Descriptions can be manipulated with the COMMENT command. Client applications can view the descriptions by joining with this table. Many built-in system objects have comments associated with them that are shown by psql's \d commands.
pg_group
pg_group defines groups and stores what users belong to what groups. Groups are created with the CREATE GROUP command.
pg_index
pg_index contains part of the information about indexes. Other index information is in pg_class.
Table 18-10. pg_index Columns
| Name | Type | References | Description |
|---|---|---|---|
| indexrelid | oid | pg_class.oid | The oid of the pg_class entry for this index |
| indrelid | oid | pg_class.oid | The oid of the pg_class entry of the table this index is for |
| indproc | oid | pg_proc.oid | The registered procedure, if this is a functional index. |
| indkey | int2vector | pg_attribute.attnum | This is an vector (array) of up to INDEX_MAX_KEYS values that indicate which table columns this index pertains to. For example a value of 1 3 would mean that the first and the third column make up the index key. |
| indclass | oidvector | pg_opclass.oid | For each column in the index key this contains a reference to the "operator class" to use. See pg_opclass for details. |
| indisclustered | bool | unused | |
| indislossy | bool | Reserved. | |
| indhaskeytype | bool | The type of key is different from type of column. (obsolete) | |
| indisunique | bool | If true, this is a unique index. | |
| indisprimary | bool | If true, this index is a unique index that represents the primary key of the table. | |
| indreference | oid | unused | |
| indpred | text | Query plan for partial index predicate (not functional) |
pg_inherits
pg_inherits records information about table inheritance hierarchies.
Table 18-11. pg_inherits Columns
| Name | Type | References | Description |
|---|---|---|---|
| inhrelid | oid | pg_class.oid | This is the reference to the subtable. Therefore the identified table is inherited from another table. |
| inhparent | oid | pg_class.oid | This is the reference to the parent table, from which the table referenced by inhrelid is inherited. |
| inhseqno | int4 | If there is more than one subtable/parent pair (multiple inheritance), this number tells the order in which the inherited columns are to be arranged. The count starts at 1. |
pg_language
pg_language stores call interfaces or languages in which you can write functions or stored procedures. See under CREATE LANGUAGE for more information about language handlers.
Table 18-12. pg_language Columns
| Name | Type | References | Description |
|---|---|---|---|
| lanname | name | Name of the language | |
| lanispl | bool | This is false for internal languages (such as SQL) and true for dynamically loaded language handler modules. It essentially means that, if it is true, the language may be dropped. | |
| lanpltrusted | bool | This is a trusted language. See under CREATE LANGUAGE what this means. If this is an internal language (lanispl is false) then this field is meaningless. | |
| lanplcallfoid | oid | pg_proc.oid | For non-internal languages this references the language handler, which is a special function that is responsible for executing all functions that are written in the particular language. |
| lancompiler | text | not useful |
pg_operator
See CREATE OPERATOR for details on these operator parameters.
Table 18-13. pg_operator Columns
| Name | Type | References | Description |
|---|---|---|---|
| oprname | name | Name of the operator | |
| oprowner | int4 | pg_shadow.usesysid | Owner (creator) of the operator |
| oprprec | int2 | unused | |
| oprkind | char | 'b' = infix ("both"), 'l' = prefix ("left"), 'r' = postfix ("right") | |
| oprisleft | bool | unused | |
| oprcanhash | bool | This operator supports hash joins. | |
| oprleft | oid | pg_type.oid | Type of the left operand |
| oprright | oid | pg_type.oid | Type of the right operand |
| oprresult | oid | pg_type.oid | Type of the result |
| oprcom | oid | pg_operator.oid | Commutator of this operator, if any |
| oprnegate | oid | pg_operator.oid | Negator of this operator, if any. |
| oprlsortop | oid | pg_operator.oid | If this operator supports merge joins, it is set to the operator that sorts the type of the left-hand operand. |
| oprrsortop | oid | pg_operator.oid | If this operator supports merge joins, the operator that sorts the type of the right-hand operand |
| oprcode | regproc | Function that implements this operator | |
| oprrest | regproc | Restriction selectivity estimation function for this operator | |
| oprjoin | regproc | Join selectivity estimation function for this operator |
pg_proc
pg_proc stores information about functions (or procedures). See CREATE FUNCTION for more information about the meaning of some fields.
Table 18-14. pg_proc Columns
| Name | Type | References | Description |
|---|---|---|---|
| proname | name | Name of the function | |
| proowner | int4 | pg_shadow.usesysid | Owner (creator) of the function |
| prolang | oid | pg_language.oid | Implementation language or call interface of this function |
| proisinh | bool | unused | |
| proistrusted | bool | not functional | |
| proiscachable | bool | Function returns same result for same input values | |
| proisstrict | bool | Function is strict. See "Notes on pg_proc columns", which follows this table. | |
| pronargs | int2 | Number of arguments | |
| proretset | bool | Function returns a set (probably not functional) | |
| prorettype | oid | pg_type.oid | Data type of the return value (0 if the function does not return a value) |
| proargtypes | oidvector | pg_type.oid | A vector with the data types of the function arguments |
| probyte_pct | int4 | Reserved; unused. | |
| properbyte_pct_cpu | int4 | Reserved; unused. | |
| propercall_pct_cpu | int4 | Reserved; unused. | |
| prooutin_ratio | int4 | Reserved; unused. | |
| prosrc | text | Source. See "Notes on pg_proc columns", which follows this table. | |
| probin | bytea | Reserved; unused. |
Notes on pg_proc Columns
- proisstrict
If proisstrict is set, the function returns null if any call argument is null. In that case the function will not actually be called at all. Functions that are not "strict" must be prepared to handle null inputs.
- prosrc
prosrc tells the function handler how to invoke the function. It might be the actual source code of the function for interpreted languages, a link symbol, a file name, or just about anything else, depending the implementation language/call convention.
pg_relcheck
pg_relcheck stores CHECK constraints on tables. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) See under CREATE TABLE for more information.
Table 18-15. pg_relcheck Columns
| Name | Type | References | Description |
|---|---|---|---|
| rcrelid | oid | pg_class.oid | The table to which this check constraint applies |
| rcname | name | Constraint name | |
| rcbin | text | An internal representation of the constraint expression | |
| rcsrc | text | A human-readable representation of the constraint expression |
 | pg_class.relchecks needs to match up with the entries in this table. |
pg_shadow
pg_shadow stores information about database users. The name stems from the fact that this table should not be readable by the public since it contains passwords. It is similar to a shadow password file in UNIX. pg_user is a view on pg_shadow that blanks out the password field.
Table 18-16. pg_shadow Columns
| Name | Type | References | Description |
|---|---|---|---|
| usename | name | User name | |
| usesysid | int4 | User id (arbitrary, unique number used to reference this user) | |
| usecreatedb | bool | User may create databases | |
| usetrace | bool | unused | |
| usesuper | bool | User is a superuser | |
| usecatupd | bool | User may update system catalogs. (Even a superuser may not do this unless this attribute is true.) | |
| passwd | text | Password | |
| valuntil | abstime | Account expiry time (only used for password authentication) |
pg_type
Table 18-17. pg_type Columns
| Name | Type | References | Description |
|---|---|---|---|
| typname | name | Data type name | |
| typowner | int4 | pg_shadow.usesysid | Owner (creator) of the type |
| typlen | int2 | Length of the storage representation of the type, -1 if variable length | |
| typprtlen | int2 | unused | |
| typbyval | bool | Pass by value. See "Notes on pg_type columns", which follows this table. | |
| typtype | char | Type of type. See "Notes on pg_type columns", which follows this table. | |
| typisdefined | bool | Reserved; unused. | |
| typdelim | char | Character that separates two values of this type when parsing array input | |
| typrelid | oid | pg_class.oid | Table described by this type. See "Notes on pg_type columns", which follows this table. |
| typelem | oid | pg_type.oid | Type element. See "Notes on pg_type columns", which follows this table. |
| typinput | regproc | Input function | |
| typoutput | regproc | Output function | |
| typreceive | regproc | unused | |
| typsend | regproc | unused | |
| typalign | char | Alignment. See "Notes on pg_type columns", which follows this table. | |
| typstorage | char | Toast storage strategy. See "Notes on pg_type columns", which follows this table. | |
| typdefault | text | Reserved; unused. |
Notes on pg_type Columns
- typbyval
typbyval determines whether internal routines pass a value of this type by value or by reference. Only char, short, and int equivalent items can be passed by value. Variable-length types are always passed by reference. Note that typbyval can be false even if the length would allow pass-by-value; this is currently true for type float4, for example.
- typtype
typtype is b for a basic type and c for a catalog type (that is, a table). If typtype is c, typrelid is the OID of the type's entry in pg_class.
- typrelid
If this is a catalog type (see typtype, above), then the typrelid field points to the pg_class entry that defines the corresponding table. A table could theoretically be used as a composite data type, but this is not fully functional.
- typelem
If typelem is not 0 then it identifies another row in pg_type. The current type can then be subscripted like an array yielding values of type typelem. A non-zero typelem does not guarantee this type to be a "real" array type; some ordinary fixed-length types can also be subscripted (for example, oidvector). Variable-length types can not be turned into pseudo-arrays like that. Hence, the way to determine whether a type is a "true" array type is typelem != 0 and typlen < 0.
- typalign
typalign is the alignment required when storing a value of this type. It applies to storage on disk as well as most representations of the value inside Postgres. When multiple values are stored consecutively, such as in the representation of a complete row on disk, padding is inserted before a datum of this type so that it begins on the specified boundary. The alignment reference is the beginning of the first datum in the sequence.
'c' = CHAR alignment, that is, no alignment needed.
's' = SHORT alignment (2 bytes on most machines).
'i' = INT alignment (4 bytes on most machines).
'd' = DOUBLE alignment (8 bytes on many machines, but by no means all).
- typstorage
typstorage tells for variable-length types (those with typlen = -1) if the type is prepared for toasting and what the default strategy for attributes of this type should be. Possible values are
'p': Value must always be stored plain.
'e': Value can be stored in a "secondary" relation (if the relation has one). Refer to pg_class.reltoastrelid.
'm': Value can be stored compressed inline.
'x': Value can be stored compressed inline or in "secondary".