Querying Transactions

Function

This API is used to query real-time transactions of a database instance node. Before calling this API:

Learn how to authenticate this API.
Obtain the required region and endpoint.

Constraints

This API is only available for nodes with CNs or DNs (primary or standby DNs) deployed.

Debugging

You can debug this API in API Explorer.

URI

POST /v3/{project_id}/instances/{instance_id}/transactions/list

**Table 1** Parameter description
Parameter	Mandatory	Type	Description
project_id	Yes	String	Definition Project ID of a tenant in a region. To obtain the value, see Obtaining a Project ID. Constraints N/A Range The value can contain 32 characters. Only letters and digits are allowed. Default Value N/A
instance_id	Yes	String	Definition Instance ID, which uniquely identifies an instance. Constraints N/A Range The value can contain 36 characters. Only letters and digits are allowed. Default Value N/A

Request Parameters

**Table 2** Request header parameters
Parameter	Mandatory	Type	Description
X-Auth-Token	Yes	String	Definition User token. You can obtain the token by calling the IAM API used to obtain a user token. After the request is processed, the value of X-Subject-Token in the message header is the token value. Constraints N/A Range N/A Default Value N/A
X-Language	No	String	Definition Language. Constraints N/A Range zh-cn en-us Default Value en-us

**Table 3** Request body parameters
Parameter	Mandatory	Type	Description
node_id	Yes	String	Definition Node ID. Only CNs or DNs of the non-log type are supported. Constraints N/A Range N/A Default Value N/A
component_id	Yes	String	Definition Component ID. Only CNs or DNs of the non-log type are supported. Constraints N/A Range N/A Default Value N/A
offset	No	Integer	Definition Start page number of the transactions in the query result. Constraints N/A Range [0,2^31-1] Default Value 0: The query starts from the first data record.
limit	No	Integer	Definition Maximum number of transactions in the query result. Constraints N/A Range [1,100] Default Value 10
transaction_query_info	No	Object	Definition Filter criteria for querying transactions. For details, see Table 4. Constraints N/A Range N/A Default Value N/A

**Table 4** Parameter description of the **TransactionQueryInfo** field
Parameter	Mandatory	Type	Description
exec_time	No	Integer	Definition Transaction execution duration, in seconds. Constraints N/A Range The value must be a non-negative integer. Default Value 0
xlog_quantity	No	Integer	Definition Size of a transaction Xlog, in bytes. Constraints N/A Range The value must be a non-negative integer. Default Value 0
datnames	No	List<String>	Definition Database name. Constraints N/A Range N/A Default Value N/A
usenames	No	List<String>	Definition Username. Constraints N/A Range N/A Default Value N/A
client_addrs	No	List<String>	Definition IP address of the client that initiated the transaction request. Constraints N/A Range N/A Default Value N/A

Response Parameters

**Table 5** Response body parameters
Parameter	Type	Description
total	Integer	Definition Number of queried transactions. Range N/A
rows	Array of objects	Definition Transaction information. For details, see Table 6.

**Table 6** Parameter description of the **TransactionInfo** field
Parameter	Type	Description
sessionid	Integer	Definition Transaction ID. Range N/A
pid	Integer	Definition Thread ID. Range N/A
query_id	Integer	Definition SQL query ID. Range N/A
datname	String	Definition Database. Range N/A
client_addr	String	Definition IP address of the client that initiated the transaction request. Range N/A
client_port	Integer	Definition Port number of the client that initiated the transaction request. Range N/A
usename	String	Definition Username. Range N/A
query	String	Definition SQL query statement. Range N/A
backend_start	String	Definition Session start time. Range N/A
xact_start	String	Definition Transaction start time. Range N/A
application_name	String	Definition Application name. Range N/A
state	String	Definition Transaction status. Range N/A
state_change	String	Definition Transaction status change time. Range N/A
query_start	String	Definition Query start time. Range N/A
waiting	String	Definition Lock waiting. Range N/A
unique_sql_id	Integer	Definition Normalized SQL ID. Range N/A
top_xid	String	Definition Top-level transaction ID. Range N/A
current_xid	String	Definition Current transaction ID. Range N/A
exec_time	String	Definition Transaction execution duration. Range N/A
xlog_quantity	Integer	Definition Number of Xlogs. Range N/A

Example Request

POST https://gaussdb-opengauss.ap-southeast-1.myhuaweicloud.com/v3/0611f1bd8b00d5d32f17c017f15b599f/instances/d04686c6baae4f65a742771186f47b7ain14/transactions/list
{ 
   "node_id" : "42cbf784585e419d9eb33c005f4ced94no14", 
   "component_id" : "dn_6001", 
   "limit" : 10, 
   "offset" : 0, 
   "transaction_query_info" : { 
     "exec_time" : "1000", 
     "xlog_quantity" : "999", 
     "datnames" : [ "postgres" ], 
     "usenames" : [ "root" ], 
     "client_addrs" : [ "172.0.0.1" ] 
   } 
}

Example Response

{
    "total" : 1,
    "rows" : [ {
      "sessionid" : 25347,
      "pid" : 140625211029248,
      "query_id" : 0,
      "datname" : "postgres",
      "client_addr" : "127.0.0.1",
      "client_port" : 60256,
      "usename" : "root",
      "query" : "start transaction isolation level repeatable read;",
      "backend_start" : "2023-12-11 15:10:21.553063+08",
      "xact_start" : "2023-12-11 15:10:29.671267+08",
      "application_name" : "gsql",
      "state" : "idle in transaction",
      "state_change" : "2023-12-11 15:10:29.671345+08",
      "query_start" : "2023-12-11 15:10:29.671267+08",
      "waiting" : "f",
      "unique_sql_id" : 0,
      "top_xid" : "0",
      "current_xid" : "0",
      "exec_time" : "232",
      "xlog_quantity" : 0
    } ]
  }