System Views
En esta página
- System Views#
- Overview#
- pg_available_extensions#
- pg_available_extension_versions#
- pg_backend_memory_contexts#
- pg_config#
- pg_cursors#
- pg_file_settings#
- pg_group#
- pg_hba_file_rules#
- pg_ident_file_mappings#
- pg_indexes#
- pg_locks#
- pg_matviews#
- pg_policies#
- pg_prepared_statements#
- pg_prepared_xacts#
- pg_publication_tables#
- pg_replication_origin_status#
- pg_replication_slots#
- pg_roles#
- pg_rules#
- pg_seclabels#
- pg_sequences#
- pg_settings#
- pg_shadow#
- pg_shmem_allocations#
- pg_stats#
- pg_stats_ext#
- pg_stats_ext_exprs#
- pg_tables#
- pg_timezone_abbrevs#
- pg_timezone_names#
- pg_user#
- pg_user_mappings#
- pg_views#
- pg_wait_events#
System Views#
In addition to the system catalogs, PostgreSQL provides a number of built-in views. Some system views provide convenient access to some commonly used queries on the system catalogs. Other views provide access to internal server state.
The information schema (???) provides an alternative set of views which overlap the functionality of the system views. Since the information schema is SQL-standard whereas the views described here are PostgreSQL-specific, it's usually better to use the information schema if it provides all the information you need.
System Views lists the system views described here. More detailed documentation of each view follows below. There are some additional views that provide access to accumulated statistics; they are described in ???.
Overview#
System Views lists the system views. More detailed documentation of each catalog follows below. Except where noted, all the views described here are read-only.
| View Name | Purpose |
|---|---|
| pg_available_extensions | available extensions |
| pg_available_extension_versions | available versions of extensions |
| pg_backend_memory_contexts | backend memory contexts |
| pg_config | compile-time configuration parameters |
| pg_cursors | open cursors |
| pg_file_settings | summary of configuration file contents |
| pg_group | groups of database users |
| pg_hba_file_rules | summary of client authentication configuration file contents |
| pg_ident_file_mappings | summary of client user name mapping configuration file contents |
| pg_indexes | indexes |
| pg_locks | locks currently held or awaited |
| pg_matviews | materialized views |
| pg_policies | policies |
| pg_prepared_statements | prepared statements |
| pg_prepared_xacts | prepared transactions |
| pg_publication_tables | publications and information of their associated tables |
| pg_replication_origin_status | information about replication origins, including replication progress |
| pg_replication_slots | replication slot information |
| pg_roles | database roles |
| pg_rules | rules |
| pg_seclabels | security labels |
| pg_sequences | sequences |
| pg_settings | parameter settings |
| pg_shadow | database users |
| pg_shmem_allocations | shared memory allocations |
| pg_stats | planner statistics |
| pg_stats_ext | extended planner statistics |
| pg_stats_ext_exprs | extended planner statistics for expressions |
| pg_tables | tables |
| pg_timezone_abbrevs | time zone abbreviations |
| pg_timezone_names | time zone names |
| pg_user | database users |
| pg_user_mappings | user mappings |
| pg_views | views |
| pg_wait_events | wait events |
System Views {#view-table}
pg_available_extensions#
pg_available_extensions
The pg_available_extensions view lists the extensions that are available for installation. See also the pg_extension catalog, which shows the extensions currently installed.
Column Type Description |
|---|
name Extension name |
default_version Name of default version, or |
installed_version Currently installed version of the extension, or |
comment Comment string from the extension's control file |
The pg_available_extensions view is read-only.
pg_available_extension_versions#
pg_available_extension_versions
The pg_available_extension_versions view lists the specific extension versions that are available for installation. See also the pg_extension catalog, which shows the extensions currently installed.
Column Type Description |
|---|
name Extension name |
version Version name |
installed True if this version of this extension is currently installed |
superuser True if only superusers are allowed to install this extension (but see trusted) |
trusted True if the extension can be installed by non-superusers with appropriate privileges |
relocatable True if extension can be relocated to another schema |
schema Name of the schema that the extension must be installed into, or |
requires Names of prerequisite extensions, or |
comment Comment string from the extension's control file |
The pg_available_extension_versions view is read-only.
pg_backend_memory_contexts#
pg_backend_memory_contexts
The view pg_backend_memory_contexts displays all the memory contexts of the server process attached to the current session.
pg_backend_memory_contexts contains one row for each memory context.
Column Type Description |
|---|
name Name of the memory context |
ident Identification information of the memory context. This field is truncated at 1024 bytes |
parent Name of the parent of this memory context |
level Distance from TopMemoryContext in context tree |
total_bytes Total bytes allocated for this memory context |
total_nblocks Total number of blocks allocated for this memory context |
free_bytes Free space in bytes |
free_chunks Total number of free chunks |
used_bytes Used space in bytes |
By default, the pg_backend_memory_contexts view can be read only by superusers or roles with the privileges of the pg_read_all_stats role.
pg_config#
pg_config
The view pg_config describes the compile-time configuration parameters of the currently installed version of PostgreSQL. It is intended, for example, to be used by software packages that want to interface to PostgreSQL to facilitate finding the required header files and libraries. It provides the same basic information as the ??? PostgreSQL client application.
By default, the pg_config view can be read only by superusers.
Column Type Description |
|---|
name The parameter name |
setting The parameter value |
pg_cursors#
pg_cursors
The pg_cursors view lists the cursors that are currently available. Cursors can be defined in several ways:
-
via the
DECLAREstatement in SQL -
via the Bind message in the frontend/backend protocol, as described in ???
-
via the Server Programming Interface (SPI), as described in ???
The pg_cursors view displays cursors created by any of these means. Cursors only exist for the duration of the transaction that defines them, unless they have been declared WITH HOLD. Therefore non-holdable cursors are only present in the view until the end of their creating transaction.
[!NOTE] Cursors are used internally to implement some of the components of PostgreSQL, such as procedural languages. Therefore, the pg_cursors view might include cursors that have not been explicitly created by the user.
Column Type Description |
|---|
name The name of the cursor |
statement The verbatim query string submitted to declare this cursor |
is_holdable
|
is_binary
|
is_scrollable
|
creation_time The time at which the cursor was declared |
The pg_cursors view is read-only.
pg_file_settings#
pg_file_settings
The view pg_file_settings provides a summary of the contents of the server's configuration file(s). A row appears in this view for each “name = value” entry appearing in the files, with annotations indicating whether the value could be applied successfully. Additional row(s) may appear for problems not linked to a “name = value” entry, such as syntax errors in the files.
This view is helpful for checking whether planned changes in the configuration files will work, or for diagnosing a previous failure. Note that this view reports on the current contents of the files, not on what was last applied by the server. (The pg_settings view is usually sufficient to determine that.)
By default, the pg_file_settings view can be read only by superusers.
Column Type Description |
|---|
sourcefile Full path name of the configuration file |
sourceline Line number within the configuration file where the entry appears |
seqno Order in which the entries are processed (1..<n>) |
name Configuration parameter name |
setting Value to be assigned to the parameter |
applied True if the value can be applied successfully |
error If not null, an error message indicating why this entry could not be applied |
If the configuration file contains syntax errors or invalid parameter names, the server will not attempt to apply any settings from it, and therefore all the applied fields will read as false. In such a case there will be one or more rows with non-null error fields indicating the problem(s). Otherwise, individual settings will be applied if possible. If an individual setting cannot be applied (e.g., invalid value, or the setting cannot be changed after server start) it will have an appropriate message in the error field. Another way that an entry might have applied = false is that it is overridden by a later entry for the same parameter name; this case is not considered an error so nothing appears in the error field.
See ??? for more information about the various ways to change run-time parameters.
pg_group#
pg_group
The view pg_group exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows the names and members of all roles that are marked as not rolcanlogin, which is an approximation to the set of roles that are being used as groups.
Column Type Description |
|---|
groname Name of the group |
grosysid ID of this group |
grolist An array containing the IDs of the roles in this group |
pg_hba_file_rules#
pg_hba_file_rules
The view pg_hba_file_rules provides a summary of the contents of the client authentication configuration file, pg_hba.conf. A row appears in this view for each non-empty, non-comment line in the file, with annotations indicating whether the rule could be applied successfully.
This view can be helpful for checking whether planned changes in the authentication configuration file will work, or for diagnosing a previous failure. Note that this view reports on the current contents of the file, not on what was last loaded by the server.
By default, the pg_hba_file_rules view can be read only by superusers.
Column Type Description |
|---|
rule_number Number of this rule, if valid, otherwise |
file_name Name of the file containing this rule |
line_number Line number of this rule in |
type Type of connection |
database List of database name(s) to which this rule applies |
user_name List of user and group name(s) to which this rule applies |
address Host name or IP address, or one of |
netmask IP address mask, or null if not applicable |
auth_method Authentication method |
options Options specified for authentication method, if any |
error If not null, an error message indicating why this line could not be processed |
Usually, a row reflecting an incorrect entry will have values for only the line_number and error fields.
See ??? for more information about client authentication configuration.
pg_ident_file_mappings#
pg_ident_file_mappings
The view pg_ident_file_mappings provides a summary of the contents of the client user name mapping configuration file, pg_ident.conf. A row appears in this view for each non-empty, non-comment line in the file, with annotations indicating whether the map could be applied successfully.
This view can be helpful for checking whether planned changes in the authentication configuration file will work, or for diagnosing a previous failure. Note that this view reports on the current contents of the file, not on what was last loaded by the server.
By default, the pg_ident_file_mappings view can be read only by superusers.
Column Type Description |
|---|
map_number Number of this map, in priority order, if valid, otherwise |
file_name Name of the file containing this map |
line_number Line number of this map in |
map_name Name of the map |
sys_name Detected user name of the client |
pg_username Requested PostgreSQL user name |
error If not |
Usually, a row reflecting an incorrect entry will have values for only the line_number and error fields.
See ??? for more information about client authentication configuration.
pg_indexes#
pg_indexes
The view pg_indexes provides access to useful information about each index in the database.
Column Type Description |
|---|
schemaname Name of schema containing table and index |
tablename Name of table the index is for |
indexname Name of index |
tablespace Name of tablespace containing index (null if default for database) |
indexdef Index definition (a reconstructed ??? command) |
pg_locks#
pg_locks
The view pg_locks provides access to information about the locks held by active processes within the database server. See ??? for more discussion of locking.
pg_locks contains one row per active lockable object, requested lock mode, and relevant process. Thus, the same lockable object might appear many times, if multiple processes are holding or waiting for locks on it. However, an object that currently has no locks on it will not appear at all.
There are several distinct types of lockable objects: whole relations (e.g., tables), individual pages of relations, individual tuples of relations, transaction IDs (both virtual and permanent IDs), and general database objects (identified by class OID and object OID, in the same way as in pg_description or pg_depend). Also, the right to extend a relation is represented as a separate lockable object, as is the right to update pg_database.datfrozenxid. Also, “advisory” locks can be taken on numbers that have user-defined meanings.
Column Type Description |
|---|
locktype Type of the lockable object: |
database OID of the database in which the lock target exists, or zero if the target is a shared object, or null if the target is a transaction ID |
relation OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation |
page Page number targeted by the lock within the relation, or null if the target is not a relation page or tuple |
tuple Tuple number targeted by the lock within the page, or null if the target is not a tuple |
virtualxid Virtual ID of the transaction targeted by the lock, or null if the target is not a virtual transaction ID; see ??? |
transactionid ID of the transaction targeted by the lock, or null if the target is not a transaction ID; ??? |
classid OID of the system catalog containing the lock target, or null if the target is not a general database object |
objid OID of the lock target within its system catalog, or null if the target is not a general database object |
objsubid Column number targeted by the lock (the classid and objid refer to the table itself), or zero if the target is some other general database object, or null if the target is not a general database object |
virtualtransaction Virtual ID of the transaction that is holding or awaiting this lock |
pid Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction |
mode Name of the lock mode held or desired by this process (see ??? and ???) |
granted True if lock is held, false if lock is awaited |
fastpath True if lock was taken via fast path, false if taken via main lock table |
waitstart Time when the server process started waiting for this lock, or null if the lock is held. Note that this can be null for a very short period of time after the wait started even though granted is |
granted is true in a row representing a lock held by the indicated process. False indicates that this process is currently waiting to acquire this lock, which implies that at least one other process is holding or waiting for a conflicting lock mode on the same lockable object. The waiting process will sleep until the other lock is released (or a deadlock situation is detected). A single process can be waiting to acquire at most one lock at a time.
Throughout running a transaction, a server process holds an exclusive lock on the transaction's virtual transaction ID. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on the transaction's permanent transaction ID until it ends. When a process finds it necessary to wait specifically for another transaction to end, it does so by attempting to acquire share lock on the other transaction's ID (either virtual or permanent ID depending on the situation). That will succeed only when the other transaction terminates and releases its locks.
Although tuples are a lockable type of object, information about row-level locks is stored on disk, not in memory, and therefore row-level locks normally do not appear in this view. If a process is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock.
A speculative insertion lock consists of a transaction ID and a speculative insertion token. The speculative insertion token is displayed in the objid column.
Advisory locks can be acquired on keys consisting of either a single bigint value or two integer values. A bigint key is displayed with its high-order half in the classid column, its low-order half in the objid column, and objsubid equal to 1. The original bigint value can be reassembled with the expression (classid::bigint << 32) | objid::bigint. Integer keys are displayed with the first key in the classid column, the second key in the objid column, and objsubid equal to 2. The actual meaning of the keys is up to the user. Advisory locks are local to each database, so the database column is meaningful for an advisory lock.
Apply transaction locks are used in parallel mode to apply the transaction in logical replication. The remote transaction ID is displayed in the transactionid column. The objsubid displays the lock subtype which is 0 for the lock used to synchronize the set of changes, and 1 for the lock used to wait for the transaction to finish to ensure commit order.
pg_locks provides a global view of all locks in the database cluster, not only those relevant to the current database. Although its relation column can be joined against pg_class.oid to identify locked relations, this will only work correctly for relations in the current database (those for which the database column is either the current database's OID or zero).
The pid column can be joined to the pid column of the pg_stat_activity view to get more information on the session holding or awaiting each lock, for example
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
ON pl.pid = psa.pid;
Also, if you are using prepared transactions, the virtualtransaction column can be joined to the transaction column of the pg_prepared_xacts view to get more information on prepared transactions that hold locks. (A prepared transaction can never be waiting for a lock, but it continues to hold the locks it acquired while running.) For example:
SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
ON pl.virtualtransaction = '-1/' || ppx.transaction;
While it is possible to obtain information about which processes block which other processes by joining pg_locks against itself, this is very difficult to get right in detail. Such a query would have to encode knowledge about which lock modes conflict with which others. Worse, the pg_locks view does not expose information about which processes are ahead of which others in lock wait queues, nor information about which processes are parallel workers running on behalf of which other client sessions. It is better to use the pg_blocking_pids() function (see ???) to identify which process(es) a waiting process is blocked behind.
The pg_locks view displays data from both the regular lock manager and the predicate lock manager, which are separate systems; in addition, the regular lock manager subdivides its locks into regular and fast-path locks. This data is not guaranteed to be entirely consistent. When the view is queried, data on fast-path locks (with fastpath = true) is gathered from each backend one at a time, without freezing the state of the entire lock manager, so it is possible for locks to be taken or released while information is gathered. Note, however, that these locks are known not to conflict with any other lock currently in place. After all backends have been queried for fast-path locks, the remainder of the regular lock manager is locked as a unit, and a consistent snapshot of all remaining locks is collected as an atomic action. After unlocking the regular lock manager, the predicate lock manager is similarly locked and all predicate locks are collected as an atomic action. Thus, with the exception of fast-path locks, each lock manager will deliver a consistent set of results, but as we do not lock both lock managers simultaneously, it is possible for locks to be taken or released after we interrogate the regular lock manager and before we interrogate the predicate lock manager.
Locking the regular and/or predicate lock manager could have some impact on database performance if this view is very frequently accessed. The locks are held only for the minimum amount of time necessary to obtain data from the lock managers, but this does not completely eliminate the possibility of a performance impact.
pg_matviews#
pg_matviews
materialized views
The view pg_matviews provides access to useful information about each materialized view in the database.
Column Type Description |
|---|
schemaname Name of schema containing materialized view |
matviewname Name of materialized view |
matviewowner Name of materialized view's owner |
tablespace Name of tablespace containing materialized view (null if default for database) |
hasindexes True if materialized view has (or recently had) any indexes |
ispopulated True if materialized view is currently populated |
definition Materialized view definition (a reconstructed ??? query) |
pg_policies#
pg_policies
The view pg_policies provides access to useful information about each row-level security policy in the database.
Column Type Description |
|---|
schemaname Name of schema containing table policy is on |
tablename Name of table policy is on |
policyname Name of policy |
permissive Is the policy permissive or restrictive? |
roles The roles to which this policy applies |
cmd The command type to which the policy is applied |
qual The expression added to the security barrier qualifications for queries that this policy applies to |
with_check The expression added to the WITH CHECK qualifications for queries that attempt to add rows to this table |
pg_prepared_statements#
pg_prepared_statements
The pg_prepared_statements view displays all the prepared statements that are available in the current session. See ??? for more information about prepared statements.
pg_prepared_statements contains one row for each prepared statement. Rows are added to the view when a new prepared statement is created and removed when a prepared statement is released (for example, via the DEALLOCATE command).
Column Type Description |
|---|
name The identifier of the prepared statement |
statement The query string submitted by the client to create this prepared statement. For prepared statements created via SQL, this is the |
prepare_time The time at which the prepared statement was created |
parameter_types The expected parameter types for the prepared statement in the form of an array of |
result_types The types of the columns returned by the prepared statement in the form of an array of |
from_sql
|
generic_plans Number of times generic plan was chosen |
custom_plans Number of times custom plan was chosen |
The pg_prepared_statements view is read-only.
pg_prepared_xacts#
pg_prepared_xacts
The view pg_prepared_xacts displays information about transactions that are currently prepared for two-phase commit (see ??? for details).
pg_prepared_xacts contains one row per prepared transaction. An entry is removed when the transaction is committed or rolled back.
Column Type Description |
|---|
transaction Numeric transaction identifier of the prepared transaction |
gid Global transaction identifier that was assigned to the transaction |
prepared Time at which the transaction was prepared for commit |
owner Name of the user that executed the transaction |
database Name of the database in which the transaction was executed |
When the pg_prepared_xacts view is accessed, the internal transaction manager data structures are momentarily locked, and a copy is made for the view to display. This ensures that the view produces a consistent set of results, while not blocking normal operations longer than necessary. Nonetheless there could be some impact on database performance if this view is frequently accessed.
pg_publication_tables#
pg_publication_tables
The view pg_publication_tables provides information about the mapping between publications and information of tables they contain. Unlike the underlying catalog pg_publication_rel, this view expands publications defined as FOR ALL TABLES and FOR TABLES IN SCHEMA, so for such publications there will be a row for each eligible table.
Column Type Description |
|---|
pubname Name of publication |
schemaname Name of schema containing table |
tablename Name of table |
attnames Names of table columns included in the publication. This contains all the columns of the table when the user didn't specify the column list for the table. |
rowfilter Expression for the table's publication qualifying condition |
pg_replication_origin_status#
pg_replication_origin_status
The pg_replication_origin_status view contains information about how far replay for a certain origin has progressed. For more on replication origins see ???.
Column Type Description |
|---|
local_id internal node identifier |
external_id external node identifier |
remote_lsn The origin node's LSN up to which data has been replicated. |
local_lsn This node's LSN at which |
pg_replication_slots#
pg_replication_slots
The pg_replication_slots view provides a listing of all replication slots that currently exist on the database cluster, along with their current state.
For more on replication slots, see ??? and ???.
Column Type Description |
|---|
slot_name A unique, cluster-wide identifier for the replication slot |
plugin The base name of the shared object containing the output plugin this logical slot is using, or null for physical slots. |
slot_type The slot type: |
datoid The OID of the database this slot is associated with, or null. Only logical slots have an associated database. |
database The name of the database this slot is associated with, or null. Only logical slots have an associated database. |
temporary True if this is a temporary replication slot. Temporary slots are not saved to disk and are automatically dropped on error or when the session has finished. |
active True if this slot is currently being streamed |
active_pid The process ID of the session streaming data for this slot. |
xmin The oldest transaction that this slot needs the database to retain. |
catalog_xmin The oldest transaction affecting the system catalogs that this slot needs the database to retain. |
restart_lsn The address ( |
confirmed_flush_lsn The address ( |
wal_status Availability of WAL files claimed by this slot. Possible values are:
|
safe_wal_size The number of bytes that can be written to WAL such that this slot is not in danger of getting in state "lost". It is NULL for lost slots, as well as if |
two_phase True if the slot is enabled for decoding prepared transactions. Always false for physical slots. |
inactive_since The time when the slot became inactive. |
conflicting True if this logical slot conflicted with recovery (and so is now invalidated). When this column is true, check invalidation_reason column for the conflict reason. Always NULL for physical slots. |
invalidation_reason The reason for the slot's invalidation. It is set for both logical and physical slots.
|
failover True if this is a logical slot enabled to be synced to the standbys so that logical replication can be resumed from the new primary after failover. Always false for physical slots. |
synced True if this is a logical slot that was synced from a primary server. On a hot standby, the slots with the synced column marked as true can neither be used for logical decoding nor dropped manually. The value of this column has no meaning on the primary server; the column value on the primary is default false for all slots but may (if leftover from a promoted standby) also be true. |
pg_roles#
pg_roles
The view pg_roles provides access to information about database roles. This is simply a publicly readable view of pg_authid that blanks out the password field.
Column Type Description |
|---|
rolname Role name |
rolsuper Role has superuser privileges |
rolinherit Role automatically inherits privileges of roles it is a member of |
rolcreaterole Role can create more roles |
rolcreatedb Role can create databases |
rolcanlogin Role can log in. That is, this role can be given as the initial session authorization identifier |
rolreplication Role is a replication role. A replication role can initiate replication connections and create and drop replication slots. |
rolconnlimit For roles that can log in, this sets maximum number of concurrent connections this role can make. -1 means no limit. |
rolpassword Not the password (always reads as |
rolvaliduntil Password expiry time (only used for password authentication); null if no expiration |
rolbypassrls Role bypasses every row-level security policy, see ??? for more information. |
rolconfig Role-specific defaults for run-time configuration variables |
oid ID of role |
pg_rules#
pg_rules
The view pg_rules provides access to useful information about query rewrite rules.
Column Type Description |
|---|
schemaname Name of schema containing table |
tablename Name of table the rule is for |
rulename Name of rule |
definition Rule definition (a reconstructed creation command) |
The pg_rules view excludes the ON SELECT rules of views and materialized views; those can be seen in pg_views and pg_matviews.
pg_seclabels#
pg_seclabels
The view pg_seclabels provides information about security labels. It as an easier-to-query version of the pg_seclabel catalog.
Column Type Description |
|---|
objoid The OID of the object this security label pertains to |
classoid The OID of the system catalog this object appears in |
objsubid For a security label on a table column, this is the column number (the objoid and classoid refer to the table itself). For all other object types, this column is zero. |
objtype The type of object to which this label applies, as text. |
objnamespace The OID of the namespace for this object, if applicable; otherwise NULL. |
objname The name of the object to which this label applies, as text. |
provider The label provider associated with this label. |
label The security label applied to this object. |
pg_sequences#
pg_sequences
The view pg_sequences provides access to useful information about each sequence in the database.
Column Type Description |
|---|
schemaname Name of schema containing sequence |
sequencename Name of sequence |
sequenceowner Name of sequence's owner |
data_type Data type of the sequence |
start_value Start value of the sequence |
min_value Minimum value of the sequence |
max_value Maximum value of the sequence |
increment_by Increment value of the sequence |
cycle Whether the sequence cycles |
cache_size Cache size of the sequence |
last_value The last sequence value written to disk. If caching is used, this value can be greater than the last value handed out from the sequence. |
The last_value column will read as null if any of the following are true:
-
The sequence has not been read from yet.
-
The current user does not have
USAGEorSELECTprivilege on the sequence. -
The sequence is unlogged and the server is a standby.
pg_settings#
pg_settings
The view pg_settings provides access to run-time parameters of the server. It is essentially an alternative interface to the SHOW and SET commands. It also provides access to some facts about each parameter that are not directly available from SHOW, such as minimum and maximum values.
Column Type Description |
|---|
name Run-time configuration parameter name |
setting Current value of the parameter |
unit Implicit unit of the parameter |
category Logical group of the parameter |
short_desc A brief description of the parameter |
extra_desc Additional, more detailed, description of the parameter |
context Context required to set the parameter's value (see below) |
vartype Parameter type ( |
source Source of the current parameter value |
min_val Minimum allowed value of the parameter (null for non-numeric values) |
max_val Maximum allowed value of the parameter (null for non-numeric values) |
enumvals Allowed values of an enum parameter (null for non-enum values) |
boot_val Parameter value assumed at server startup if the parameter is not otherwise set |
reset_val Value that |
sourcefile Configuration file the current value was set in (null for values set from sources other than configuration files, or when examined by a user who neither is a superuser nor has privileges of |
sourceline Line number within the configuration file the current value was set at (null for values set from sources other than configuration files, or when examined by a user who neither is a superuser nor has privileges of |
pending_restart
|
There are several possible values of context. In order of decreasing difficulty of changing the setting, they are:
internal
These settings cannot be changed directly; they reflect internally determined values. Some of them may be adjustable by rebuilding the server with different configuration options, or by changing options supplied to initdb.
postmaster
These settings can only be applied when the server starts, so any change requires restarting the server. Values for these settings are typically stored in the postgresql.conf file, or passed on the command line when starting the server. Of course, settings with any of the lower context types can also be set at server start time.
sighup
Changes to these settings can be made in postgresql.conf without restarting the server. Send a SIGHUP signal to the postmaster to cause it to re-read postgresql.conf and apply the changes. The postmaster will also forward the SIGHUP signal to its child processes so that they all pick up the new value.
superuser-backend
Changes to these settings can be made in postgresql.conf without restarting the server. They can also be set for a particular session in the connection request packet (for example, via libpq's PGOPTIONS environment variable), but only if the connecting user is a superuser or has been granted the appropriate SET privilege. However, these settings never change in a session after it is started. If you change them in postgresql.conf, send a SIGHUP signal to the postmaster to cause it to re-read postgresql.conf. The new values will only affect subsequently-launched sessions.
backend
Changes to these settings can be made in postgresql.conf without restarting the server. They can also be set for a particular session in the connection request packet (for example, via libpq's PGOPTIONS environment variable); any user can make such a change for their session. However, these settings never change in a session after it is started. If you change them in postgresql.conf, send a SIGHUP signal to the postmaster to cause it to re-read postgresql.conf. The new values will only affect subsequently-launched sessions.
superuser
These settings can be set from postgresql.conf, or within a session via the SET command; but only superusers and users with the appropriate SET privilege can change them via SET. Changes in postgresql.conf will affect existing sessions only if no session-local value has been established with SET.
user
These settings can be set from postgresql.conf, or within a session via the SET command. Any user is allowed to change their session-local value. Changes in postgresql.conf will affect existing sessions only if no session-local value has been established with SET.
See ??? for more information about the various ways to change these parameters.
This view cannot be inserted into or deleted from, but it can be updated. An UPDATE applied to a row of pg_settings is equivalent to executing the SET command on that named parameter. The change only affects the value used by the current session. If an UPDATE is issued within a transaction that is later aborted, the effects of the UPDATE command disappear when the transaction is rolled back. Once the surrounding transaction is committed, the effects will persist until the end of the session, unless overridden by another UPDATE or SET.
This view does not display customized options unless the extension module that defines them has been loaded by the backend process executing the query (e.g., via a mention in ???, a call to a C function in the extension, or the LOAD command). For example, since archive modules are normally loaded only by the archiver process not regular sessions, this view will not display any customized options defined by such modules unless special action is taken to load them into the backend process executing the query.
pg_shadow#
pg_shadow
The view pg_shadow exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows properties of all roles that are marked as rolcanlogin in pg_authid.
The name stems from the fact that this table should not be readable by the public since it contains passwords. pg_user is a publicly readable view on pg_shadow that blanks out the password field.
Column Type Description |
|---|
usename User name |
usesysid ID of this user |
usecreatedb User can create databases |
usesuper User is a superuser |
userepl User can initiate streaming replication and put the system in and out of backup mode. |
usebypassrls User bypasses every row-level security policy, see ??? for more information. |
passwd Encrypted password; null if none. See pg_authid for details of how encrypted passwords are stored. |
valuntil Password expiry time (only used for password authentication) |
useconfig Session defaults for run-time configuration variables |
pg_shmem_allocations#
pg_shmem_allocations
The pg_shmem_allocations view shows allocations made from the server's main shared memory segment. This includes both memory allocated by PostgreSQL itself and memory allocated by extensions using the mechanisms detailed in ???.
Note that this view does not include memory allocated using the dynamic shared memory infrastructure.
Column Type Description |
|---|
name The name of the shared memory allocation. NULL for unused memory and |
off The offset at which the allocation starts. NULL for anonymous allocations, since details related to them are not known. |
size Size of the allocation in bytes |
allocated_size Size of the allocation in bytes including padding. For anonymous allocations, no information about padding is available, so the |
Anonymous allocations are allocations that have been made with ShmemAlloc() directly, rather than via ShmemInitStruct() or ShmemInitHash().
By default, the pg_shmem_allocations view can be read only by superusers or roles with privileges of the pg_read_all_stats role.
pg_stats#
pg_stats
The view pg_stats provides access to the information stored in the pg_statistic catalog. This view allows access only to rows of pg_statistic that correspond to tables the user has permission to read, and therefore it is safe to allow public read access to this view.
pg_stats is also designed to present the information in a more readable format than the underlying catalog at the cost that its schema must be extended whenever new slot types are defined for pg_statistic.
Column Type Description |
|---|
schemaname Name of schema containing table |
tablename Name of table |
attname Name of column described by this row |
inherited If true, this row includes values from child tables, not just the values in the specified table |
null_frac Fraction of column entries that are null |
avg_width Average width in bytes of column's entries |
n_distinct If greater than zero, the estimated number of distinct values in the column. If less than zero, the negative of the number of distinct values divided by the number of rows. (The negated form is used when |
most_common_vals A list of the most common values in the column. (Null if no values seem to be more common than any others.) |
most_common_freqs A list of the frequencies of the most common values, i.e., number of occurrences of each divided by total number of rows. (Null when most_common_vals is.) |
histogram_bounds A list of values that divide the column's values into groups of approximately equal population. The values in most_common_vals, if present, are omitted from this histogram calculation. (This column is null if the column data type does not have a |
correlation Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper than when it is near zero, due to reduction of random access to the disk. (This column is null if the column data type does not have a |
most_common_elems A list of non-null element values most often appearing within values of the column. (Null for scalar types.) |
most_common_elem_freqs A list of the frequencies of the most common element values, i.e., the fraction of rows containing at least one instance of the given value. Two or three additional values follow the per-element frequencies; these are the minimum and maximum of the preceding per-element frequencies, and optionally the frequency of null elements. (Null when most_common_elems is.) |
elem_count_histogram A histogram of the counts of distinct non-null element values within the values of the column, followed by the average number of distinct non-null elements. (Null for scalar types.) |
range_length_histogram A histogram of the lengths of non-empty and non-null range values of a range type column. (Null for non-range types.) This histogram is calculated using the |
range_empty_frac Fraction of column entries whose values are empty ranges. (Null for non-range types.) |
range_bounds_histogram A histogram of lower and upper bounds of non-empty and non-null range values. (Null for non-range types.) These two histograms are represented as a single array of ranges, whose lower bounds represent the histogram of lower bounds, and upper bounds represent the histogram of upper bounds. |
The maximum number of entries in the array fields can be controlled on a column-by-column basis using the ALTER TABLE SET STATISTICS command, or globally by setting the ??? run-time parameter.
pg_stats_ext#
pg_stats_ext
The view pg_stats_ext provides access to information about each extended statistics object in the database, combining information stored in the pg_statistic_ext and pg_statistic_ext_data catalogs. This view allows access only to rows of pg_statistic_ext and pg_statistic_ext_data that correspond to tables the user owns, and therefore it is safe to allow public read access to this view.
pg_stats_ext is also designed to present the information in a more readable format than the underlying catalogs at the cost that its schema must be extended whenever new types of extended statistics are added to pg_statistic_ext.
Column Type Description |
|---|
schemaname Name of schema containing table |
tablename Name of table |
statistics_schemaname Name of schema containing extended statistics object |
statistics_name Name of extended statistics object |
statistics_owner Owner of the extended statistics object |
attnames Names of the columns included in the extended statistics object |
exprs Expressions included in the extended statistics object |
kinds Types of extended statistics object enabled for this record |
inherited If true, the stats include values from child tables, not just the values in the specified relation |
n_distinct N-distinct counts for combinations of column values. If greater than zero, the estimated number of distinct values in the combination. If less than zero, the negative of the number of distinct values divided by the number of rows. (The negated form is used when |
dependencies Functional dependency statistics |
most_common_vals A list of the most common combinations of values in the columns. (Null if no combinations seem to be more common than any others.) |
most_common_val_nulls A list of NULL flags for the most common combinations of values. (Null when most_common_vals is.) |
most_common_freqs A list of the frequencies of the most common combinations, i.e., number of occurrences of each divided by total number of rows. (Null when most_common_vals is.) |
most_common_base_freqs A list of the base frequencies of the most common combinations, i.e., product of per-value frequencies. (Null when most_common_vals is.) |
The maximum number of entries in the array fields can be controlled on a column-by-column basis using the ALTER TABLE SET STATISTICS command, or globally by setting the ??? run-time parameter.
pg_stats_ext_exprs#
pg_stats_ext_exprs
The view pg_stats_ext_exprs provides access to information about all expressions included in extended statistics objects, combining information stored in the pg_statistic_ext and pg_statistic_ext_data catalogs. This view allows access only to rows of pg_statistic_ext and pg_statistic_ext_data that correspond to tables the user owns, and therefore it is safe to allow public read access to this view.
pg_stats_ext_exprs is also designed to present the information in a more readable format than the underlying catalogs at the cost that its schema must be extended whenever the structure of statistics in pg_statistic_ext changes.
Column Type Description |
|---|
schemaname Name of schema containing table |
tablename Name of table the statistics object is defined on |
statistics_schemaname Name of schema containing extended statistics object |
statistics_name Name of extended statistics object |
statistics_owner Owner of the extended statistics object |
expr Expression included in the extended statistics object |
inherited If true, the stats include values from child tables, not just the values in the specified relation |
null_frac Fraction of expression entries that are null |
avg_width Average width in bytes of expression's entries |
n_distinct If greater than zero, the estimated number of distinct values in the expression. If less than zero, the negative of the number of distinct values divided by the number of rows. (The negated form is used when |
most_common_vals A list of the most common values in the expression. (Null if no values seem to be more common than any others.) |
most_common_freqs A list of the frequencies of the most common values, i.e., number of occurrences of each divided by total number of rows. (Null when most_common_vals is.) |
histogram_bounds A list of values that divide the expression's values into groups of approximately equal population. The values in most_common_vals, if present, are omitted from this histogram calculation. (This expression is null if the expression data type does not have a |
correlation Statistical correlation between physical row ordering and logical ordering of the expression values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the expression will be estimated to be cheaper than when it is near zero, due to reduction of random access to the disk. (This expression is null if the expression's data type does not have a |
most_common_elems A list of non-null element values most often appearing within values of the expression. (Null for scalar types.) |
most_common_elem_freqs A list of the frequencies of the most common element values, i.e., the fraction of rows containing at least one instance of the given value. Two or three additional values follow the per-element frequencies; these are the minimum and maximum of the preceding per-element frequencies, and optionally the frequency of null elements. (Null when most_common_elems is.) |
elem_count_histogram A histogram of the counts of distinct non-null element values within the values of the expression, followed by the average number of distinct non-null elements. (Null for scalar types.) |
The maximum number of entries in the array fields can be controlled on a column-by-column basis using the ALTER TABLE SET STATISTICS command, or globally by setting the ??? run-time parameter.
pg_tables#
pg_tables
The view pg_tables provides access to useful information about each table in the database.
Column Type Description |
|---|
schemaname Name of schema containing table |
tablename Name of table |
tableowner Name of table's owner |
tablespace Name of tablespace containing table (null if default for database) |
hasindexes True if table has (or recently had) any indexes |
hasrules True if table has (or once had) rules |
hastriggers True if table has (or once had) triggers |
rowsecurity True if row security is enabled on the table |
pg_timezone_abbrevs#
pg_timezone_abbrevs
The view pg_timezone_abbrevs provides a list of time zone abbreviations that are currently recognized by the datetime input routines. The contents of this view change when the ??? run-time parameter is modified.
Column Type Description |
|---|
abbrev Time zone abbreviation |
utc_offset Offset from UTC (positive means east of Greenwich) |
is_dst True if this is a daylight-savings abbreviation |
While most timezone abbreviations represent fixed offsets from UTC, there are some that have historically varied in value (see ??? for more information). In such cases this view presents their current meaning.
pg_timezone_names#
pg_timezone_names
The view pg_timezone_names provides a list of time zone names that are recognized by SET TIMEZONE, along with their associated abbreviations, UTC offsets, and daylight-savings status. (Technically, PostgreSQL does not use UTC because leap seconds are not handled.) Unlike the abbreviations shown in pg_timezone_abbrevs, many of these names imply a set of daylight-savings transition date rules. Therefore, the associated information changes across local DST boundaries. The displayed information is computed based on the current value of CURRENT_TIMESTAMP.
Column Type Description |
|---|
name Time zone name |
abbrev Time zone abbreviation |
utc_offset Offset from UTC (positive means east of Greenwich) |
is_dst True if currently observing daylight savings |
pg_user#
pg_user
The view pg_user provides access to information about database users. This is simply a publicly readable view of pg_shadow that blanks out the password field.
Column Type Description |
|---|
usename User name |
usesysid ID of this user |
usecreatedb User can create databases |
usesuper User is a superuser |
userepl User can initiate streaming replication and put the system in and out of backup mode. |
usebypassrls User bypasses every row-level security policy, see ??? for more information. |
passwd Not the password (always reads as |
valuntil Password expiry time (only used for password authentication) |
useconfig Session defaults for run-time configuration variables |
pg_user_mappings#
pg_user_mappings
The view pg_user_mappings provides access to information about user mappings. This is essentially a publicly readable view of pg_user_mapping that leaves out the options field if the user has no rights to use it.
Column Type Description |
|---|
umid OID of the user mapping |
srvid The OID of the foreign server that contains this mapping |
srvname Name of the foreign server |
umuser OID of the local role being mapped, or zero if the user mapping is public |
usename Name of the local user to be mapped |
umoptions User mapping specific options, as “keyword=value” strings |
To protect password information stored as a user mapping option, the umoptions column will read as null unless one of the following applies:
-
current user is the user being mapped, and owns the server or holds
USAGEprivilege on it -
current user is the server owner and mapping is for
PUBLIC -
current user is a superuser
pg_views#
pg_views
The view pg_views provides access to useful information about each view in the database.
Column Type Description |
|---|
schemaname Name of schema containing view |
viewname Name of view |
viewowner Name of view's owner |
definition View definition (a reconstructed ??? query) |
pg_wait_events#
pg_wait_events
The view pg_wait_events provides description about the wait events.
Column Type Description |
|---|
type Wait event type |
name Wait event name |
description Wait event description |