Session Information Functions
SYS_CONTEXT()
Description: Returns the value of the parameter associated with the context namespace at the current time. This function has the same function as USERENV() when the first input parameter is userenv.
Currently, the following parameters can be used as the second input parameter.
'current_edition_name', 'current_schema', 'current_sql', 'current_user', 'current_userid', 'database_role', 'ip_address', 'is_apply_server', 'isdba', 'nls_currency', 'nls_date_format', 'nls_date_language', 'server_host', 'service_name', 'session_user', 'session_userid', 'db_name', 'db_unique_name', 'instance', 'instance_name', 'current_edition_id', 'current_schemaid', 'lang', 'language', and 'sid'
Return type: text
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT SYS_CONTEXT('userenv','NLS_CURRENCY'); sys_context ------------- $ (1 row) SELECT SYS_CONTEXT('userenv','NLS_DATE_FORMAT'); sys_context --------------- ISO, MDY (1 row) SELECT SYS_CONTEXT('userenv','NLS_DATE_LANGUAGE'); sys_context ------------- en_US.UTF-8 (1 row) |
USERENV()
Description: Returns the value of the parameter associated with the context namespace at the current time. This function has the same function as SYS_CONTEXT() when the first input parameter is userenv.
Currently, the following input parameters are supported:
'current_edition_name', 'current_schema', 'current_sql', 'current_user', 'current_userid', 'database_role', 'ip_address', 'is_apply_server', 'isdba', 'nls_currency', 'nls_date_format', 'nls_date_language', 'server_host', 'service_name', 'session_user', 'session_userid', 'db_name', 'db_unique_name', 'instance', 'instance_name', 'current_edition_id', 'current_schemaid', 'lang', 'language', and 'sid'
Currently, the following input parameters are not supported:
'action', 'is_application_root', 'is_application_pdb', 'audited_cursorid', 'authenticated_identity', 'authentication_data', 'authentication_method', 'cdb_domain', 'cdb_name', 'client_identifier', 'con_id', 'con_name', 'current_sql_length', 'db_domain', 'db_supplemental_log_level', 'dblink_info', 'drain_status', 'entryid', 'enterprise_identity', 'fg_job_id', 'global_uid', 'identification_type', 'is_dg_rolling_upgrade', 'ldap_server_type', 'module', 'network_protocol', 'nls_calendar', 'nls_sort', 'nls_territory', 'oracle_home', 'os_user', 'platform_slash', 'policy_invoker', 'proxy_enterprise_identity', 'proxy_user', 'proxy_userid', 'scheduler_job', 'session_edition_id', 'session_edition_name', 'sessionid', 'statementid', 'terminal', 'unified_audit_sessionid', 'session_default_collation', 'client_info', 'bg_job_id', 'client_program_name', 'current_bind', 'global_context_memory', 'host', and 'current_sqln'
Return type: text
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT USERENV('sid'); userenv --------- 955 (1 row) SELECT USERENV('instance'); userenv --------- 14524 (1 row) SELECT USERENV('action'); userenv --------- (1 row) SELECT USERENV('actijj'); ERROR: unrecognized configuration parameter "actijj" CONTEXT: PL/pgSQL function userenv(text) line 114 at RETURN referenced column: userenv |
If the input parameter is not supported, NULL is returned. If the input parameter is invalid, an error is reported.
current_catalog()
Description: Name of the current database (called "catalog" in the SQL standard)
Return type: name
Example:
1 2 3 4 5 |
testdb=# SELECT current_catalog; current_database ------------------ testdb (1 row) |
current_database()
Description: Name of the current database
Return type: name
Example:
1 2 3 4 5 |
testdb=# SELECT current_database(); current_database ------------------ testdb (1 row) |
current_query()
Description: Text of the currently executing query committed by the client (which might contain more than one statement)
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT current_query(); current_query ------------------------- SELECT current_query(); (1 row) |
current_schema[()]
Description: Name of the current schema
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT current_schema(); current_schema ---------------- public (1 row) |
Note: current_schema returns the first valid schema name in the search path. (If the search path is empty or contains no valid schema name, NULL is returned.) 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)
Description: Name of a schema in the search path
Return type: name[]
Example:
1 2 3 4 5 |
gaussdb=# SELECT current_schemas(true); current_schemas --------------------- {pg_catalog,public} (1 row) |
Note:
current_schemas(Boolean) returns an array of the names of all schemas in the search path. The Boolean option specifies whether implicitly included system schemas such as pg_catalog are included in the returned search path.
The search path can be altered at the run time. The command is as follows:
1
|
SET search_path TO schema [, schema, ...] |
database()
Description: Returns the name of the current schema.
Parameter: none
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT database(); database ---------- public (1 row) |
This function is valid in a MySQL-compatible database when the GUC parameters b_format_version and b_format_dev_version are set to '5.7' and '1', respectively.
current_user()
Description: Username of current execution context
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT current_user; current_user -------------- omm (1 row) |
Note: current_user is the user identifier used for permission check. Generally, it indicates a session user, which can be modified by SET ROLE. During function execution, it is affected by the SECURITY DEFINER attribute.
definer_current_user
Description: Username of current execution context
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT definer_current_user(); definer_current_user ---------------------- omm (1 row) |
Note: Generally, the results of definer_current_user and current_user are the same. However, when this function is executed in a stored procedure, the name of user who defines the current stored procedure is returned.
pg_current_sessionid()
Description: Session ID in the current execution environment.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_current_sessionid(); pg_current_sessionid ---------------------------- 1579228402.140190434944768 (1 row) |
Note: pg_current_sessionid() is used to obtain the session ID in the current execution environment. The format of the value is Timestamp.Session ID. When enable_thread_pool is disabled, the actual session ID is the thread ID.
pg_current_sessid()
Description: Session ID in the current execution environment.
Return type: text
Example:
gaussdb=# select pg_current_sessid();
pg_current_sessid
-------------------
140308875015936
(1 row)
Note: In thread pool mode, the session ID of the current session is obtained. In non-thread pool mode, the backend thread ID of the current session is obtained.
pg_current_userid()
Description: Current user ID
Return type: text
Example:
gaussdb=# SELECT pg_current_userid();
pg_current_userid
-------------------
10
(1 row)
tablespace_oid_name(oid)
Description: Queries the tablespace name based on the tablespace OID.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# select tablespace_oid_name(1663); tablespace_oid_name --------------------- pg_default (1 row) |
inet_client_addr()
Description: Remote connection address. inet_client_addr returns the IP address of the current client.
It is available only in remote connection mode.
Return type: inet
Example:
1 2 3 4 5 |
gaussdb=# SELECT inet_client_addr(); inet_client_addr ------------------ 10.10.0.50 (1 row) |
inet_client_port()
Description: Remote connection port. inet_client_port returns the port number of the current client.
This function is valid only in remote connection mode.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT inet_client_port(); inet_client_port ------------------ 33143 (1 row) |
inet_server_addr()
Description: Local connection address. inet_server_addr returns the IP address on which the server accepts the current connection.
It is available only in remote connection mode.
Return type: inet
Example:
1 2 3 4 5 |
gaussdb=# SELECT inet_server_addr(); inet_server_addr ------------------ 10.10.0.13 (1 row) |
inet_server_port()
Description: Local connection port. inet_server_port returns the number of the port receiving the current connection. All these functions return NULL if the current connection is via a UDS.
This function is valid only in remote connection mode.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT inet_server_port(); inet_server_port ------------------ 8000 (1 row) |
pg_backend_pid()
Description: Thread ID of the worker thread connected to the current session.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_backend_pid(); pg_backend_pid ----------------- 140229352617744 (1 row) |
pg_conf_load_time()
Description: Configures load time. pg_conf_load_time returns the timestamp when the server configuration files were last loaded.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_conf_load_time(); pg_conf_load_time ------------------------------ 2017-09-01 16:05:23.89868+08 (1 row) |
pg_my_temp_schema()
Description: OID of the temporary schema of a session. The value is 0 if the OID does not exist.
Return type: OID
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_my_temp_schema(); pg_my_temp_schema ------------------- 0 (1 row) |
Note: pg_my_temp_schema returns the OID of the current session's temporary schema, or 0 if it has no temporary schemas (because no temporary tables are created). pg_is_other_temp_schema returns true if the given OID is the OID of another session's temporary schema.
pg_is_other_temp_schema(oid)
Description: Specifies whether the schema is the temporary schema of another session.
Return type: Boolean
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_is_other_temp_schema(25356); pg_is_other_temp_schema ------------------------- f (1 row) |
pg_listening_channels()
Description: Name of the channel that the session is currently listening to.
Return type: setof text
Example:
1 2 3 4 |
gaussdb=# SELECT pg_listening_channels(); pg_listening_channels ----------------------- (0 rows) |
Note: pg_listening_channels returns a set of names of channels that the current session is currently listening to.
pg_postmaster_start_time()
Description: Server start time. pg_postmaster_start_time returns the timestamp with time zone when the server is started.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_postmaster_start_time(); pg_postmaster_start_time ------------------------------ 2017-08-30 16:02:54.99854+08 (1 row) |
sessionid2pid()
Description: Obtains PID information from a session ID (for example, the sessid column in pv_session_stat).
Return type: int8
Example:
1 2 3 4 5 6 |
gaussdb=# select sessionid2pid(sessid::cstring) from pv_session_stat limit 2; sessionid2pid ----------------- 139973107902208 139973107902208 (2 rows) |
session_context( 'namespace' , 'parameter')
Description: Obtains and returns the parameter values of a specified namespace.
Return type: VARCHAR
Example:
1 2 3 4 5 |
gaussdb=# SELECT session_context('USERENV', 'CURRENT_SCHEMA'); session_context ------------- public (1 row) |
The result varies according to the current actual schema.
Note: Currently, only the SESSION_CONTEXT('USERENV', 'CURRENT_SCHEMA') and SESSION_CONTEXT('USERENV', 'CURRENT_USER') formats are supported.
pg_trigger_depth()
Description: Nesting level of triggers.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_trigger_depth(); pg_trigger_depth ------------------ 0 (1 row) |
opengauss_version()
Description: Referenced openGauss kernel version.
Return type: text
The following is an example. Replace x.x.x in the query result with the actual value.
1 2 3 4 5 |
gaussdb=# SELECT opengauss_version(); opengauss_version ------------------- x.x.x (1 row) |
gs_deployment()
Description: Deployment mode of the current system. For a distributed system, Distribute is returned.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT gs_deployment(); gs_deployment --------------- Distribute (1 row) |
session_user
Description: Session username.
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT session_user; session_user -------------- omm (1 row) |
Note: session_user usually specifies the initial user connected to the current database, but system administrators can change this setting by using SET SESSION AUTHORIZATION.
user
Description: Equivalent to current_user.
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT user; current_user -------------- omm (1 row) |
get_shard_oids_byname()
Description: Returns the OID of the node when the node name is entered.
Return type: OID
Example:
1 2 3 4 5 |
gaussdb=# SELECT get_shard_oids_byname('datanode1'); get_shard_oids_byname ----------------------- {16385} (1 row) |
getpgusername()
Description: Obtains the database username.
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT getpgusername(); getpgusername --------------- GaussDB_userna (1 row) |
getdatabaseencoding()
Description: Obtains the database encoding mode.
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT getdatabaseencoding(); getdatabaseencoding --------------------- SQL_ASCII (1 row) |
version()
Description: version information. version returns a string describing a server's version.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT version(); version ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- gaussdb (GaussDB XXX.XXX.XXX build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release (1 row) |
working_version_num()
Description: version number. It returns a version number related to system compatibility.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT working_version_num(); working_version_num --------------------- 92231 (1 row) |
get_hostname()
Description: Returns the host name of the current node.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT get_hostname(); get_hostname -------------- linux-user (1 row) |
get_nodename()
Description: Returns the name of the current node.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT get_nodename(); get_nodename -------------- coordinator1 (1 row) |
get_nodeinfo(text)
Description: Returns the value of the corresponding node information based on the search attribute. Currently, the search attributes include node_name and node_type.
Return type: text
Example:
1 2 3 4 5 6 7 8 9 10 |
gaussdb=# SELECT get_nodeinfo('node_type'); get_nodeinfo -------------- DIS_CN (1 row) gaussdb=# SELECT get_nodeinfo('node_name'); get_nodeinfo -------------- coordinator1 (1 row) |
get_schema_oid(cstring)
Description: Returns the OID of the queried schema.
Return type: OID
Example:
1 2 3 4 5 |
gaussdb=# SELECT get_schema_oid('public'); get_schema_oid ---------------- 2200 (1 row) |
pgxc_parse_clog(OUT xid int8, OUT nodename text, OUT status text)
Description: Returns the status of all transactions in the current cluster.
Return type: SETOF record
Example:
1 2 3 4 5 6 7 |
gaussdb=# SELECT pgxc_parse_clog(); pgxc_parse_clog ---------------- (0,dn_6004_6005_6006,INPROGRESS) (1,dn_6004_6005_6006,COMMITTED) (2,dn_6004_6005_6006,INPROGRESS) (3 row) |
pgxc_parse_clog(IN int2 bucketid, OUT xid int8, OUT nodename text, OUT status text)
Description: Returns the statuses of all transactions with the specified bucket IDs in the current cluster. The input parameter is the bucket IDs of the hash bucket table. The value range is [–1,1023]. For an ordinary table, the value is –1.
Return type: SETOF record
Example:
1 2 3 4 5 6 7 |
gaussdb=# SELECT pgxc_parse_clog('-1'); pgxc_parse_clog ---------------- (0,dn_6004_6005_6006,INPROGRESS) (1,dn_6004_6005_6006,COMMITTED) (2,dn_6004_6005_6006,INPROGRESS) (3 row) |
pgxc_prepared_xact()
Description: Returns the list of transaction GIDs at the prepared stage in the cluster.
Return type: set of text
Example:
1 2 3 4 |
gaussdb=# SELECT pgxc_prepared_xact(); pgxc_prepared_xact -------------------- (0 row) |
pgxc_xacts_iscommitted()
Description: Returns the status of the transaction with the specified XID in the cluster. t indicates the committed state, f indicates the aborted state, and null indicates other states. Users must have the SYSADMIN or MONADMIN permission.
Return type: SETOF record
Example:
1 2 3 4 5 6 7 8 |
gaussdb=# SELECT pgxc_xacts_iscommitted(1); pgxc_xacts_iscommitted -------------------- (dn_6004_6005_6006,t) (cn_5001,t) (cn_5002,t) (dn_6001_6002_6003,t) (4 row) |
pgxc_total_memory_detail()
Description: Displays the memory usage in the cluster. Users must have the SYSADMIN or MONADMIN permission.
If the GUC parameter enable_memory_limit is set to off, this function cannot be used.
Return type: set of pv_total_memory_detail
Example:
1 2 3 4 5 6 7 8 |
gaussdb=# SELECT pgxc_total_memory_detail(); pgxc_total_memory_detail -------------------- (dn_6004_6005_6006,max_process_memory,81920) (dn_6004_6005_6006,process_used_memory,72747) (dn_6004_6005_6006,max_dynamic_memory,12096) (dn_6004_6005_6006,dynamic_used_memory,1530) (4 row) |
pv_total_memory_detail()
Description: Collects statistics on memory usage of the current database node in the unit of MB.
If the GUC parameter enable_memory_limit is set to off, this function cannot be used.
Return type: record
|
Name |
Type |
Description |
|---|---|---|
|
nodename |
text |
Node name |
|
memorytype |
text |
Memory type. The value must be one of the following:
|
|
memorymbytes |
integer |
Size of allocated memory-typed memory |
get_client_info()
Description: Returns client information.
Return type: record
pv_session_memory_detail()
Description: Collects session memory usage by the MemoryContext node.
Return type: record
|
Name |
Type |
Description |
|---|---|---|
|
sessid |
text |
|
|
threadid |
bigint |
Thread ID bound to a session. |
|
contextname |
text |
Name of the memory context. |
|
level |
smallint |
Hierarchy of the memory context |
|
parent |
text |
Name of the parent memory context |
|
totalsize |
bigint |
Total size of the memory context, in bytes |
|
freesize |
bigint |
Total size of released memory in the current memory context, in bytes |
|
usedsize |
bigint |
Size of used memory in the memory context, in bytes. For TempSmallContextGroup, this parameter specifies the number of collected memory contexts. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
