System Information Functions
PostgreSQL 9.5.4 Documentation | |||
---|---|---|---|
Prev | Up | Chapter 9. Functions and Operators | Next |
Table 9-57 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.2 for more information.
Table 9-57. Session Information Functions
Name | Return Type | Description |
---|---|---|
current_catalog
|
name | name of current database (called "catalog" in the SQL standard) |
current_database()
|
name | name of current database |
current_query()
|
text | text of the currently executing query, as submitted by the client (might contain more than one statement) |
current_schema
[()]
|
name | name of current schema |
current_schemas(
boolean
)
|
name[] | names of schemas in search path, optionally including implicit schemas |
current_user
|
name | user name of current execution context |
inet_client_addr()
|
inet | address of the remote connection |
inet_client_port()
|
int | port of the remote connection |
inet_server_addr()
|
inet | address of the local connection |
inet_server_port()
|
int | port of the local connection |
pg_backend_pid()
|
int | Process ID of the server process attached to the current session |
pg_conf_load_time()
|
timestamp with time zone | configuration load time |
pg_is_other_temp_schema(
oid
)
|
boolean | is schema another session's temporary schema? |
pg_listening_channels()
|
setof text | channel names that the session is currently listening on |
pg_my_temp_schema()
|
oid | OID of session's temporary schema, or 0 if none |
pg_postmaster_start_time()
|
timestamp with time zone | server start time |
pg_trigger_depth()
|
int | current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger) |
session_user
|
name | session user name |
user
|
name |
equivalent to
current_user
|
version()
|
text | PostgreSQL version information. See also server_version_num for a machine-readable version. |
Note:
current_catalog
,current_schema
,current_user
,session_user
, anduser
have special syntactic status in SQL : they must be called without trailing parentheses. (In PostgreSQL, parentheses can optionally be used withcurrent_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_schema
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.
current_schemas(boolean)
returns an array of the names of all
schemas presently in the search path. The Boolean option determines whether or not
implicitly included system schemas such as
pg_catalog
are included in the
returned search path.
Note: The search path can be altered at run time. The command is:
SET search_path TO schema [, schema, ...]
pg_listening_channels
returns a set of names of
channels that the current session is listening to. See
LISTEN
for more information.
inet_client_addr
returns the IP address of the
current client, and
inet_client_port
returns the
port number.
inet_server_addr
returns the IP address on which
the server accepted the current connection, and
inet_server_port
returns the port number.
All these functions return NULL if the current connection is via a
Unix-domain socket.
pg_my_temp_schema
returns the OID of the current
session's temporary schema, or zero if it has none (because it has not
created any temporary tables).
pg_is_other_temp_schema
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.)
pg_postmaster_start_time
returns the
timestamp with time zone
when the
server started.
pg_conf_load_time
returns the
timestamp with time zone
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.)
version
returns a string describing the
PostgreSQL
server's version. You can also
get this information from
server_version
or
for a machine-readable version,
server_version_num
.
Software developers should use
server_version_num
(available since 8.2) or
PQserverVersion
instead
of parsing the text version.
Table 9-58 lists functions that allow the user to query object access privileges programmatically. See Section 5.6 for more information about privileges.
Table 9-58. Access Privilege Inquiry Functions
Name | Return Type | Description |
---|---|---|
has_any_column_privilege
(
user
,
table
,
privilege
)
|
boolean | does user have privilege for any column of table |
has_any_column_privilege
(
table
,
privilege
)
|
boolean | does current user have privilege for any column of table |
has_column_privilege
(
user
,
table
,
column
,
privilege
)
|
boolean | does user have privilege for column |
has_column_privilege
(
table
,
column
,
privilege
)
|
boolean | does current user have privilege for column |
has_database_privilege
(
user
,
database
,
privilege
)
|
boolean | does user have privilege for database |
has_database_privilege
(
database
,
privilege
)
|
boolean | does current user have privilege for database |
has_foreign_data_wrapper_privilege
(
user
,
fdw
,
privilege
)
|
boolean | does user have privilege for foreign-data wrapper |
has_foreign_data_wrapper_privilege
(
fdw
,
privilege
)
|
boolean | does current user have privilege for foreign-data wrapper |
has_function_privilege
(
user
,
function
,
privilege
)
|
boolean | does user have privilege for function |
has_function_privilege
(
function
,
privilege
)
|
boolean | does current user have privilege for function |
has_language_privilege
(
user
,
language
,
privilege
)
|
boolean | does user have privilege for language |
has_language_privilege
(
language
,
privilege
)
|
boolean | does current user have privilege for language |
has_schema_privilege
(
user
,
schema
,
privilege
)
|
boolean | does user have privilege for schema |
has_schema_privilege
(
schema
,
privilege
)
|
boolean | does current user have privilege for schema |
has_sequence_privilege
(
user
,
sequence
,
privilege
)
|
boolean | does user have privilege for sequence |
has_sequence_privilege
(
sequence
,
privilege
)
|
boolean | does current user have privilege for sequence |
has_server_privilege
(
user
,
server
,
privilege
)
|
boolean | does user have privilege for foreign server |
has_server_privilege
(
server
,
privilege
)
|
boolean | does current user have privilege for foreign server |
has_table_privilege
(
user
,
table
,
privilege
)
|
boolean | does user have privilege for table |
has_table_privilege
(
table
,
privilege
)
|
boolean | does current user have privilege for table |
has_tablespace_privilege
(
user
,
tablespace
,
privilege
)
|
boolean | does user have privilege for tablespace |
has_tablespace_privilege
(
tablespace
,
privilege
)
|
boolean | does current user have privilege for tablespace |
pg_has_role
(
user
,
role
,
privilege
)
|
boolean | does user have privilege for role |
pg_has_role
(
role
,
privilege
)
|
boolean | does current user have privilege for role |
row_security_active
(
table
)
|
boolean | does current user have row level security active for table |
has_table_privilege
checks whether a user
can access a table in a particular way. The user can be
specified by name, by OID (
pg_authid.oid
),
public
to indicate the PUBLIC pseudo-role, or if the argument is
omitted
current_user
is assumed. The table can be specified
by name or by OID. (Thus, there are actually six variants of
has_table_privilege
, which can be distinguished by
the number and types of their arguments.) When specifying by name,
the name can be schema-qualified if necessary.
The desired access privilege type
is specified by a text string, which must evaluate to one of the
values
SELECT
,
INSERT
,
UPDATE
,
DELETE
,
TRUNCATE
,
REFERENCES
, or
TRIGGER
. 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');
has_sequence_privilege
checks whether a user
can access a sequence in a particular way. The possibilities for its
arguments are analogous to
has_table_privilege
.
The desired access privilege type must evaluate to one of
USAGE
,
SELECT
, or
UPDATE
.
has_any_column_privilege
checks whether a user can
access any column of a table in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
,
except that the desired access privilege type must evaluate to some
combination of
SELECT
,
INSERT
,
UPDATE
, or
REFERENCES
. Note that having any of these privileges
at the table level implicitly grants it for each column of the table,
so
has_any_column_privilege
will always return
true
if
has_table_privilege
does for the same
arguments. But
has_any_column_privilege
also succeeds if
there is a column-level grant of the privilege for at least one column.
has_column_privilege
checks whether a user
can access a column in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
,
with the addition that the column can be specified either by name
or attribute number.
The desired access privilege type must evaluate to some combination of
SELECT
,
INSERT
,
UPDATE
, or
REFERENCES
. Note that having any of these privileges
at the table level implicitly grants it for each column of the table.
has_database_privilege
checks whether a user
can access a database in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
.
The desired access privilege type must evaluate to some combination of
CREATE
,
CONNECT
,
TEMPORARY
, or
TEMP
(which is equivalent to
TEMPORARY
).
has_function_privilege
checks whether a user
can access a function in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
.
When specifying a function by a text string rather than by OID,
the allowed input is the same as for the
regprocedure
data type
(see
Section 8.18
).
The desired access privilege type must evaluate to
EXECUTE
.
An example is:
SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
has_foreign_data_wrapper_privilege
checks whether a user
can access a foreign-data wrapper in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
.
The desired access privilege type must evaluate to
USAGE
.
has_language_privilege
checks whether a user
can access a procedural language in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
.
The desired access privilege type must evaluate to
USAGE
.
has_schema_privilege
checks whether a user
can access a schema in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
.
The desired access privilege type must evaluate to some combination of
CREATE
or
USAGE
.
has_server_privilege
checks whether a user
can access a foreign server in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
.
The desired access privilege type must evaluate to
USAGE
.
has_tablespace_privilege
checks whether a user
can access a tablespace in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
.
The desired access privilege type must evaluate to
CREATE
.
pg_has_role
checks whether a user
can access a role in a particular way.
Its argument possibilities
are analogous to
has_table_privilege
,
except that
public
is not allowed as a user name.
The desired access privilege type must evaluate to some combination of
MEMBER
or
USAGE
.
MEMBER
denotes direct or indirect membership in
the role (that is, the right to do
SET ROLE
), while
USAGE
denotes whether the privileges of the role
are immediately available without doing
SET ROLE
.
row_security_active
checks whether row level
security is active for the specified table in the context of the
current_user
and environment. The table can
be specified by name or by OID.
Table 9-59 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. To list the names of all visible tables:
SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
Table 9-59. Schema Visibility Inquiry Functions
Name | Return Type | Description |
---|---|---|
pg_collation_is_visible(
collation_oid
)
|
boolean | is collation visible in search path |
pg_conversion_is_visible(
conversion_oid
)
|
boolean | is conversion visible in search path |
pg_function_is_visible(
function_oid
)
|
boolean | is function visible in search path |
pg_opclass_is_visible(
opclass_oid
)
|
boolean | is operator class visible in search path |
pg_operator_is_visible(
operator_oid
)
|
boolean | is operator visible in search path |
pg_opfamily_is_visible(
opclass_oid
)
|
boolean | is operator family visible in search path |
pg_table_is_visible(
table_oid
)
|
boolean | is table visible in search path |
pg_ts_config_is_visible(
config_oid
)
|
boolean | is text search configuration visible in search path |
pg_ts_dict_is_visible(
dict_oid
)
|
boolean | is text search dictionary visible in search path |
pg_ts_parser_is_visible(
parser_oid
)
|
boolean | is text search parser visible in search path |
pg_ts_template_is_visible(
template_oid
)
|
boolean | is text search template visible in search path |
pg_type_is_visible(
type_oid
)
|
boolean | is type (or domain) visible in search path |
Each function performs the visibility check for one type of database
object. Note that
pg_table_is_visible
can also be used
with views, materialized views, indexes, sequences and foreign tables;
pg_type_is_visible
can also be used with domains.
For functions and operators, an object in
the search path is visible if there is no object of the same name
and argument data type(s)
earlier in the path. For operator
classes, both name and associated index access method are considered.
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-60 lists functions that extract information from the system catalogs.
Table 9-60. System Catalog Information Functions
Name | Return Type | Description |
---|---|---|
format_type(
type_oid
,
typemod
)
|
text | get SQL name of a data type |
pg_get_constraintdef(
constraint_oid
)
|
text | get definition of a constraint |
pg_get_constraintdef(
constraint_oid
,
pretty_bool
)
|
text | get definition of a constraint |
pg_get_expr(
pg_node_tree
,
relation_oid
)
|
text | decompile internal form of an expression, assuming that any Vars in it refer to the relation indicated by the second parameter |
pg_get_expr(
pg_node_tree
,
relation_oid
,
pretty_bool
)
|
text | decompile internal form of an expression, assuming that any Vars in it refer to the relation indicated by the second parameter |
pg_get_functiondef(
func_oid
)
|
text | get definition of a function |
pg_get_function_arguments(
func_oid
)
|
text | get argument list of function's definition (with default values) |
pg_get_function_identity_arguments(
func_oid
)
|
text | get argument list to identify a function (without default values) |
pg_get_function_result(
func_oid
)
|
text | get RETURNS clause for function |
pg_get_indexdef(
index_oid
)
|
text | get CREATE INDEX command for index |
pg_get_indexdef(
index_oid
,
column_no
,
pretty_bool
)
|
text | get CREATE INDEX command for index, or definition of just one index column when column_no is not zero |
pg_get_keywords()
|
setof record | get list of SQL keywords and their categories |
pg_get_ruledef(
rule_oid
)
|
text | get CREATE RULE command for rule |
pg_get_ruledef(
rule_oid
,
pretty_bool
)
|
text | get CREATE RULE command for rule |
pg_get_serial_sequence(
table_name
,
column_name
)
|
text | get name of the sequence that a serial , smallserial or bigserial column uses |
pg_get_triggerdef
(
trigger_oid
)
|
text | get CREATE [ CONSTRAINT ] TRIGGER command for trigger |
pg_get_triggerdef
(
trigger_oid
,
pretty_bool
)
|
text | get CREATE [ CONSTRAINT ] TRIGGER command for trigger |
pg_get_userbyid(
role_oid
)
|
name | get role name with given OID |
pg_get_viewdef(
view_name
)
|
text | get underlying SELECT command for view or materialized view ( deprecated ) |
pg_get_viewdef(
view_name
,
pretty_bool
)
|
text | get underlying SELECT command for view or materialized view ( deprecated ) |
pg_get_viewdef(
view_oid
)
|
text | get underlying SELECT command for view or materialized view |
pg_get_viewdef(
view_oid
,
pretty_bool
)
|
text | get underlying SELECT command for view or materialized view |
pg_get_viewdef(
view_oid
,
wrap_column_int
)
|
text | get underlying SELECT command for view or materialized view; lines with fields are wrapped to specified number of columns, pretty-printing is implied |
pg_options_to_table(
reloptions
)
|
setof record | get the set of storage option name/value pairs |
pg_tablespace_databases(
tablespace_oid
)
|
setof oid | get the set of database OIDs that have objects in the tablespace |
pg_tablespace_location(
tablespace_oid
)
|
text | get the path in the file system that this tablespace is located in |
pg_typeof(
any
)
|
regtype | get the data type of any value |
collation for (
any
)
|
text | get the collation of the argument |
to_regclass(
rel_name
)
|
regclass | get the OID of the named relation |
to_regproc(
func_name
)
|
regproc | get the OID of the named function |
to_regprocedure(
func_name
)
|
regprocedure | get the OID of the named function |
to_regoper(
operator_name
)
|
regoper | get the OID of the named operator |
to_regoperator(
operator_name
)
|
regoperator | get the OID of the named operator |
to_regtype(
type_name
)
|
regtype | get the OID of the named type |
to_regnamespace(
schema_name
)
|
regnamespace | get the OID of the named schema |
to_regrole(
role_name
)
|
regrole | get the OID of the named role |
format_type
returns the SQL name of 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.
pg_get_keywords
returns a set of records describing
the SQL keywords recognized by the server. The
word
column
contains the keyword. The
catcode
column contains a
category code:
U
for unreserved,
C
for column name,
T
for type or function name, or
R
for reserved.
The
catdesc
column contains a possibly-localized string
describing the category.
pg_get_constraintdef
,
pg_get_indexdef
,
pg_get_ruledef
,
and
pg_get_triggerdef
, respectively reconstruct the
creating command for a constraint, index, rule, or trigger. (Note that this
is a decompiled reconstruction, not the original text of the command.)
pg_get_expr
decompiles the internal form of an
individual expression, such as the default value for a column. It can be
useful when examining the contents of system catalogs. If the expression
might contain Vars, specify the OID of the relation they refer to as the
second parameter; if no Vars are expected, zero is sufficient.
pg_get_viewdef
reconstructs the
SELECT
query that defines a view. Most of these functions come in two variants,
one of which can optionally
"pretty-print"
the result. 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
; avoid using pretty-printed output for dump
purposes. Passing
false
for the pretty-print parameter yields
the same result as the variant that does not have the parameter at all.
pg_get_functiondef
returns a complete
CREATE OR REPLACE FUNCTION
statement for a function.
pg_get_function_arguments
returns the argument list
of a function, in the form it would need to appear in within
CREATE FUNCTION
.
pg_get_function_result
similarly returns the
appropriate
RETURNS
clause for the function.
pg_get_function_identity_arguments
returns the
argument list necessary to identify a function, in the form it
would need to appear in within
ALTER FUNCTION
, for
instance. This form omits default values.
pg_get_serial_sequence
returns the name of the
sequence associated with a column, or NULL if no sequence is associated
with the column. The first input parameter is a table name with
optional schema, and the second parameter is a column name. Because
the first parameter is potentially a schema and table, it is not treated
as a double-quoted identifier, meaning it is lower cased by default,
while the second parameter, being just a column name, is treated as
double-quoted and has its case preserved. The function returns a value
suitably formatted for passing to sequence functions (see
Section 9.16
). This association can be modified or
removed with
ALTER SEQUENCE OWNED BY
. (The function
probably should have been called
pg_get_owned_sequence
; its current name reflects the fact
that it's typically used with
serial
or
bigserial
columns.)
pg_get_userbyid
extracts a role's name given
its OID.
pg_options_to_table
returns the set of storage
option name/value pairs
(
option_name
/
option_value
) when passed
pg_class
.
reloptions
or
pg_attribute
.
attoptions
.
pg_tablespace_databases
allows a tablespace to be
examined. It returns the set of OIDs of databases that have objects stored
in the tablespace. If this function returns any rows, the tablespace is not
empty and cannot be dropped. To display the specific objects populating the
tablespace, you will need to connect to the databases identified by
pg_tablespace_databases
and query their
pg_class
catalogs.
pg_typeof
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
regtype
, which is an OID alias type (see
Section 8.18
); this means that it is the same as an
OID for comparison purposes but displays as a type name. For example:
SELECT pg_typeof(33); pg_typeof ----------- integer (1 row) SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); typlen -------- 4 (1 row)
The expression collation for returns the collation of the value that is passed to it. Example:
SELECT collation for (description) FROM pg_description LIMIT 1; pg_collation_for ------------------ "default" (1 row) SELECT collation for ('foo' COLLATE "de_DE"); pg_collation_for ------------------ "de_DE" (1 row)
The value might be quoted and schema-qualified. If no collation is derived for the argument expression, then a null value is returned. If the argument is not of a collatable data type, then an error is raised.
The
to_regclass
,
to_regproc
,
to_regprocedure
,
to_regoper
,
to_regoperator
,
to_regtype
,
to_regnamespace
, and
to_regrole
functions translate relation, function, operator, type, schema, and role
names to objects of
type
regclass
,
regproc
,
regprocedure
,
regoper
,
regoperator
,
regtype
,
regnamespace
, and
regrole
respectively. These functions differ from a cast from
text in that they don't accept a numeric OID, and that they return null
rather than throwing an error if the name is not found (or, for
to_regproc
and
to_regoper
, if
the given name matches multiple objects).
Table 9-61 lists functions related to database object identification and addressing.
Table 9-61. Object Information and Addressing Functions
Name | Return Type | Description |
---|---|---|
pg_describe_object(
catalog_id
,
object_id
,
object_sub_id
)
|
text | get description of a database object |
pg_identify_object(
catalog_id
oid
,
object_id
oid
,
object_sub_id
integer
)
|
type text , schema text , name text , identity text | get identity of a database object |
pg_identify_object_as_address(
catalog_id
oid
,
object_id
oid
,
object_sub_id
integer
)
|
type text , name text[] , args text[] | get external representation of a database object's address |
pg_get_object_address(
type
text
,
name
text[]
,
args
text[]
)
|
catalog_id oid , object_id oid , object_sub_id int32 | get address of a database object, from its external representation |
pg_describe_object
returns a textual description of a database
object specified by catalog OID, object OID and a (possibly zero) sub-object ID.
This description is intended to be human-readable, and might be translated,
depending on server configuration.
This is useful to determine the identity of an object as stored in the
pg_depend
catalog.
pg_identify_object
returns a row containing enough information
to uniquely identify the database object specified by catalog OID, object OID and a
(possibly zero) sub-object ID. This information is intended to be machine-readable,
and is never translated.
type
identifies the type of database object;
schema
is the schema name that the object belongs in, or
NULL
for object types that do not belong to schemas;
name
is the name of the object, quoted if necessary, only
present if it can be used (alongside schema name, if pertinent) as a unique
identifier of the object, otherwise
NULL
;
identity
is the complete object identity, with the precise format
depending on object type, and each part within the format being
schema-qualified and quoted as necessary.
pg_identify_object_as_address
returns a row containing
enough information to uniquely identify the database object specified by
catalog OID, object OID and a (possibly zero) sub-object ID. The returned
information is independent of the current server, that is, it could be used
to identify an identically named object in another server.
type
identifies the type of database object;
name
and
args
are text arrays that together
form a reference to the object. These three columns can be passed to
pg_get_object_address
to obtain the internal address
of the object.
This function is the inverse of
pg_get_object_address
.
pg_get_object_address
returns a row containing enough
information to uniquely identify the database object specified by its
type and object name and argument arrays. The returned values are the
ones that would be used in system catalogs such as
pg_depend
and can be passed to other system functions such as
pg_identify_object
or
pg_describe_object
.
catalog_id
is the OID of the system catalog containing the
object;
object_id
is the OID of the object itself, and
object_sub_id
is the object sub-ID, or zero if none.
This function is the inverse of
pg_identify_object_as_address
.
The functions shown in Table 9-62 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-62. Comment Information Functions
Name | Return Type | Description |
---|---|---|
col_description(
table_oid
,
column_number
)
|
text | get comment for a table column |
obj_description(
object_oid
,
catalog_name
)
|
text | get comment for a database object |
obj_description(
object_oid
)
|
text | get comment for a database object ( deprecated ) |
shobj_description(
object_oid
,
catalog_name
)
|
text | get comment for a shared database object |
col_description
returns the comment for a table
column, which is specified by the OID of its table and its column number.
(
obj_description
cannot be used for table columns
since columns do not have OIDs of their own.)
The two-parameter form of
obj_description
returns the
comment for a database object specified by its OID and the name of the
containing system catalog. For example,
obj_description(123456,'pg_class')
would retrieve the comment for the table with OID 123456.
The one-parameter form of
obj_description
requires only
the object OID. It is deprecated since there is no guarantee that
OIDs are unique across different system catalogs; therefore, the wrong
comment might be returned.
shobj_description
is used just like
obj_description
except it is used for retrieving
comments on shared objects. Some system catalogs are global to all
databases within each cluster, and the descriptions for objects in them
are stored globally as well.
The functions shown in Table 9-63 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-63. Transaction IDs and Snapshots
Name | Return Type | Description |
---|---|---|
txid_current()
|
bigint | get current transaction ID, assigning a new one if the current transaction does not have one |
txid_current_snapshot()
|
txid_snapshot | get current snapshot |
txid_snapshot_xip(
txid_snapshot
)
|
setof bigint | get in-progress transaction IDs in snapshot |
txid_snapshot_xmax(
txid_snapshot
)
|
bigint | get xmax of snapshot |
txid_snapshot_xmin(
txid_snapshot
)
|
bigint | get xmin of snapshot |
txid_visible_in_snapshot(
bigint
,
txid_snapshot
)
|
boolean | is transaction ID visible in snapshot? (do not use with subtransaction ids) |
The internal transaction ID type ( xid ) is 32 bits wide and wraps around every 4 billion transactions. However, these functions export a 64-bit format that is extended with an "epoch" counter so it will not wrap around during the life of an installation. The data type used by these functions, txid_snapshot , stores information about transaction ID visibility at a particular moment in time. Its components are described in Table 9-64 .
Table 9-64. Snapshot Components
Name | Description |
---|---|
xmin | Earliest transaction ID (txid) that is still active. All earlier transactions will either be committed and visible, or rolled back and dead. |
xmax | First as-yet-unassigned txid. All txids greater than or equal to this are not yet started as of the time of the snapshot, and thus invisible. |
xip_list | Active txids at the time of the snapshot. The list includes only those active txids between xmin and xmax ; there might be active txids higher than xmax . A txid that is xmin <= txid < xmax and not in this list was already completed at the time of the snapshot, and thus either visible or dead according to its commit status. The list does not include txids of subtransactions. |
txid_snapshot 's textual representation is xmin : xmax : xip_list . For example 10:20:10,14,15 means xmin=10, xmax=20, xip_list=10, 14, 15 .
The functions shown in Table 9-65 provide information about transactions that have been already committed. These functions mainly provide information about when the transactions were committed. They only provide useful data when track_commit_timestamp configuration option is enabled and only for transactions that were committed after it was enabled.
Table 9-65. Committed transaction information
Name | Return Type | Description |
---|---|---|
pg_xact_commit_timestamp(
xid
)
|
timestamp with time zone | get commit timestamp of a transaction |
pg_last_committed_xact()
|
xid xid , timestamp timestamp with time zone | get transaction ID and commit timestamp of latest committed transaction |