Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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 (Chapter 37) 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.
Table 54.1 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 Table 28.2.
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 Section 27.2.6 and Chapter 49.
pg_replication_slots Columnspg_roles 這個 view 提供對資料庫角色的資訊。 這只是 pg_authid 的一個公共可讀的 view，它將密碼部份予以屏蔽。
此 view 列出底層的 OID 欄位，因此可能需要執行與其他目錄的交叉查詢。
Table 54.20. pg_roles 欄位
| Name | Type | References | Description |
|---|---|---|---|
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 timezone_abbreviations run-time parameter is modified.
pg_timezone_abbrevs ColumnsWhile most timezone abbreviations represent fixed offsets from UTC, there are some that have historically varied in value (see Section B.4 for more information). In such cases this view presents their current meaning.
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 Section 38.10.10.
Note that this view does not include memory allocated using the dynamic shared memory infrastructure.
pg_shmem_allocations ColumnsAnonymous 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.
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.
pg_shadow ColumnsTable 54.1 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 |
|---|---|
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.
pg_user ColumnsThe 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.
pg_stats ColumnsThe 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 default_statistics_target run-time parameter.
檢視表 pg_settings 提供對伺服器的執行時參數的存取。它本質上是 SHOW 和 SET 指令的替代介面。它也提供 SHOW 無法直接獲得的一些資訊存取，例如最小值和最大值。
pg_settings Columns設定內容有幾種可能的值，是為了降低變更組態的複雜度，它們是：
internal
這些設定無法直接更改；它們反映了內部所決定的值，其中一些可以透過使用不同的組態選項重建伺服器，或透過更改提供給 initdb 的選項來調整。
postmaster
這些設定只能在伺服器啟動時套用，因此任何變更都需要重新啟動伺服器。這些設定的值通常儲存在 postgresql.conf 檔案中，或在啟動伺服器時在命令列中給予。當然，也可以在伺服器啟動時設定任何層級較低的設定。
sighup
可以在 postgresql.conf 中對這些設定進行變更，而毌須重新啟動伺服器。只要向 postmaster 發送一個 SIGHUP 信號，使其重新讀取 postgresql.conf 並套用變更。postmaster 還會將 SIGHUP 信號轉發給其子程序，以便它們都獲取新值。
superuser-backend
可以在 postgresql.conf 中對這些設定進行變更，且毌須重新啟動伺服器。它們也可以在連線要求的封包中設定為特別連線（例如，透過 libpq 的 PGOPTIONS 環境變數），但前提是連線使用者是超級使用者。但是，這些設定在啟動後的連線中永遠不會變更。如果你在 postgresql.conf 中更改它們，請向 postmaster 發送一個 SIGHUP 信號，使其重新讀取 postgresql.conf。新值只會影響隨後啟動的連線。
backend
可以在 postgresql.conf 中對這些設定進行變更，而毌須重新啟動伺服器。它們也可以在連線請求封包中設定為特別連線（例如，透過 libpq 的 PGOPTIONS 環境變數）；任何使用者都可以為他們的連線進行這樣的變更。但是，這些設定在啟動後的連線中永遠無法變更。如果你在 postgresql.conf 中更改它們，請向 postmaster 發送一個 SIGHUP 信號，使其重新讀取 postgresql.conf。新值只會影響隨後啟動的連線。
superuser
這些設定可以從 postgresql.conf 設定，也可以透過 SET 指令在連線中設定；但只有超級使用者可以透過 SET 來更改。僅當沒有使用 SET 建立連線專用的值時，postgresql.conf 中的變更才會影響現有連線。
user
這些設定可以從 postgresql.conf 設定，也可以透過 SET 指令在連線中設定。允許任何使用者變更其連線中所使用的值。僅當未使用 SET 未建立連線專用值時，postgresql.conf 中的變更才會影響現有連線。
有關變更這些參數的各種方法和更多資訊，請參閱第 20.1 節。
pg_settings 檢視表無法INSERT 或 DELETE，但可以 UPDATE。套用於一行 pg_settings 的 UPDATE 相當於對該參數執行 SET 指令。此變更僅影響目前連線所使用的值。如果在稍後中止的交易事務中發出 UPDATE，則在回溯事務時 UPDATE 指令的效果會消失。一旦提交了相關的事務，則效果將持續到連線結束，除非被另一個 UPDATE 或 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 shared_preload_libraries, 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.
Column Type
Description
slot_name name
A unique, cluster-wide identifier for the replication slot
plugin name
The base name of the shared object containing the output plugin this logical slot is using, or null for physical slots.
slot_type text
The slot type: physical or logical
datoid oid (references pg_database.oid)
The OID of the database this slot is associated with, or null. Only logical slots have an associated database.
database name (references pg_database.datname)
The name of the database this slot is associated with, or null. Only logical slots have an associated database.
temporary bool
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 bool
True if this slot is currently actively being used
active_pid int4
The process ID of the session using this slot if the slot is currently actively being used. NULL if inactive.
xmin xid
The oldest transaction that this slot needs the database to retain. VACUUM cannot remove tuples deleted by any later transaction.
catalog_xmin xid
The oldest transaction affecting the system catalogs that this slot needs the database to retain. VACUUM cannot remove catalog tuples deleted by any later transaction.
restart_lsn pg_lsn
The address (LSN) of oldest WAL which still might be required by the consumer of this slot and thus won't be automatically removed during checkpoints unless this LSN gets behind more than max_slot_wal_keep_size from the current LSN. NULL if the LSN of this slot has never been reserved.
confirmed_flush_lsn pg_lsn
The address (LSN) up to which the logical slot's consumer has confirmed receiving data. Data older than this is not available anymore. NULL for physical slots.
wal_status text
Availability of WAL files claimed by this slot. Possible values are:
reserved means that the claimed files are within max_wal_size.
extended means that max_wal_size is exceeded but the files are still retained, either by the replication slot or by wal_keep_size.
unreserved means that the slot no longer retains the required WAL files and some of them are to be removed at the next checkpoint. This state can return to reserved or extended.
lost means that some required WAL files have been removed and this slot is no longer usable.
The last two states are seen only when max_slot_wal_keep_size is non-negative. If restart_lsn is NULL, this field is null.
safe_wal_size int8
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 max_slot_wal_keep_size is -1.
two_phase bool
True if the slot is enabled for decoding prepared transactions. Always false for physical slots.
rolname
name
角色名稱
rolsuper
bool
角色具有超級使用者權限
rolinherit
bool
角色自動繼承它所屬角色的權限
rolcreaterole
bool
角色可以建立更多角色
rolcreatedb
bool
角色可以建立資料庫
rolcanlogin
bool
角色可以登入。也就是說，可以將此角色作為初始連線認證使用
rolreplication
bool
角色是可以進行資料複寫的角色。複寫角色表示可以啟動資料複寫連線並建立和刪除複寫對象。
rolconnlimit
int4
對於可以登入的角色，這個設定此角色可以建立的最大同時連線數。 -1 意味著沒有限制。
rolpassword
text
不是密碼（讀出來都是********）
rolvaliduntil
timestamptz
密碼到期時間（僅用於密碼驗證）; 如果沒有到期時間，則顯示 null
rolbypassrls
bool
角色繞過每一個資料列層級的安全原則，參閱第 5.8 節了解更多訊息。
rolconfig
text[]
執行環境時用於角色的組態預設值
oid
oid
pg_authid.oid
角色的 ID
Column Type
Description
abbrev text
Time zone abbreviation
utc_offset interval
Offset from UTC (positive means east of Greenwich)
is_dst bool
True if this is a daylight-savings abbreviation
Column Type
Description
name text
The name of the shared memory allocation. NULL for unused memory and <anonymous> for anonymous allocations.
off int8
The offset at which the allocation starts. NULL for anonymous allocations, since details related to them are not known.
size int8
Size of the allocation
allocated_size int8
Size of the allocation including padding. For anonymous allocations, no information about padding is available, so the size and allocated_size columns will always be equal. Padding is not meaningful for free memory, so the columns will be equal in that case also.
Column Type
Description
schemaname name (references pg_namespace.nspname)
Name of schema containing view
viewname name (references pg_class.relname)
Name of view
viewowner name (references pg_authid.rolname)
Name of view's owner
definition text
View definition (a reconstructed SELECT query)
Column Type
Description
schemaname name (references pg_namespace.nspname)
Name of schema containing table
tablename name (references pg_class.relname)
Name of table
tableowner name (references pg_authid.rolname)
Name of table's owner
tablespace name (references pg_tablespace.spcname)
Name of tablespace containing table (null if default for database)
hasindexes bool (references pg_class.relhasindex)
True if table has (or recently had) any indexes
hasrules bool (references pg_class.relhasrules)
True if table has (or once had) rules
hastriggers bool (references pg_class.relhastriggers)
True if table has (or once had) triggers
rowsecurity bool (references pg_class.relrowsecurity)
True if row security is enabled on the table
Column Type
Description
usename name (references pg_authid.rolname)
User name
usesysid oid (references pg_authid.oid)
ID of this user
usecreatedb bool
User can create databases
usesuper bool
User is a superuser
userepl bool
User can initiate streaming replication and put the system in and out of backup mode.
usebypassrls bool
User bypasses every row-level security policy, see Section 5.8 for more information.
passwd text
Password (possibly encrypted); null if none. See pg_authid for details of how encrypted passwords are stored.
valuntil timestamptz
Password expiry time (only used for password authentication)
useconfig text[]
Session defaults for run-time configuration variables
available extensions
available versions of extensions
backend memory contexts
compile-time configuration parameters
open cursors
summary of configuration file contents
groups of database users
summary of client authentication configuration file contents
summary of client user name mapping configuration file contents
indexes
locks currently held or awaited
materialized views
policies
prepared statements
prepared transactions
publications and information of their associated tables
information about replication origins, including replication progress
replication slot information
database roles
rules
security labels
sequences
parameter settings
database users
shared memory allocations
planner statistics
extended planner statistics
extended planner statistics for expressions
tables
time zone abbreviations
time zone names
database users
user mappings
views
Column Type
Description
usename name
User name
usesysid oid
ID of this user
usecreatedb bool
User can create databases
usesuper bool
User is a superuser
userepl bool
User can initiate streaming replication and put the system in and out of backup mode.
usebypassrls bool
User bypasses every row-level security policy, see Section 5.8 for more information.
passwd text
Not the password (always reads as ********)
valuntil timestamptz
Password expiry time (only used for password authentication)
useconfig text[]
Session defaults for run-time configuration variables
Column Type
Description
schemaname name (references pg_namespace.nspname)
Name of schema containing table
tablename name (references pg_class.relname)
Name of table
attname name (references pg_attribute.attname)
Name of column described by this row
inherited bool
If true, this row includes values from child tables, not just the values in the specified table
null_frac float4
Fraction of column entries that are null
avg_width int4
Average width in bytes of column's entries
n_distinct float4
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 ANALYZE believes that the number of distinct values is likely to increase as the table grows; the positive form is used when the column seems to have a fixed number of possible values.) For example, -1 indicates a unique column in which the number of distinct values is the same as the number of rows.
most_common_vals anyarray
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 float4[]
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 anyarray
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 < operator or if the most_common_vals list accounts for the entire population.)
correlation float4
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 < operator.)
most_common_elems anyarray
A list of non-null element values most often appearing within values of the column. (Null for scalar types.)
most_common_elem_freqs float4[]
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 float4[]
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.)
Column Type
Description
name text
執行階段的組態參數名稱
setting text
參數的現值
unit text
參數隱含的單位
category text
參數的邏輯分類
short_desc text
參數的簡要說明
extra_desc text
更進一步的詳細參數說明
context text
組態參數值的必要內容（詳見下文）
vartype text
參數型別（bool、enum、integer、real 或 string）
source text
目前參數值的來源
min_val text
參數的最小允許值（非數字型別為 null）
max_val text
參數的最大允許值（非數字型別為 null）
enumvals text[]
列舉參數的允許值（非列舉型別為 null）
boot_val text
如果未另行設定參數，則在伺服器啟動時預先給予參數值
reset_val text
RESET 將參數重置為目前連線中的值
sourcefile text
組態檔案目前設定為何（對於從組態檔案以外來源設定的值，或者由非超級使用者也不是 pg_read_all_settings 的成員所給予，為null）；在組態檔案中使用 include 指令時會很有幫助。
sourceline int4
組態檔案中目前設定所在的行號（對於從組態檔案以外來源所設定的值，或者由非超級使用者，也不是 pg_read_all_settings 成員所給予的值，則為 null）。
pending_restart bool
如果組態檔案中的值已更改但需要重新啟動，則為 true；否則為 false。
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.
pg_timezone_names ColumnsColumn Type
Description
name text
Time zone name
abbrev text
Time zone abbreviation
utc_offset interval
Offset from UTC (positive means east of Greenwich)
is_dst bool
True if currently observing daylight savings