Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
版本：11
目錄 pg_language 註冊了可以撰寫函數或 stored procedure 的語言。有關語言處理程序的更多訊息，請參閱 CREATE LANGUAGE 和第 41 章。
pg_language Columns| Name | Type | References | Description |
|---|---|---|---|
oid
oid
資料列識別指標（隱藏屬性；必須明確選擇）
lanname
name
語言名稱
lanowner
oid
``pg_authid.oid
語言的所有者
lanispl
bool
對於內部語言（例如 SQL）而言這是 false 的，對於使用者定義的語言則是 true。目前，pg_dump 仍然使用它來決定需要轉存哪些語言，但將來可能會被不同的機制所取代。
lanpltrusted
bool
如果這是一種受信任的語言，則為 True，這意味著它被認為不會授予對正常 SQL 執行環境之外任何內容的存取權限。只有超級使用者才能以不受信任的語言建立函數。
lanplcallfoid
oid
``pg_proc.oid
對於非內部語言，這引用了語言處理程序，它是一個特殊的函數，負責執行使用特定語言所編寫的所有函數。
laninline
oid
``pg_proc.oid
這引用了一個負責執行 “inline” 匿名程式區塊（DO 區塊）的函數。如果不支援 inline 區塊，則為零。
lanvalidator
oid
``pg_proc.oid
這引用了一個語言驗證器函數，該函數負責在建立新函數時檢查它們的語法和有效性。如果未提供驗證器，則為零。
lanacl
aclitem[]
目錄 pg_auth_members 顯示角色之間的成員資格關連。允許任何非循環的關連。
由於角色身份是叢集範圍的，因此 pg_auth_members 在叢集的所有資料庫之間共享：每個叢集只有一個 pg_auth_members 副本，而不是每個資料庫一個副本。
Table 51.9. pg_auth_members Columns
| Name | Type | References | Description |
|---|---|---|---|
The catalog 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 indeed all objects that have pg_class entries.)
The term attribute is equivalent to column and is used for historical reasons.
pg_attribute Columns| Name | Type | References | Description |
|---|---|---|---|
In a dropped column's pg_attribute entry, atttypid is reset to zero, but attlen and the other fields copied from pg_type are still valid. This arrangement is needed to cope with the situation where the dropped column's data type was later dropped, and so there is no pg_type row anymore. attlen and the other fields can be used to interpret the contents of a row of the table.
The catalog pg_am stores information about relation access methods. There is one row for each access method supported by the system. Currently, only indexes have access methods. The requirements for index access methods are discussed in detail in Chapter 60.
Table 51.3. pg_am Columns
| Name | Type | References | Description |
|---|---|---|---|
Before PostgreSQL 9.6, pg_am contained many additional columns representing properties of index access methods. That data is now only directly visible at the C code level. However,pg_index_column_has_property() and related functions have been added to allow SQL queries to inspect index access method properties; see Table 9.63.
roleid
oid
``pg_authid.oid
具有成員的角色 ID
member
oid
pg_authid.oid
作為 roleid 成員的角色 ID
grantor
oid
``pg_authid.oid
授予此成員資格的角色 ID
admin_option
bool
如果成員可以將 roleid 的成員資格授予其他人，則為 True
attrelid
oid
pg_class.oid
The table this column belongs to
attname
name
The column name
atttypid
oid
pg_type.oid
The data type of this column
attstattarget
int4
attstattarget controls the level of detail of statistics accumulated for this column by ANALYZE. A zero value indicates that no statistics should be collected. A negative value says to use the system default statistics target. The exact meaning of positive values is data type-dependent. For scalar data types, attstattarget is both the target number of “most common values” to collect, and the target number of histogram bins to create.
attlen
int2
A copy of pg_type.typlen of this column's type
attnum
int2
The number of the column. Ordinary columns are numbered from 1 up. System columns, such as oid, have (arbitrary) negative numbers.
attndims
int4
Number of dimensions, if the column is an array type; otherwise 0. (Presently, the number of dimensions of an array is not enforced, so any nonzero value effectively means “it's an array”.)
attcacheoff
int4
Always -1 in storage, but when loaded into a row descriptor in memory this might be updated to cache the offset of the attribute within the row
atttypmod
int4
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 functions and length coercion functions. The value will generally be -1 for types that do not need atttypmod.
attbyval
bool
A copy of pg_type.typbyval of this column's type
attstorage
char
Normally a copy of pg_type.typstorage of this column's type. For TOAST-able data types, this can be altered after column creation to control storage policy.
attalign
char
A copy of pg_type.typalign of this column's type
attnotnull
bool
This represents a not-null 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.
attidentity
char
If a zero byte (''), then not an identity column. Otherwise, a = generated always, d = generated by default.
attisdropped
bool
This column has been dropped and is no longer valid. A dropped column is still physically present in the table, but is ignored by the parser and so cannot be accessed via SQL.
attislocal
bool
This column is defined locally in the relation. Note that a column can be locally defined and inherited simultaneously.
attinhcount
int4
The number of direct ancestors this column has. A column with a nonzero number of ancestors cannot be dropped nor renamed.
attcollation
oid
pg_collation.oid
The defined collation of the column, or zero if the column is not of a collatable data type.
attacl
aclitem[]
Column-level access privileges, if any have been granted specifically on this column
attoptions
text[]
Attribute-level options, as “keyword=value” strings
attfdwoptions
text[]
Attribute-level foreign data wrapper options, as “keyword=value” strings
oid
oid
Row identifier (hidden attribute; must be explicitly selected)
amname
name
Name of the access method
amhandler
regproc
pg_proc.oid
OID of a handler function that is responsible for supplying information about the access method
amtype
char
Currently always i to indicate an index access method; other values may be allowed in future
系統目錄（system catalog）是記錄資料庫管理系統儲存結構原始資料的地方，例如關於資料表和欄位的訊息以及內部日誌記錄訊息。PostgreSQL 的系統目錄是一般的資料表。您可以刪除並重新建立資料表、增加欄位、插入和更新內容，並以這種方式嚴重混淆您的系統。當然，通常情況下，不應該手動更改系統目錄，通常有 SQL 命令來執行此操作。（例如，CREATE DATABASE 向 pg_database 系統目錄插入一行 - 實際上是在磁碟上建立數據庫）。對於特別深奧的操作有一些例外，但其中很多已經隨著時間的推移而變為 SQL 命令，因此需要系統目錄的直接操作正在不斷減少。
The catalog pg_collation describes the available collations, which are essentially mappings from an SQL name to operating system locale categories. See Section 23.2 for more information.
Table 51.12. pg_collation Columns
| Name | Type | References | Description |
|---|---|---|---|
Note that the unique key on this catalog is (collname, collencoding, collnamespace) not just (collname, collnamespace). PostgreSQL generally ignores all collations that do not have collencoding equal to either the current database's encoding or -1, and creation of new entries with the same name as an entry with collencoding = -1 is forbidden. Therefore it is sufficient to use a qualified SQL name (schema.name) to identify a collation, even though this is not unique according to the catalog definition. The reason for defining the catalog this way is that initdb fills it in at cluster initialization time with entries for all locales available on the system, so it must be able to hold entries for all encodings that might ever be used in the cluster.
In the template0 database, it could be useful to create collations whose encoding does not match the database encoding, since they could match the encodings of databases later cloned from template0. This would currently have to be done manually.
The catalog pg_constraint stores check, primary key, unique, foreign key, and exclusion constraints on tables. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) Not-null constraints are represented in the pg_attribute catalog, not here.
User-defined constraint triggers (created with CREATE CONSTRAINT TRIGGER) also give rise to an entry in this table.
Check constraints on domains are stored here, too.
Table 51.13. pg_constraint Columns
| Name | Type | References | Description |
|---|---|---|---|
In the case of an exclusion constraint, conkey is only useful for constraint elements that are simple column references. For other cases, a zero appears in conkey and the associated index must be consulted to discover the expression that is constrained. (conkey thus has the same contents as pg_index.indkey for the index.)
consrc is not updated when referenced objects change; for example, it won't track renaming of columns. Rather than relying on this field, it's best to use pg_get_constraintdef() to extract the definition of a check constraint.
pg_class.relchecks needs to agree with the number of check-constraint entries found in this table for each relation.
The catalog pg_authid contains information about database authorization identifiers (roles). A role subsumes the concepts of “users” and “groups”. A user is essentially just a role with the rolcanlogin flag set. Any role (with or without rolcanlogin) can have other roles as members; see pg_auth_members.
Since this catalog contains passwords, it must not be publicly readable. pg_roles is a publicly readable view on pg_authid that blanks out the password field.
Chapter 21 contains detailed information about user and privilege management.
Because user identities are cluster-wide, pg_authid is shared across all databases of a cluster: there is only one copy of pg_authid per cluster, not one per database.
pg_authid ColumnsFor an MD5 encrypted password, rolpassword column will begin with the string md5 followed by a 32-character hexadecimal MD5 hash. The MD5 hash will be of the user's password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe.
If the password is encrypted with SCRAM-SHA-256, it has the format:
where salt, StoredKey and ServerKey are in Base64 encoded format. This format is the same as that specified by RFC 5803.
A password that does not follow either of those formats is assumed to be unencrypted.
系統目錄 pg_cast 儲存資料型別的轉換方式，包括了內建的和使用者定義的。
需要注意的是，pg_cast 並不代表系統知道如何執行的每一種型別轉換；只有那些不能從某些通用規則中推導出來的。例如，domain 和它的基本型別之間的轉換在 pg_cast 中就沒有明確表示。另一個重要的例外是「自動 I/O 強制轉換」，即使用資料型別自己的 I/O 函數執行的轉換為 text 或其他字串型別或從 text 及其他字串型別轉換的那些，在 pg_cast 中沒有明確表示。
pg_cast ColumnsThe cast functions listed in pg_cast must always take the cast source type as their first argument type, and return the cast destination type as their result type. A cast function can have up to three arguments. The second argument, if present, must be type integer; it receives the type modifier associated with the destination type, or -1 if there is none. The third argument, if present, must be type boolean; it receives true if the cast is an explicit cast, false otherwise.
It is legitimate to create a pg_cast entry in which the source and target types are the same, if the associated function takes more than one argument. Such entries represent “length coercion functions” that coerce values of the type to be legal for a particular type modifier value.
When a pg_cast entry has different source and target types and a function that takes more than one argument, it represents converting from one type to another and applying a length coercion in a single step. When no such entry is available, coercion to a type that uses a type modifier involves two steps, one to convert between data types and a second to apply the modifier.
檢視表 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 中的變更才會影響現有連線。
有關變更這些參數的各種方法和更多資訊，請參閱第 19.1 節。
pg_settings 檢視表無法INSERT 或 DELETE，但可以 UPDATE。套用於一行 pg_settings 的 UPDATE 相當於對該參數執行 SET 指令。此變更僅影響目前連線所使用的值。如果在稍後中止的交易事務中發出 UPDATE，則在回溯事務時 UPDATE 指令的效果會消失。一旦提交了相關的事務，則效果將持續到連線結束，除非被另一個 UPDATE 或 SET 覆蓋。
pg_roles 這個 view 提供對資料庫角色的資訊。 這只是 pg_authid 的一個公共可讀的 view，它將密碼部份予以屏蔽。
此 view 列出底層的 OID 欄位，因此可能需要執行與其他目錄的交叉查詢。
Table 51.83. pg_roles 欄位
| Name | Type | References | Description |
|---|---|---|---|
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 Columns目錄 pg_database 儲存有關資料庫一些可用的訊息。資料庫是使用 命令建立的。關於某些參數的含義的詳細訊息，請參閱。
與大多數系統目錄不同，pg_database 在叢集的所有資料庫之間共享：每個叢集只有一個 pg_database 副本，而不是每個資料庫一個副本。
Table 51.15. pg_database 欄位
| 名稱 | 型別 | 參閱 | 說明 |
|---|
目錄 pg_extension 儲存有關已安裝延伸功能的資訊。有關延伸功能的詳細資訊，請參閱。
Table 51.22. pg_extension Columns
| Name | Type | References | Description |
|---|
請注意，與大多數帶有「namespace」欄位的目錄不同，extnamespace 並不暗指該延伸功能屬於該綱要(schema)。延伸功能並不在任何綱要之中。不過 extnamespace 指示包含大多數或所有延伸功能所屬物件的綱要。如果 extrelocatable 為 true，則此綱要實際上必須包含屬於該延伸功能的所有需要綱要的物件。\
oid
oid
Row identifier (hidden attribute; must be explicitly selected)
collname
name
Collation name (unique per namespace and encoding)
collnamespace
oid
pg_namespace.oid
The OID of the namespace that contains this collation
collowner
oid
pg_authid.oid
Owner of the collation
collprovider
char
Provider of the collation: d = database default, c = libc, i = icu
collencoding
int4
Encoding in which the collation is applicable, or -1 if it works for any encoding
collcollate
name
LC_COLLATE for this collation object
collctype
name
LC_CTYPE for this collation object
collversion
text
Provider-specific version of the collation. This is recorded when the collation is created and then checked when it is used, to detect changes in the collation definition that could lead to data corruption.
oid
oid
Row identifier (hidden attribute; must be explicitly selected)
conname
name
Constraint name (not necessarily unique!)
connamespace
oid
pg_namespace.oid
The OID of the namespace that contains this constraint
contype
char
c = check constraint, f = foreign key constraint, p = primary key constraint, u = unique constraint, t = constraint trigger, x = exclusion constraint
condeferrable
bool
Is the constraint deferrable?
condeferred
bool
Is the constraint deferred by default?
convalidated
bool
Has the constraint been validated? Currently, can only be false for foreign keys and CHECK constraints
conrelid
oid
pg_class.oid
The table this constraint is on; 0 if not a table constraint
contypid
oid
pg_type.oid
The domain this constraint is on; 0 if not a domain constraint
conindid
oid
pg_class.oid
The index supporting this constraint, if it's a unique, primary key, foreign key, or exclusion constraint; else 0
confrelid
oid
pg_class.oid
If a foreign key, the referenced table; else 0
confupdtype
char
Foreign key update action code: a = no action, r = restrict, c = cascade, n = set null, d = set default
confdeltype
char
Foreign key deletion action code: a = no action, r = restrict, c = cascade, n = set null, d = set default
confmatchtype
char
Foreign key match type: f = full, p = partial, s = simple
conislocal
bool
This constraint is defined locally for the relation. Note that a constraint can be locally defined and inherited simultaneously.
coninhcount
int4
The number of direct inheritance ancestors this constraint has. A constraint with a nonzero number of ancestors cannot be dropped nor renamed.
connoinherit
bool
This constraint is defined locally for the relation. It is a non-inheritable constraint.
conkey
int2[]
pg_attribute.attnum
If a table constraint (including foreign keys, but not constraint triggers), list of the constrained columns
confkey
int2[]
pg_attribute.attnum
If a foreign key, list of the referenced columns
conpfeqop
oid[]
pg_operator.oid
If a foreign key, list of the equality operators for PK = FK comparisons
conppeqop
oid[]
pg_operator.oid
If a foreign key, list of the equality operators for PK = PK comparisons
conffeqop
oid[]
pg_operator.oid
If a foreign key, list of the equality operators for FK = FK comparisons
conexclop
oid[]
pg_operator.oid
If an exclusion constraint, list of the per-column exclusion operators
conbin
pg_node_tree
If a check constraint, an internal representation of the expression
consrc
text
If a check constraint, a human-readable representation of the expression
Column Type
Description
oid oid
Row identifier
rolname name
Role name
rolsuper bool
Role has superuser privileges
rolinherit bool
Role automatically inherits privileges of roles it is a member of
rolcreaterole bool
Role can create more roles
rolcreatedb bool
Role can create databases
rolcanlogin bool
Role can log in. That is, this role can be given as the initial session authorization identifier
rolreplication bool
Role is a replication role. A replication role can initiate replication connections and create and drop replication slots.
rolbypassrls bool
Role bypasses every row level security policy, see Section 5.8 for more information.
rolconnlimit int4
For roles that can log in, this sets maximum number of concurrent connections this role can make. -1 means no limit.
rolpassword text
Password (possibly encrypted); null if none. The format depends on the form of encryption used.
rolvaliduntil timestamptz
Password expiry time (only used for password authentication); null if no expiration
Column Type
Description
oid oid
Row identifier
castsource oid (references pg_type.oid)
OID of the source data type
casttarget oid (references pg_type.oid)
OID of the target data type
castfunc oid (references pg_proc.oid)
The OID of the function to use to perform this cast. Zero is stored if the cast method doesn't require a function.
castcontext char
Indicates what contexts the cast can be invoked in. e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases.
castmethod char
Indicates how the cast is performed. f means that the function specified in the castfunc field is used. i means that the input/output functions are used. b means that the types are binary-coercible, thus no conversion is required.
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。
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
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 |
The OID of the |
The OID of the |
The total number of columns in the index (duplicates |
The number of key columns in the index, not counting any included columns, which are merely stored and do not participate in the index semantics |
If true, this is a unique index |
If true, this index represents the primary key of the table ( |
If true, this index supports an exclusion constraint |
If true, the uniqueness check is enforced immediately on insertion (irrelevant if |
If true, the table was last clustered on this index |
If true, the index is currently valid for queries. False means the index is possibly incomplete: it must still be modified by |
If true, queries must not use the index until the |
If true, the index is currently ready for inserts. False means the index must be ignored by |
If false, the index is in process of being dropped, and should be ignored for all purposes (including HOT-safety decisions) |
If true this index has been chosen as “replica identity” using |
This is an array of |
For each column in the index key ( |
This is an array of |
Expression trees (in |
Expression tree (in |
|
| 資料列識別指標（隱藏屬性；必須明確選擇） |
|
| 資料庫名稱 |
|
| .oid | 資料庫的擁有者，通常是建立它的使用者 |
|
| 此資料庫的字元編碼（pg_encoding_to_char（）可將此數字轉換為編碼名稱） |
|
| 這個資料庫的 LC_COLLATE |
|
| 這個資料庫的 LC_CTYPE |
|
| 如果為 true，則該資料庫可以由具有 CREATEDB 權限的任何使用者複製；如果為 false，則只有超級使用者或資料庫的擁有者才能複製它。 |
|
| 如果為 false，則沒有人可以連線到該資料庫。這用於保護 template0 資料庫免遭更改。 |
|
| 設定可以對此資料庫執行的最大同時連線數。-1 意味著沒有限制。 |
|
| 資料庫中的最後一個系統 OID；特別適用於 pg_dump |
|
| 在這個事務 ID 之前在此資料庫中的所有事務 ID，已被替換為永久（「 frozen」）。這用於追踪是否需要清理資料庫以防止事務 ID 重覆或允許縮減 pg_xact。它是每個資料表 pg_class.relfrozenxid 的最小值。 |
|
| 此資料庫中的所有 multixact ID 已被替換為該資料庫中的事務 ID。這用於追踪資料庫是否需要清理，以防止 multixact ID 重覆或允許縮減 pg_multixact。它是每個資料表 pg_class.relminmxid 的最小值。 |
|
| .oid | 資料庫預設的資料表空間。在此資料庫中，pg_class.reltablespace 為零的所有資料表都將儲存在此資料表空間中；特別是所有非共享系統目錄都將在那裡。 |
|
| 存取權限；詳情請參閱 和 |
|
| 資料列指標ID |
|
| 延伸功能名稱 |
|
| ``.oid | 延伸功能的擁有者 |
|
| ``.oid | 延伸功能之中所導出物件的綱要名稱 |
|
| 如果延伸功能可以接受重新定位到另一個綱要之中，則為 True |
|
| 延伸功能的版本名稱 |
|
| ``.oid | 延伸功能組態資料表的 regclass OID 陣列，如果沒有，則為 NULL |
|
| 延伸功能組態資料表的 WHERE 子句過濾條件陣列，如果沒有，則為 NULL |
版本：11
The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also pg_index), sequences (but see also pg_sequence), views, materialized views, composite types, and TOAST tables; see relkind. Below, when we mean all of these kinds of objects we speak of “relations”. Not all columns are meaningful for all relation types.
pg_class ColumnsSeveral of the Boolean flags in pg_class are maintained lazily: they are guaranteed to be true if that's the correct state, but may not be reset to false immediately when the condition is no longer true. For example, relhasindex is set by CREATE INDEX, but it is never cleared by DROP INDEX. Instead, VACUUM clears relhasindex if it finds the table has no indexes. This arrangement avoids race conditions and improves concurrency.\
The catalog pg_statistic stores statistical data about the contents of the database. Entries are created by ANALYZE and subsequently used by the query planner. Note that all the statistical data is inherently approximate, even assuming that it is up-to-date.
Normally there is one entry, with stainherit = false, for each table column that has been analyzed. If the table has inheritance children, a second entry with stainherit = true is also created. This row represents the column's statistics over the inheritance tree, i.e., statistics for the data you'd see with SELECT column FROM table*, whereas the stainherit = false row represents the results of SELECT column FROM ONLY table.
pg_statistic also stores statistical data about the values of index expressions. These are described as if they were actual data columns; in particular, starelid references the index. No entry is made for an ordinary non-expression index column, however, since it would be redundant with the entry for the underlying table column. Currently, entries for index expressions always have stainherit = false.
Since different kinds of statistics might be appropriate for different kinds of data, pg_statistic is designed not to assume very much about what sort of statistics it stores. Only extremely general statistics (such as nullness) are given dedicated columns in pg_statistic. Everything else is stored in “slots”, which are groups of associated columns whose content is identified by a code number in one of the slot's columns. For more information see src/include/catalog/pg_statistic.h.
pg_statistic should not be readable by the public, since even statistical information about a table's contents might be considered sensitive. (Example: minimum and maximum values of a salary column might be quite interesting.) pg_stats is a publicly readable view on pg_statistic that only exposes information about those tables that are readable by the current user.
pg_statistic ColumnsThe catalog pg_opclass defines index access method operator classes. Each operator class defines semantics for index columns of a particular data type and a particular index access method. An operator class essentially specifies that a particular operator family is applicable to a particular indexable column data type. The set of operators from the family that are actually usable with the indexed column are whichever ones accept the column's data type as their left-hand input.
Operator classes are described at length in Section 37.14.
Table 51.33. pg_opclass Columns
| Name | Type | References | Description |
|---|---|---|---|
An operator class's opcmethod must match the opfmethod of its containing operator family. Also, there must be no more than one pg_opclass row having opcdefault true for any given combination of opcmethod and opcintype.
The catalog pg_policy stores row level security policies for tables. A policy includes the kind of command that it applies to (possibly all commands), the roles that it applies to, the expression to be added as a security-barrier qualification to queries that include the table, and the expression to be added as a WITH CHECK option for queries that attempt to add new records to the table.
Table 51.38. pg_policy Columns
| Name | Type | References | Description |
|---|---|---|---|
Policies stored in pg_policy are applied only when pg_class.relrowsecurity is set for their table.
The catalog pg_tablespace stores information about the available tablespaces. Tables can be placed in particular tablespaces to aid administration of disk layout.
Unlike most system catalogs, pg_tablespace is shared across all databases of a cluster: there is only one copy of pg_tablespace per cluster, not one per database.
pg_tablespace ColumnsThe catalog pg_event_trigger stores event triggers. See Chapter 39 for more information.
pg_event_trigger Columns版本：11
目錄 pg_proc 儲存有關函數、程序函數、彙總函數和窗函數（或統稱為 routines）的資訊。 有關更多資訊，請參閱 ， 和。
如果 prokind 指示該項目用於彙總函數，則 pg_aggregate 中應有相對應的資料列。
pg_proc Columns| Name | Type | References | Description |
|---|
對於內建和動態載入的已編譯函數，prosrc 包含函數的 C 語言名稱（link symbol）。 對於所有其他目前已知的語言類型，prosrc 包含函數的原始碼。除了動態載入的 C 函數外，probin 均未使用，因為它用於記錄該函數的共享函式庫檔案的名稱。
The catalog pg_type stores information about data types. Base types and enum types (scalar types) are created with , and domains with . A composite type is automatically created for each table in the database, to represent the row structure of the table. It is also possible to create composite types with CREATE TYPE AS.
Table 51.62. pg_type Columns
| Name | Type | References | Description |
|---|
Table 51.63. typcategory Codes
版本：11
The catalog pg_statistic_ext holds definitions of extended planner statistics. Each row in this catalog corresponds to a statistics object created with .
pg_statistic_ext ColumnsThe pg_statistic_ext entry is filled in completely during CREATE STATISTICS, but the actual statistical values are not computed then. Subsequent ANALYZE commands compute the desired values and populate an entry in the catalog.
indexrelid oid (references .oid)
indrelid oid (references .oid)
indkey int2vector (references .attnum)
indcollation oidvector (references .oid)
indclass oidvector (references .oid)
For each column in the index key (indnkeyatts values), this contains the OID of the operator class to use. See for details.
lists the system-defined values of typcategory. Any future additions to this list will also be upper-case ASCII letters. All other ASCII characters are reserved for user-defined categories.
| Code | Category |
|---|
Column Type
Description
oid oid
Row identifier
relname name
Name of the table, index, view, etc.
relnamespace oid (references pg_namespace.oid)
The OID of the namespace that contains this relation
reltype oid (references pg_type.oid)
The OID of the data type that corresponds to this table's row type, if any (zero for indexes, which have no pg_type entry)
reloftype oid (references pg_type.oid)
For typed tables, the OID of the underlying composite type, zero for all other relations
relowner oid (references pg_authid.oid)
Owner of the relation
relam oid (references pg_am.oid)
If this is a table or an index, the access method used (heap, B-tree, hash, etc.)
relfilenode oid
Name of the on-disk file of this relation; zero means this is a “mapped” relation whose disk file name is determined by low-level state
reltablespace oid (references pg_tablespace.oid)
The tablespace in which this relation is stored. If zero, the database's default tablespace is implied. (Not meaningful if the relation has no on-disk file.)
relpages int4
Size of the on-disk representation of this table in pages (of size BLCKSZ). This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.
reltuples float4
Number of live rows in the table. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.
relallvisible int4
Number of pages that are marked all-visible in the table's visibility map. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.
reltoastrelid oid (references 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.
relhasindex bool
True if this is a table and it has (or recently had) any indexes
relisshared bool
True if this table is shared across all databases in the cluster. Only certain system catalogs (such as pg_database) are shared.
relpersistence char
p = permanent table, u = unlogged table, t = temporary table
relkind char
r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index
relnatts int2
Number of user columns in the relation (system columns not counted). 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_constraint catalog
relhasrules bool
True if table has (or once had) rules; see pg_rewrite catalog
relhastriggers bool
True if table has (or once had) triggers; see pg_trigger catalog
relhassubclass bool
True if table or index has (or once had) any inheritance children
relrowsecurity bool
True if table has row level security enabled; see pg_policy catalog
relforcerowsecurity bool
True if row level security (when enabled) will also apply to table owner; see pg_policy catalog
relispopulated bool
True if relation is populated (this is true for all relations other than some materialized views)
relreplident char
Columns used to form “replica identity” for rows: d = default (primary key, if any), n = nothing, f = all columns, i = index with indisreplident set (same as nothing if the index used has been dropped)
relispartition bool
True if table or index is a partition
relrewrite oid (references pg_class.oid)
For new relations being written during a DDL operation that requires a table rewrite, this contains the OID of the original relation; otherwise 0. That state is only visible internally; this field should never contain anything other than 0 for a user-visible relation.
relfrozenxid xid
All transaction IDs before this one have been replaced with a permanent (“frozen”) transaction ID in this table. This is used to track whether the table needs to be vacuumed in order to prevent transaction ID wraparound or to allow pg_xact to be shrunk. Zero (InvalidTransactionId) if the relation is not a table.
relminmxid xid
All multixact IDs before this one have been replaced by a transaction ID in this table. This is used to track whether the table needs to be vacuumed in order to prevent multixact ID wraparound or to allow pg_multixact to be shrunk. Zero (InvalidMultiXactId) if the relation is not a table.
relacl aclitem[]
Access privileges; see Section 5.7 for details
reloptions text[]
Access-method-specific options, as “keyword=value” strings
relpartbound pg_node_tree
If table is a partition (see relispartition), internal representation of the partition bound
Column Type
Description
oid oid
Row identifier
nspname name
Name of the namespace
nspowner oid (references pg_authid.oid)
Owner of the namespace
nspacl aclitem[]
存取權限； 詳見 5.7 節
oid
oid
Row identifier (hidden attribute; must be explicitly selected)
rulename
name
Rule name
ev_class
oid
pg_class.oid
The table this rule is for
ev_type
char
Event type that the rule is for: 1 = SELECT, 2 = UPDATE, 3 = INSERT, 4 = DELETE
ev_enabled
char
Controls in which session_replication_role modes the rule fires. O = rule fires in “origin” and “local” modes, D = rule is disabled, R = rule fires in “replica” mode, A = rule fires always.
is_instead
bool
True if the rule is an INSTEAD rule
ev_qual
pg_node_tree
Expression tree (in the form of a nodeToString() representation) for the rule's qualifying condition
ev_action
pg_node_tree
Query tree (in the form of a nodeToString() representation) for the rule's action
Column Type
Description
starelid oid (references pg_class.oid)
The table or index that the described column belongs to
staattnum int2 (references pg_attribute.attnum)
The number of the described column
stainherit bool
If true, the stats include inheritance child columns, not just the values in the specified relation
stanullfrac float4
The fraction of the column's entries that are null
stawidth int4
The average stored width, in bytes, of nonnull entries
stadistinct float4
The number of distinct nonnull data values in the column. A value greater than zero is the actual number of distinct values. A value less than zero is the negative of a multiplier for the number of rows in the table; for example, a column in which about 80% of the values are nonnull and each nonnull value appears about twice on average could be represented by stadistinct = -0.4. A zero value means the number of distinct values is unknown.
stakindN int2
A code number indicating the kind of statistics stored in the Nth “slot” of the pg_statistic row.
staopN oid (references pg_operator.oid)
An operator used to derive the statistics stored in the Nth “slot”. For example, a histogram slot would show the < operator that defines the sort order of the data.
stacollN oid (references pg_collation.oid)
The collation used to derive the statistics stored in the Nth “slot”. For example, a histogram slot for a collatable column would show the collation that defines the sort order of the data. Zero for noncollatable data.
stanumbersN float4[]
Numerical statistics of the appropriate kind for the Nth “slot”, or null if the slot kind does not involve numerical values
stavaluesN anyarray
Column data values of the appropriate kind for the Nth “slot”, or null if the slot kind does not store any data values. Each array's element values are actually of the specific column's data type, or a related type such as an array's element type, so there is no way to define these columns' type more specifically than anyarray.
oid
oid
Row identifier (hidden attribute; must be explicitly selected)
opcmethod
oid
pg_am.oid
Index access method operator class is for
opcname
name
Name of this operator class
opcnamespace
oid
pg_namespace.oid
Namespace of this operator class
opcowner
oid
pg_authid.oid
Owner of the operator class
opcfamily
oid
pg_opfamily.oid
Operator family containing the operator class
opcintype
oid
pg_type.oid
Data type that the operator class indexes
opcdefault
bool
True if this operator class is the default for opcintype
opckeytype
oid
pg_type.oid
Type of data stored in index, or zero if same as opcintype
polname
name
The name of the policy
polrelid
oid
pg_class.oid
The table to which the policy applies
polcmd
char
The command type to which the policy is applied: r for SELECT, a for INSERT, w for UPDATE, d for DELETE, or * for all
polpermissive
boolean
Is the policy permissive or restrictive?
polroles
oid[]
pg_authid.oid
The roles to which the policy is applied
polqual
pg_node_tree
The expression tree to be added to the security barrier qualifications for queries that use the table
polwithcheck
pg_node_tree
The expression tree to be added to the WITH CHECK qualifications for queries that attempt to add rows to the table
Column Type
Description
oid oid
Row identifier
spcname name
Tablespace name
spcowner oid (references pg_authid.oid)
Owner of the tablespace, usually the user who created it
spcacl aclitem[]
Access privileges; see Section 5.7 for details
spcoptions text[]
Tablespace-level options, as “keyword=value” strings
Column Type
Description
oid oid
Row identifier
evtname name
Trigger name (must be unique)
evtevent name
Identifies the event for which this trigger fires
evtowner oid (references pg_authid.oid)
Owner of the event trigger
evtfoid oid (references pg_proc.oid)
The function to be called
evtenabled char
Controls in which session_replication_role modes the event trigger fires. O = trigger fires in “origin” and “local” modes, D = trigger is disabled, R = trigger fires in “replica” mode, A = trigger fires always.
evttags text[]
Command tags for which this trigger will fire. If NULL, the firing of this trigger is not restricted on the basis of the command tag.
| Array types |
| Boolean types |
| Composite types |
| Date/time types |
| Enum types |
| Geometric types |
| Network address types |
| Numeric types |
| Pseudo-types |
| Range types |
| String types |
| Timespan types |
| User-defined types |
| Bit-string types |
|
|
|
| Extension name |
|
| 預設版本的名稱；如果未指定，則為 NULL |
|
| 目前安裝的延伸功能版本，如果未安裝，則為 NULL |
|
| 延伸功能的控制檔案中的註解文字內容 |
|
| Row identifier (hidden attribute; must be explicitly selected) |
|
| Name of the function |
|
| The OID of the namespace that contains this function |
|
| Owner of the function |
|
| Implementation language or call interface of this function |
|
|
|
| Estimated number of result rows (zero if not |
|
| Data type of the variadic array parameter's elements, or zero if the function does not have a variadic parameter |
|
|
|
|
|
|
| Function is a security definer (i.e., a “setuid” function) |
|
| The function has no side effects. No information about the arguments is conveyed except via the return value. Any function that might throw an error depending on the values of its arguments is not leak-proof. |
|
| Function returns null if any call argument is null. In that case the function won't actually be called at all. Functions that are not “strict” must be prepared to handle null inputs. |
|
| Function returns a set (i.e., multiple values of the specified data type) |
|
|
|
|
|
|
|
| Number of input arguments |
|
| Number of arguments that have defaults |
|
| Data type of the return value |
|
| An array with the data types of the function arguments. This includes only input arguments (including |
|
| An array with the data types of the function arguments. This includes all arguments (including |
|
| An array with the modes of the function arguments, encoded as |
|
| An array with the names of the function arguments. Arguments without a name are set to empty strings in the array. If none of the arguments have a name, this field will be null. Note that subscripts correspond to positions of |
|
| Expression trees (in |
|
| Data type OIDs for which to apply transforms. |
|
| This 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 on the implementation language/call convention. |
|
| Additional information about how to invoke the function. Again, the interpretation is language-specific. |
|
| Function's local settings for run-time configuration variables |
|
|
|
| Row identifier (hidden attribute; must be explicitly selected) |
|
| Data type name |
|
| The OID of the namespace that contains this type |
|
| Owner of the type |
|
| For a fixed-size type, |
|
|
|
|
|
|
|
|
|
| True if the type is a preferred cast target within its |
|
| True if the type is defined, false if this is a placeholder entry for a not-yet-defined type. When |
|
| Character that separates two values of this type when parsing array input. Note that the delimiter is associated with the array element data type, not the array data type. |
|
| If this is a composite type (see |
|
| If |
|
| If |
|
| Input conversion function (text format) |
|
| Output conversion function (text format) |
|
| Input conversion function (binary format), or 0 if none |
|
| Output conversion function (binary format), or 0 if none |
|
| Type modifier input function, or 0 if type does not support modifiers |
|
| Type modifier output function, or 0 to use the standard format |
|
| Custom |
|
|
|
|
|
|
|
|
|
|
| If this is a domain (see |
|
| Domains use |
|
|
|
|
|
|
|
| If |
|
|
|
|
|
Column Type Description |
Row identifier |
OID of the database which the subscription resides in |
Name of the subscription |
Owner of the subscription |
If true, the subscription is enabled and should be replicating. |
Connection string to the upstream database |
Name of the replication slot in the upstream database (also used for the local replication origin name); null represents |
Contains the value of the |
Array of subscribed publication names. These reference the publications on the publisher server. For more on publications see . |
Column Type Description |
Reference to subscription |
Reference to relation |
State code: |
Remote LSN of the state change used for synchronization coordination when in |
Column Type Description |
Row identifier |
Table containing the columns described by this object |
Name of the statistics object |
The OID of the namespace that contains this statistics object |
Owner of the statistics object |
|
An array of attribute numbers, indicating which table columns are covered by this statistics object; for example a value of |
An array containing codes for the enabled statistic kinds; valid values are: |
The catalog pg_trigger stores triggers on tables and views. See CREATE TRIGGER for more information.
Table 51.56. pg_trigger Columns
| Name | Type | References | Description |
|---|---|---|---|
Currently, column-specific triggering is supported only for UPDATE events, and so tgattr is relevant only for that event type. tgtype might contain bits for other event types as well, but those are presumed to be table-wide regardless of what is in tgattr.
When tgconstraint is nonzero, tgconstrrelid, tgconstrindid, tgdeferrable, and tginitdeferred are largely redundant with the referenced pg_constraint entry. However, it is possible for a non-deferrable trigger to be associated with a deferrable constraint: foreign key constraints can have some deferrable and some non-deferrable triggers.
pg_class.relhastriggers must be true if a relation has any triggers in this catalog.
The view pg_prepared_xacts displays information about transactions that are currently prepared for two-phase commit (see PREPARE TRANSACTION for details).
pg_prepared_xacts contains one row per prepared transaction. An entry is removed when the transaction is committed or rolled back.
pg_prepared_xacts ColumnsWhen 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.
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.
pg_hba_file_rules ColumnsUsually, a row reflecting an incorrect entry will have values for only the line_number and error fields.
使用者身份驗證設定的相關資訊，請參閱第 20 章。
pg_available_extension_versions 檢視圖列出可用於安裝的特定延伸功能版本。另請參閱 pg_extension 目錄，該目錄列出了目前已安裝的延伸功能。
pg_available_extension_versions Columns| Name | Type | Description |
|---|---|---|
pg_available_extension_versions 檢視表是唯讀的。
The pg_replication_origin_status view contains information about how far replay for a certain origin has progressed. For more on replication origins see .
Table 51.80. pg_replication_origin_status Columns
| Name | Type | References | Description |
|---|
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.
pg_locks Columnsgranted 為 true 的話，代表此鎖定由該筆資料的程序所持有。False 表示此程序目前正在等待取得鎖定，這意味著至少一個其他程序正持有或等待同一可鎖定物件上在鎖定模式有衝突。等待的程序將會一直休眠，直到另一個鎖定被釋放（或檢測到 deadlock 情況）為止。一個程序等待最多只可以取得一個鎖定。
在整個交易事務執行過程中，伺服器程序對事務的虛擬事務 ID 持有排他鎖定(exclusive lock)。如果將永久性 ID 分配給事務（通常僅在事務變更資料庫狀態時才會發生），它還會對事務的永久性事務 ID 持有排他鎖定，直到結束。當一個程序發現有必要專門等待另一個事務結束時，它透過嘗試獲取另一個事務的 ID（取決於情況的虛擬 ID 或永久 ID）上的共享鎖定(share lock)來做到這一點。僅當另一個事務結束並釋放其鎖定時，該操作才會成功。
儘管 tuple 是可鎖定的物件型別，但是有關資料列級鎖定的資訊是儲存在磁碟上，而不是儲存在記憶體之中，因此資料列級的鎖定通常不會出現在此檢視表中。如果程序正在等待資料列級的鎖定，則它通常在檢視表中顯示為正在等待該資料列鎖定目前持有者的永久事務 ID。
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.
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 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.\
The pg_shmem_allocations view shows allocations made from the server's main shared memory segment. This includes both memory allocated by postgres 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.
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.
.oid
.oid
.oid
Estimated execution cost (in units of ); if proretset, this is cost per row returned
.oid
.oid
Calls to this function can be simplified by this other function (see )
.oid
.oid
.oid
Access privileges; see and for details
.oid
.oid
typcategory is an arbitrary classification of data types that is used by the parser to determine which implicit casts should be “preferred”. See .
.oid
.oid
.oid
.oid
.oid
.oid
.oid
.oid
.oid
.oid
.oid
.oid
Access privileges; see and for details
The pid column can be joined to the pid column of the view to get more information on the session holding or awaiting each lock, for example
Also, if you are using prepared transactions, the virtualtransaction column can be joined to the transaction column of the 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:
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.
oid
oid
Row identifier (hidden attribute; must be explicitly selected)
tgrelid
oid
pg_class.oid
The table this trigger is on
tgname
name
Trigger name (must be unique among triggers of same table)
tgfoid
oid
pg_proc.oid
The function to be called
tgtype
int2
Bit mask identifying trigger firing conditions
tgenabled
char
Controls in which session_replication_role modes the trigger fires. O = trigger fires in “origin” and “local” modes, D = trigger is disabled, R = trigger fires in “replica” mode, A = trigger fires always.
tgisinternal
bool
True if trigger is internally generated (usually, to enforce the constraint identified by tgconstraint)
tgconstrrelid
oid
pg_class.oid
The table referenced by a referential integrity constraint
tgconstrindid
oid
pg_class.oid
The index supporting a unique, primary key, referential integrity, or exclusion constraint
tgconstraint
oid
pg_constraint.oid
The pg_constraint entry associated with the trigger, if any
tgdeferrable
bool
True if constraint trigger is deferrable
tginitdeferred
bool
True if constraint trigger is initially deferred
tgnargs
int2
Number of argument strings passed to trigger function
tgattr
int2vector
pg_attribute.attnum
Column numbers, if trigger is column-specific; otherwise an empty array
tgargs
bytea
Argument strings to pass to trigger, each NULL-terminated
tgqual
pg_node_tree
Expression tree (in nodeToString() representation) for the trigger's WHEN condition, or null if none
tgoldtable
name
REFERENCING clause name for OLD TABLE, or null if none
tgnewtable
name
REFERENCING clause name for NEW TABLE, or null if none
Column Type
Description
transaction xid
Numeric transaction identifier of the prepared transaction
gid text
Global transaction identifier that was assigned to the transaction
prepared timestamptz
Time at which the transaction was prepared for commit
owner name (references pg_authid.rolname)
Name of the user that executed the transaction
database name (references pg_database.datname)
Name of the database in which the transaction was executed
Column Type
Description
line_number int4
Line number of this rule in pg_hba.conf
type text
Type of connection
database text[]
List of database name(s) to which this rule applies
user_name text[]
List of user and group name(s) to which this rule applies
address text
Host name or IP address, or one of all, samehost, or samenet, or null for local connections
netmask text
IP address mask, or null if not applicable
auth_method text
Authentication method
options text[]
Options specified for authentication method, if any
error text
If not null, an error message indicating why this line could not be processed
name
name
延伸功能名稱
version
text
版本名稱
installed
bool
如果目前已安裝此延伸功能的此版本，則為 True
superuser
bool
如果僅允許超級使用者安裝此延伸功能，則為 True
relocatable
bool
如果延伸功能可以接受重新定位到另一個綱要，則為 True
schema
name
延伸功能必須安裝到的綱要名稱，如果可部分或完全重新定位，則為 NULL
requires
name[]
必須預先安裝的延伸功能名稱，如果沒有則為 NULL
comment
text
延伸功能控制檔案中的註解文字內容
Column Type
Description
schemaname name (references pg_namespace.nspname)
Name of schema containing table and index
tablename name (references pg_class.relname)
Name of table the index is for
indexname name (references pg_class.relname)
Name of index
tablespace name (references pg_tablespace.spcname)
Name of tablespace containing index (null if default for database)
indexdef text
Index definition (a reconstructed CREATE INDEX command)
Column Type Description |
The name of the shared memory allocation. NULL for unused memory and |
The offset at which the allocation starts. NULL for anonymous allocations, since details related to them are not known. |
Size of the allocation |
Size of the allocation including padding. For anonymous allocations, no information about padding is available, so the |
Column Type Description |
|
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 |
OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation |
Page number targeted by the lock within the relation, or null if the target is not a relation page or tuple |
Tuple number targeted by the lock within the page, or null if the target is not a tuple |
Virtual ID of the transaction targeted by the lock, or null if the target is not a virtual transaction ID |
ID of the transaction targeted by the lock, or null if the target is not a transaction ID |
OID of the system catalog containing the lock target, or null if the target is not a general database object |
OID of the lock target within its system catalog, or null if the target is not a general database object |
Column number targeted by the lock (the |
Virtual ID of the transaction that is holding or awaiting this lock |
Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction |
|
True if lock is held, false if lock is awaited |
True if lock was taken via fast path, false if taken via main lock table |
|
| .roident | internal node identifier |
|
| .roname | external node identifier |
|
| The origin node's LSN up to which data has been replicated. |
|
| This node's LSN at which |
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.
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.
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 26.2.6 and Chapter 48.
pg_replication_slots ColumnsType of the lockable object: relation, extend, frozenid, page, tuple, transactionid, virtualxid, spectoken, object, userlock, or advisory. (See also.)
database oid (references .oid)
relation oid (references .oid)
classid oid (references .oid)
Name of the lock mode held or desired by this process (see and )
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 the column described by this row
inherited bool
If true, this row includes inheritance child columns, 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
schemaname name (references pg_namespace.nspname)
檢視表的綱要名稱
viewname name (references pg_class.relname)
檢視表名稱
viewowner name (references pg_authid.rolname)
檢視圖的擁有者名稱
definition text
檢視表的定義（重新建構的 SELECT 查詢）
schemaname
name
pg_namespace.nspname
Name of schema containing table
tablename
name
pg_class.relname
Name of table
tableowner
name
pg_authid.rolname
Name of table's owner
tablespace
name
pg_tablespace.spcname
Name of tablespace containing table (null if default for database)
hasindexes
boolean
pg_class.relhasindex
True if table has (or recently had) any indexes
hasrules
boolean
pg_class.relhasrules
True if table has (or once had) rules
hastriggers
boolean
pg_class.relhastriggers
True if table has (or once had) triggers
rowsecurity
boolean
pg_class.relrowsecurity
True if row security is enabled on the table
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.