The functions described in this section are used to obtain various information about a PostgreSQL installation.
Table 9.69 shows several functions that extract session and system information.
In addition to the functions listed in this section, there are a number of functions related to the statistics system that also provide system information. See Section 27.2.26 for more information.
Table 9.69. Session Information Functions
Function Description |
---|
Returns the name of the current database. (Databases are
called “catalogs” in the SQL standard,
so |
Returns the text of the currently executing query, as submitted by the client (which might contain more than one statement). |
This is equivalent to |
Returns the name of the schema that is first in the search path (or a null value if the search path is empty). This is the schema that will be used for any tables or other named objects that are created without specifying a target schema. |
Returns an array of the names of all schemas presently in the
effective search path, in their priority order. (Items in the current
search_path setting that do not correspond to
existing, searchable schemas are omitted.) If the Boolean argument
is |
Returns the user name of the current execution context. |
Returns the IP address of the current client,
or |
Returns the IP port number of the current client,
or |
Returns the IP address on which the server accepted the current
connection,
or |
Returns the IP port number on which the server accepted the current
connection,
or |
Returns the process ID of the server process attached to the current session. |
Returns an array of the process ID(s) of the sessions that are blocking the server process with the specified process ID from acquiring a lock, or an empty array if there is no such server process or it is not blocked.
One server process blocks another if it either holds a lock that
conflicts with the blocked process's lock request (hard block), or is
waiting for a lock that would conflict with the blocked process's lock
request and is ahead of it in the wait queue (soft block). When using
parallel queries the result always lists client-visible process IDs
(that is, Frequent calls to this function could have some impact on database performance, because it needs exclusive access to the lock manager's shared state for a short time. |
Returns the time when the server configuration files were last loaded. If the current session was alive at the time, this will be the time when the session itself re-read the configuration files (so the reading will vary a little in different sessions). Otherwise it is the time when the postmaster process re-read the configuration files. |
Returns the path name of the log file currently in use by the logging
collector. The path includes the log_directory
directory and the individual log file name. The result
is
This function is restricted to superusers and roles with privileges of
the |
Returns the OID of the current session's temporary schema, or zero if it has none (because it has not created any temporary tables). |
Returns true if the given OID is the OID of another session's temporary schema. (This can be useful, for example, to exclude other sessions' temporary tables from a catalog display.) |
Returns true if a JIT compiler extension is
available (see Chapter 30) and the
jit configuration parameter is set to
|
Returns the set of names of asynchronous notification channels that the current session is listening to. |
Returns the fraction (0–1) of the asynchronous notification queue's maximum size that is currently occupied by notifications that are waiting to be processed. See LISTEN and NOTIFY for more information. |
Returns the time when the server started. |
Returns an array of the process ID(s) of the sessions that are blocking the server process with the specified process ID from acquiring a safe snapshot, or an empty array if there is no such server process or it is not blocked.
A session running a Frequent calls to this function could have some impact on database performance, because it needs access to the predicate lock manager's shared state for a short time. |
Returns the current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger). |
Returns the session user's name. |
Returns the authentication method and the identity (if any) that the
user presented during the authentication cycle before they were
assigned a database role. It is represented as
|
This is equivalent to |
current_catalog
,
current_role
,
current_schema
,
current_user
,
session_user
,
and user
have special syntactic status
in SQL: they must be called without trailing
parentheses. In PostgreSQL, parentheses can optionally be used with
current_schema
, but not with the others.
The session_user
is normally the user who initiated
the current database connection; but superusers can change this setting
with SET SESSION AUTHORIZATION.
The current_user
is the user identifier
that is applicable for permission checking. Normally it is equal
to the session user, but it can be changed with
SET ROLE.
It also changes during the execution of
functions with the attribute SECURITY DEFINER
.
In Unix parlance, the session user is the “real user” and
the current user is the “effective user”.
current_role
and user
are
synonyms for current_user
. (The SQL standard draws
a distinction between current_role
and current_user
, but PostgreSQL
does not, since it unifies users and roles into a single kind of entity.)
Table 9.70 lists functions that
allow querying object access privileges programmatically.
(See Section 5.8 for more information about
privileges.)
In these functions, the user whose privileges are being inquired about
can be specified by name or by OID
(pg_authid
.oid
), or if
the name is given as public
then the privileges of the
PUBLIC pseudo-role are checked. Also, the user
argument can be omitted entirely, in which case
the current_user
is assumed.
The object that is being inquired about can be specified either by name or
by OID, too. When specifying by name, a schema name can be included if
relevant.
The access privilege of interest is specified by a text string, which must
evaluate to one of the appropriate privilege keywords for the object's type
(e.g., SELECT
). Optionally, WITH GRANT
OPTION
can be added to a privilege type to test whether the
privilege is held with grant option. Also, multiple privilege types can be
listed separated by commas, in which case the result will be true if any of
the listed privileges is held. (Case of the privilege string is not
significant, and extra whitespace is allowed between but not within
privilege names.)
Some examples:
SELECT has_table_privilege('myschema.mytable', 'select'); SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION');
Table 9.70. Access Privilege Inquiry Functions
Function Description |
---|
Does user have privilege for any column of table?
This succeeds either if the privilege is held for the whole table, or
if there is a column-level grant of the privilege for at least one
column.
Allowable privilege types are
|
Does user have privilege for the specified table column?
This succeeds either if the privilege is held for the whole table, or
if there is a column-level grant of the privilege for the column.
The column can be specified by name or by attribute number
( |
Does user have privilege for database?
Allowable privilege types are
|
Does user have privilege for foreign-data wrapper?
The only allowable privilege type is |
Does user have privilege for function?
The only allowable privilege type is
When specifying a function by name rather than by OID, the allowed
input is the same as for the SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
|
Does user have privilege for language?
The only allowable privilege type is |
Does user have privilege for configuration parameter?
The parameter name is case-insensitive.
Allowable privilege types are |
Does user have privilege for schema?
Allowable privilege types are
|
Does user have privilege for sequence?
Allowable privilege types are
|
Does user have privilege for foreign server?
The only allowable privilege type is |
Does user have privilege for table?
Allowable privilege types
are |
Does user have privilege for tablespace?
The only allowable privilege type is |
Does user have privilege for data type?
The only allowable privilege type is |
Does user have privilege for role?
Allowable privilege types are
|
Is row-level security active for the specified table in the context of the current user and current environment? |
Table 9.71 shows the operators
available for the aclitem
type, which is the catalog
representation of access privileges. See Section 5.8
for information about how to read access privilege values.
Table 9.71. aclitem
Operators
Table 9.72 shows some additional
functions to manage the aclitem
type.
Table 9.72. aclitem
Functions
Function Description |
---|
Constructs an |
Returns the |
Constructs an |
Table 9.73 shows functions that determine whether a certain object is visible in the current schema search path. For example, a table is said to be visible if its containing schema is in the search path and no table of the same name appears earlier in the search path. This is equivalent to the statement that the table can be referenced by name without explicit schema qualification. Thus, to list the names of all visible tables:
SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
For functions and operators, an object in the search path is said to be visible if there is no object of the same name and argument data type(s) earlier in the path. For operator classes and families, both the name and the associated index access method are considered.
Table 9.73. Schema Visibility Inquiry Functions
All these functions require object OIDs to identify the object to be
checked. If you want to test an object by name, it is convenient to use
the OID alias types (regclass
, regtype
,
regprocedure
, regoperator
, regconfig
,
or regdictionary
),
for example:
SELECT pg_type_is_visible('myschema.widget'::regtype);
Note that it would not make much sense to test a non-schema-qualified type name in this way — if the name can be recognized at all, it must be visible.
Table 9.74 lists functions that extract information from the system catalogs.
Table 9.74. System Catalog Information Functions
Function Description |
---|
Returns the SQL name for a data type that is identified by its type OID and possibly a type modifier. Pass NULL for the type modifier if no specific modifier is known. |
Returns the OID of the base type of a domain identified by its type OID. If the argument is the OID of a non-domain type, returns the argument as-is. Returns NULL if the argument is not a valid type OID. If there's a chain of domain dependencies, it will recurse until finding the base type.
Assuming
|
Converts the supplied encoding name into an integer representing the
internal identifier used in some system catalog tables.
Returns |
Converts the integer used as the internal identifier of an encoding in some system catalog tables into a human-readable string. Returns an empty string if an invalid encoding number is provided. |
Returns a set of records describing the foreign key relationships
that exist within the PostgreSQL system
catalogs.
The |
Reconstructs the creating command for a constraint. (This is a decompiled reconstruction, not the original text of the command.) |
Decompiles the internal form of an expression stored in the system catalogs, such as the default value for a column. If the expression might contain Vars, specify the OID of the relation they refer to as the second parameter; if no Vars are expected, passing zero is sufficient. |
Reconstructs the creating command for a function or procedure.
(This is a decompiled reconstruction, not the original text
of the command.)
The result is a complete |
Reconstructs the argument list of a function or procedure, in the form
it would need to appear in within |
Reconstructs the argument list necessary to identify a function or
procedure, in the form it would need to appear in within commands such
as |
Reconstructs the |
Reconstructs the creating command for an index.
(This is a decompiled reconstruction, not the original text
of the command.) If |
Returns a set of records describing the SQL keywords recognized by the
server. The |
Reconstructs the definition of a partitioned table's partition
key, in the form it would have in the |
Reconstructs the creating command for a rule. (This is a decompiled reconstruction, not the original text of the command.) |
Returns the name of the sequence associated with a column,
or NULL if no sequence is associated with the column.
If the column is an identity column, the associated sequence is the
sequence internally created for that column.
For columns created using one of the serial types
( A typical use is in reading the current value of the sequence for an identity or serial column, for example: SELECT currval(pg_get_serial_sequence('sometable', 'id'));
|
Reconstructs the creating command for an extended statistics object. (This is a decompiled reconstruction, not the original text of the command.) |
Reconstructs the creating command for a trigger. (This is a decompiled reconstruction, not the original text of the command.) |
Returns a role's name given its OID. |
Reconstructs the underlying |
Reconstructs the underlying |
Reconstructs the underlying |
Tests whether an index column has the named property.
Common index column properties are listed in
Table 9.75.
(Note that extension access methods can define additional property
names for their indexes.)
|
Tests whether an index has the named property.
Common index properties are listed in
Table 9.76.
(Note that extension access methods can define additional property
names for their indexes.)
|
Tests whether an index access method has the named property.
Access method properties are listed in
Table 9.77.
|
Returns the set of storage options represented by a value from
|
Returns an array of the flags associated with the given GUC, or
|
Returns the set of OIDs of databases that have objects stored in the
specified tablespace. If this function returns any rows, the
tablespace is not empty and cannot be dropped. To identify the specific
objects populating the tablespace, you will need to connect to the
database(s) identified by |
Returns the file system path that this tablespace is located in. |
Returns the OID of the data type of the value that is passed to it.
This can be helpful for troubleshooting or dynamically constructing
SQL queries. The function is declared as
returning
|
Returns the name of the collation of the value that is passed to it.
The value is quoted and schema-qualified if necessary. If no
collation was derived for the argument expression,
then
|
Translates a textual relation name to its OID. A similar result is
obtained by casting the string to type |
Translates a textual collation name to its OID. A similar result is
obtained by casting the string to type |
Translates a textual schema name to its OID. A similar result is
obtained by casting the string to type |
Translates a textual operator name to its OID. A similar result is
obtained by casting the string to type |
Translates a textual operator name (with parameter types) to its OID. A similar result is
obtained by casting the string to type |
Translates a textual function or procedure name to its OID. A similar result is
obtained by casting the string to type |
Translates a textual function or procedure name (with argument types) to its OID. A similar result is
obtained by casting the string to type |
Translates a textual role name to its OID. A similar result is
obtained by casting the string to type |
Parses a string of text, extracts a potential type name from it,
and translates that name into a type OID. A syntax error in the
string will result in an error; but if the string is a
syntactically valid type name that happens not to be found in the
catalogs, the result is |
Parses a string of text, extracts a potential type name from it,
and translates its type modifier, if any. A syntax error in the
string will result in an error; but if the string is a
syntactically valid type name that happens not to be found in the
catalogs, the result is
|
Most of the functions that reconstruct (decompile) database objects
have an optional pretty
flag, which
if true
causes the result to
be “pretty-printed”. Pretty-printing suppresses unnecessary
parentheses and adds whitespace for legibility.
The pretty-printed format is more readable, but the default format
is more likely to be interpreted the same way by future versions of
PostgreSQL; so avoid using pretty-printed output
for dump purposes. Passing false
for
the pretty
parameter yields the same result as
omitting the parameter.
Table 9.75. Index Column Properties
Name | Description |
---|---|
asc | Does the column sort in ascending order on a forward scan? |
desc | Does the column sort in descending order on a forward scan? |
nulls_first | Does the column sort with nulls first on a forward scan? |
nulls_last | Does the column sort with nulls last on a forward scan? |
orderable | Does the column possess any defined sort ordering? |
distance_orderable | Can the column be scanned in order by a “distance”
operator, for example ORDER BY col <-> constant ?
|
returnable | Can the column value be returned by an index-only scan? |
search_array | Does the column natively support col = ANY(array)
searches?
|
search_nulls | Does the column support IS NULL and
IS NOT NULL searches?
|
Table 9.76. Index Properties
Name | Description |
---|---|
clusterable | Can the index be used in a CLUSTER command?
|
index_scan | Does the index support plain (non-bitmap) scans? |
bitmap_scan | Does the index support bitmap scans? |
backward_scan | Can the scan direction be changed in mid-scan (to
support FETCH BACKWARD on a cursor without
needing materialization)?
|
Table 9.77. Index Access Method Properties
Name | Description |
---|---|
can_order | Does the access method support ASC ,
DESC and related keywords in
CREATE INDEX ?
|
can_unique | Does the access method support unique indexes? |
can_multi_col | Does the access method support indexes with multiple columns? |
can_exclude | Does the access method support exclusion constraints? |
can_include | Does the access method support the INCLUDE
clause of CREATE INDEX ?
|
Table 9.78. GUC Flags
Flag | Description |
---|---|
EXPLAIN | Parameters with this flag are included in
EXPLAIN (SETTINGS) commands.
|
NO_SHOW_ALL | Parameters with this flag are excluded from
SHOW ALL commands.
|
NO_RESET | Parameters with this flag do not support
RESET commands.
|
NO_RESET_ALL | Parameters with this flag are excluded from
RESET ALL commands.
|
NOT_IN_SAMPLE | Parameters with this flag are not included in
postgresql.conf by default.
|
RUNTIME_COMPUTED | Parameters with this flag are runtime-computed ones. |
Table 9.79 lists functions related to database object identification and addressing.
Table 9.79. Object Information and Addressing Functions
The functions shown in Table 9.80 extract comments previously stored with the COMMENT command. A null value is returned if no comment could be found for the specified parameters.
Table 9.80. Comment Information Functions
The functions shown in Table 9.81 can be helpful for checking validity of proposed input data.
Table 9.81. Data Validity Checking Functions
The functions shown in Table 9.82 provide server transaction information in an exportable form. The main use of these functions is to determine which transactions were committed between two snapshots.
Table 9.82. Transaction ID and Snapshot Information Functions
Function Description |
---|
Returns the current transaction's ID. It will assign a new one if the current transaction does not have one already (because it has not performed any database updates); see Section 68.1 for details. If executed in a subtransaction, this will return the top-level transaction ID; see Section 68.3 for details. |
Returns the current transaction's ID, or |
Reports the commit status of a recent transaction.
The result is one of |
Returns a current snapshot, a data structure showing which transaction IDs are now in-progress. Only top-level transaction IDs are included in the snapshot; subtransaction IDs are not shown; see Section 68.3 for details. |
Returns the set of in-progress transaction IDs contained in a snapshot. |
Returns the |
Returns the |
Is the given transaction ID visible according to this snapshot (that is, was it completed before the snapshot was taken)? Note that this function will not give the correct answer for a subtransaction ID (subxid); see Section 68.3 for details. |
The internal transaction ID type xid
is 32 bits wide and
wraps around every 4 billion transactions. However,
the functions shown in Table 9.82 use a
64-bit type xid8
that does not wrap around during the life
of an installation and can be converted to xid
by casting if
required; see Section 68.1 for details.
The data type pg_snapshot
stores information about
transaction ID visibility at a particular moment in time. Its components
are described in Table 9.83.
pg_snapshot
's textual representation is
.
For example xmin
:xmax
:xip_list
10:20:10,14,15
means
xmin=10, xmax=20, xip_list=10, 14, 15
.
Table 9.83. Snapshot Components
Name | Description |
---|---|
xmin |
Lowest transaction ID that was still active. All transaction IDs
less than xmin are either committed and visible,
or rolled back and dead.
|
xmax |
One past the highest completed transaction ID. All transaction IDs
greater than or equal to xmax had not yet
completed as of the time of the snapshot, and thus are invisible.
|
xip_list |
Transactions in progress at the time of the snapshot. A transaction
ID that is xmin <= and not in this list was already completed at the time
of the snapshot, and thus is either visible or dead according to its
commit status. This list does not include the transaction IDs of
subtransactions (subxids).
|
In releases of PostgreSQL before 13 there was
no xid8
type, so variants of these functions were provided
that used bigint
to represent a 64-bit XID, with a
correspondingly distinct snapshot data type txid_snapshot
.
These older functions have txid
in their names. They
are still supported for backward compatibility, but may be removed from a
future release. See Table 9.84.
Table 9.84. Deprecated Transaction ID and Snapshot Information Functions
The functions shown in Table 9.85 provide information about when past transactions were committed. They only provide useful data when the track_commit_timestamp configuration option is enabled, and only for transactions that were committed after it was enabled. Commit timestamp information is routinely removed during vacuum.
Table 9.85. Committed Transaction Information Functions
The functions shown in Table 9.86
print information initialized during initdb
, such
as the catalog version. They also show information about write-ahead
logging and checkpoint processing. This information is cluster-wide,
not specific to any one database. These functions provide most of the same
information, from the same source, as the
pg_controldata application.
Table 9.86. Control Data Functions
Function Description |
---|
Returns information about current checkpoint state, as shown in Table 9.87. |
Returns information about current control file state, as shown in Table 9.88. |
Returns information about cluster initialization state, as shown in Table 9.89. |
Returns information about recovery state, as shown in Table 9.90. |
Table 9.87. pg_control_checkpoint
Output Columns
Column Name | Data Type |
---|---|
checkpoint_lsn | pg_lsn |
redo_lsn | pg_lsn |
redo_wal_file | text |
timeline_id | integer |
prev_timeline_id | integer |
full_page_writes | boolean |
next_xid | text |
next_oid | oid |
next_multixact_id | xid |
next_multi_offset | xid |
oldest_xid | xid |
oldest_xid_dbid | oid |
oldest_active_xid | xid |
oldest_multi_xid | xid |
oldest_multi_dbid | oid |
oldest_commit_ts_xid | xid |
newest_commit_ts_xid | xid |
checkpoint_time | timestamp with time zone |
Table 9.88. pg_control_system
Output Columns
Column Name | Data Type |
---|---|
pg_control_version | integer |
catalog_version_no | integer |
system_identifier | bigint |
pg_control_last_modified | timestamp with time zone |
Table 9.89. pg_control_init
Output Columns
Column Name | Data Type |
---|---|
max_data_alignment | integer |
database_block_size | integer |
blocks_per_segment | integer |
wal_block_size | integer |
bytes_per_wal_segment | integer |
max_identifier_length | integer |
max_index_columns | integer |
max_toast_chunk_size | integer |
large_object_chunk_size | integer |
float8_pass_by_value | boolean |
data_page_checksum_version | integer |
Table 9.90. pg_control_recovery
Output Columns
Column Name | Data Type |
---|---|
min_recovery_end_lsn | pg_lsn |
min_recovery_end_timeline | integer |
backup_start_lsn | pg_lsn |
backup_end_lsn | pg_lsn |
end_of_backup_record_required | boolean |
The functions shown in Table 9.91 print version information.
Table 9.91. Version Information Functions
Function Description |
---|
Returns a string describing the PostgreSQL
server's version. You can also get this information from
server_version, or for a machine-readable
version use server_version_num. Software
developers should use |
Returns a string representing the version of Unicode used by PostgreSQL. |
Returns a string representing the version of Unicode used by ICU, if
the server was built with ICU support; otherwise returns
|
The functions shown in Table 9.92 print information about the status of WAL summarization. See summarize_wal.
Table 9.92. WAL Summarization Information Functions