9.26. System Information Functions and Operators
Table 9.65 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 28.2.2 for more information.
Table 9.65. 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
|
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 32
) 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. |
This is equivalent to
|
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
|
Note
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.66
lists functions that
allow querying object access privileges programmatically.
(See
Section 5.7
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.66. 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 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.67
shows the operators
available for the
aclitem
type, which is the catalog
representation of access privileges. See
Section 5.7
for information about how to read access privilege values.
Table 9.67.
aclitem
Operators
Table 9.68
shows some additional
functions to manage the
aclitem
type.
Table 9.68.
aclitem
Functions
Function Description |
---|
Constructs an
|
Returns the
|
Constructs an
|
Table 9.69 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.69. 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.70 lists functions that extract information from the system catalogs.
Table 9.70. 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 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 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.71
.
(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.72
.
(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.73
.
|
Returns the set of storage options represented by a value from
|
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
For example: SELECT pg_typeof(33); pg_typeof ----------- integer SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); typlen -------- 4
|
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
For example: SELECT collation for (description) FROM pg_description LIMIT 1; pg_collation_for ------------------ "default" SELECT collation for ('foo' COLLATE "de_DE"); pg_collation_for ------------------ "de_DE"
|
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
|
Translates a textual type name to its OID. A similar result is
obtained by casting the string to type
|
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.71. 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.72. 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.73. 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.74 lists functions related to database object identification and addressing.
Table 9.74. Object Information and Addressing Functions
The functions shown in Table 9.75 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.75. Comment Information Functions
The functions shown in Table 9.76 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.76. 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). |
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. |
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. |
The internal transaction ID type
xid
is 32 bits wide and
wraps around every 4 billion transactions. However,
the functions shown in
Table 9.76
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. The data type
pg_snapshot
stores information about
transaction ID visibility at a particular moment in time. Its components
are described in
Table 9.77
.
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.77. 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.
|
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.78
.
Table 9.78. Deprecated Transaction ID and Snapshot Information Functions
The functions shown in Table 9.79 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.
Table 9.79. Committed Transaction Information Functions
The functions shown in
Table 9.80
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.80. Control Data Functions
Function Description |
---|
Returns information about current checkpoint state, as shown in Table 9.81 . |
Returns information about current control file state, as shown in Table 9.82 . |
Returns information about cluster initialization state, as shown in Table 9.83 . |
Returns information about recovery state, as shown in Table 9.84 . |
Table 9.81.
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.82.
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.83.
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.84.
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
|