Statistics Information Functions(1)

Statistics information functions are divided into the following two categories: functions that access databases, using the OID of each table or index in a database to mark the database for which statistics are generated; functions that access servers, identified by the server process ID, whose value ranges from 1 to the number of currently active servers.

• pg_stat_get_db_conflict_tablespace(oid)

  Description: Specifies the number of queries canceled due to a conflict between the restored tablespace and the deleted tablespace in the database.

  Return type: bigint

• pg_control_group_config

  Description: Prints Cgroup configurations on the current node.

  Return type: record

• pg_stat_get_db_stat_reset_time(oid)

  Description: Specifies the most recent time when database statistics were reset. It is initialized to the system time during the first connection to each database. The reset time is updated when you call pg_stat_reset on the database and execute pg_stat_reset_single_table_counters against any table or index in it.

  Return type: timestamptz

• pg_stat_get_function_total_time(oid)

  Description: Specifies the total wall clock time spent in the function, in microseconds. The time spent on this function calling other functions is included.

  Return type: bigint

• pg_stat_get_xact_tuples_returned(oid)

  Description: Specifies the number of rows read through sequential scans when the parameter is a table in the current transaction or the number of index entries returned when the parameter is an index.

  Return type: bigint

• pg_lock_status()

  Description: Queries information about locks held by open transactions. All users can execute this function.

  Return type: For details, see PG_LOCKS which is obtained by querying this function.

• pg_stat_get_xact_numscans(oid)

  Description: Specifies the number of sequential scans performed when the parameter is a table in the current transaction or the number of index scans performed when the parameter is an index.

  Return type: bigint

• pg_stat_get_xact_blocks_fetched(oid)

  Description: Specifies the number of disk block fetch requests for a table or an index in the current transaction.

  Return type: bigint

• pg_stat_get_xact_blocks_hit(oid)

  Description: Specifies the number of disk block fetch requests for tables or indexes found in cache in the current transaction.

  Return type: bigint

• pg_stat_get_xact_function_calls(oid)

  Description: Specifies the number of times the function is called in the current transaction.

  Return type: bigint

• pg_stat_get_xact_function_self_time(oid)

  Description: Specifies the time spent on this function in the current transaction, excluding the time spent on this function internally calling other functions.

  Return type: bigint

• pg_stat_get_xact_function_total_time(oid)

  Description: Specifies the total wall clock time (in microseconds) spent on the function in the current transaction, including the time spent on this function internally calling other functions.

  Return type: bigint

• pg_stat_get_wal_senders()

  Description: Queries walsender information on the primary server.

  Return type: setofrecord

  The following table describes return columns.

  Table 1 Return column description

  Column	Type	Description
  pid	bigint	Thread ID of the WAL sender
  sender_pid	integer	Lightweight thread ID of the WAL sender
  local_role	text	Type of the primary node
  peer_role	text	Type of the standby node
  peer_state	text	Status of the standby node
  state	text	Status of the WAL sender
  catchup_start	timestamp with time zone	Startup time of a catchup task
  catchup_end	timestamp with time zone	End time of a catchup task
  sender_sent_location	text	Sending position of the primary node
  sender_write_location	text	Writing position of the primary node
  sender_flush_location	text	Flushing position of the primary node
  sender_replay_location	text	Redo position of the primary node
  receiver_received_location	text	Receiving position of the standby node
  receiver_write_location	text	Writing position of the standby node
  receiver_flush_location	text	Flushing position of the standby node
  receiver_replay_location	text	Redo position of the standby node
  sync_percent	text	Synchronization percentage
  sync_state	text	Synchronization status
  sync_group	text	Group to which the synchronous replication belongs
  sync_priority	text	Priority of synchronous replication
  sync_most_available	text	Maximum availability mode
  channel	text	Channel information of the WAL sender

• get_paxos_replication_info()

  Description: Queries the primary/standby replication status in Paxos mode.

  Return type: setofrecord

  The following table describes return columns.

  Table 2 Return column description

  Column	Type	Description
  paxos_write_location	text	Location of the Xlog that has been written to the Distribute Consensus Framework (DCF)
  paxos_commit_location	text	Location of the Xlog agreed in the DCF
  local_write_location	text	Writing position of a node
  local_flush_location	text	Flushing position of a node
  local_replay_location	text	Redo position of a node
  dcf_replication_info	text	DCF module information of a node

• pg_stat_get_stream_replications()

  Description: Queries the primary/standby replication status.

  Return type: setofrecord

  The following table describes return values.

  Table 3 Return value description

  Return Parameter	Type	Description
  local_role	text	Local role
  static_connections	integer	Connection statistics
  db_state	text	Database status
  detail_information	text	Detailed information

• pg_stat_get_db_numbackends(oid)

  Description: Specifies the number of active server processes for a database.

  Return type: integer

• pg_stat_get_db_xact_commit(oid)

  Description: Specifies the number of transactions committed in a database.

  Return type: bigint

• pg_stat_get_db_xact_rollback(oid)

  Description: Specifies the number of transactions rolled back in a database.

  Return type: bigint

• pg_stat_get_db_blocks_fetched(oid)

  Description: Specifies the number of disk blocks fetch requests for a database.

  Return type: bigint

• pg_stat_get_db_blocks_hit(oid)

  Description: Specifies the number of disk block fetch requests found in cache for a database.

  Return type: bigint

• pg_stat_get_db_tuples_returned(oid)

  Description: Specifies the number of tuples returned for a database.

  Return type: bigint

• pg_stat_get_db_tuples_fetched(oid)

  Description: Specifies the number of tuples fetched for a database.

  Return type: bigint

• pg_stat_get_db_tuples_inserted(oid)

  Description: Specifies the number of tuples inserted in a database.

  Return type: bigint

• pg_stat_get_db_tuples_updated(oid)

  Description: Specifies the number of tuples updated in a database.

  Return type: bigint

• pg_stat_get_db_tuples_deleted(oid)

  Description: Specifies the number of tuples deleted in a database.

  Return type: bigint

• pg_stat_get_db_conflict_lock(oid)

  Description: Specifies the number of lock conflicts in a database.

  Return type: bigint

• pg_stat_get_db_deadlocks(oid)

  Description: Specifies the number of deadlocks in a database.

  Return type: bigint

• pg_stat_get_numscans(oid)

  Description: Specifies the number of sequential row scans done if parameters are in a table or the number of index scans done if parameters are in an index.

  Return type: bigint

• pg_stat_get_role_name(oid)

  Description: Obtains the username based on the user OID. Only users with the sysadmin or monitor admin permission can access the information.

  Return type: text

  Example:

  MogDB=# select pg_stat_get_role_name(10);
   pg_stat_get_role_name
  -----------------------
   aabbcc
  (1 row)

• pg_stat_get_tuples_returned(oid)

  Description: Specifies the number of sequential row scans done if parameters are in a table or the number of index scans done if parameters are in an index.

  Return type: bigint

• pg_stat_get_tuples_fetched(oid)

  Description: Specifies the number of table rows fetched by bitmap scans if parameters are in a table or the number of table rows fetched by simple index scans using the index if parameters are in an index.

  Return type: bigint

• pg_stat_get_tuples_inserted(oid)

  Description: Specifies the number of rows inserted into a table.

  Return type: bigint

• pg_stat_get_tuples_updated(oid)

  Description: Specifies the number of rows updated in a table.

  Return type: bigint

• pg_stat_get_tuples_deleted(oid)

  Description: Specifies the number of rows deleted from a table.

  Return type: bigint

• pg_stat_get_tuples_changed(oid)

  Description: Specifies the total number of inserted, updated, and deleted rows after a table was last analyzed or autoanalyzed.

  Return type: bigint

• pg_stat_get_tuples_hot_updated(oid)

  Description: Specifies the number of rows hot updated in a table.

  Return type: bigint

• pg_stat_get_live_tuples(oid)

  Description: Specifies the number of live rows in a table.

  Return type: bigint

• pg_stat_get_dead_tuples(oid)

  Description: Specifies the number of dead rows in a table.

  Return type: bigint

• pg_stat_get_blocks_fetched(oid)

  Description: Specifies the number of disk block fetch requests for a table or an index.

  Return type: bigint

• pg_stat_get_blocks_hit(oid)

  Description: Specifies the number of disk block requests found in cache for a table or an index.

  Return type: bigint

• pg_stat_get_partition_tuples_inserted(oid)

  Description: Specifies the number of rows in the corresponding table partition.

  Return type: bigint

• pg_stat_get_partition_tuples_updated(oid)

  Description: Specifies the number of rows that have been updated in the corresponding table partition.

  Return type: bigint

• pg_stat_get_partition_tuples_deleted(oid)

  Description: Specifies the number of rows deleted from the corresponding table partition.

  Return type: bigint

• pg_stat_get_partition_tuples_changed(oid)

  Description: Specifies the total number of inserted, updated, and deleted rows after a table partition was last analyzed or autoanalyzed.

  Return type: bigint

• pg_stat_get_partition_live_tuples(oid)

  Description: Specifies the number of live rows in a partitioned table.

  Return type: bigint

• pg_stat_get_partition_dead_tuples(oid)

  Description: Specifies the number of dead rows in a partitioned table.

  Return type: bigint

• pg_stat_get_xact_tuples_fetched(oid)

  Description: Specifies the number of tuple rows scanned in a transaction.

  Return type: bigint

• pg_stat_get_xact_tuples_inserted(oid)

  Description: Specifies the number of tuple inserted into the active subtransactions related to a table.

  Return type: bigint

• pg_stat_get_xact_tuples_deleted(oid)

  Description: Specifies the number of deleted tuples in the active subtransactions related to a table.

  Return type: bigint

• pg_stat_get_xact_tuples_hot_updated(oid)

  Description: Specifies the number of hot updated tuples in the active subtransactions related to a table.

  Return type: bigint

• pg_stat_get_xact_tuples_updated(oid)

  Description: Specifies the number of updated tuples in the active subtransactions related to a table.

  Return type: bigint

• pg_stat_get_xact_partition_tuples_inserted(oid)

  Description: Specifies the number of inserted tuples in the active subtransactions related to a table partition.

  Return type: bigint

• pg_stat_get_xact_partition_tuples_deleted(oid)

  Description: Specifies the number of deleted tuples in the active subtransactions related to a table partition.

  Return type: bigint

• pg_stat_get_xact_partition_tuples_hot_updated(oid)

  Description: Specifies the number of hot updated tuples in the active subtransactions related to a table partition.

  Return type: bigint

• pg_stat_get_xact_partition_tuples_updated(oid)

  Description: Specifies the number of updated tuples in the active subtransactions related to a table partition.

  Return type: bigint

• pg_stat_get_last_vacuum_time(oid)

  Description: Specifies the most recent time when the autovacuum thread is manually started to clear a table.

  Return type: timestamptz

• pg_stat_get_last_autovacuum_time(oid)

  Description: Specifies the time of the last vacuum initiated by the autovacuum daemon on a table.

  Return type: timestamptz

• pg_stat_get_vacuum_count(oid)

  Description: Specifies the number of times a table is manually cleared.

  Return type: bigint

• pg_stat_get_autovacuum_count(oid)

  Description: Specifies the number of times the autovacuum daemon is started to clear a table.

  Return type: bigint

• pg_stat_get_last_analyze_time(oid)

  Description: Specifies the last time when a table starts to be analyzed manually or by the autovacuum thread.

  Return type: timestamptz

• pg_stat_get_last_autoanalyze_time(oid)

  Description: Specifies the time when the last analysis initiated by the autovacuum daemon on a table.

  Return type: timestamptz

• pg_stat_get_analyze_count(oid)

  Description: Specifies the number of times a table is manually analyzed.

  Return type: bigint

• pg_stat_get_autoanalyze_count(oid)

  Description: Specifies the number of times the autovacuum daemon analyzes a table.

  Return type: bigint

• pg_total_autovac_tuples(bool,bool)

  Description: Returns tuple records related to the total autovac, such as nodename, nspname, relname, and tuple IUDs. The input parameters specify whether to query relation and local information, respectively.

  Return type: setofrecord

  The following table describes return parameters.

  Table 4 Return parameter description

  Return Parameter	Type	Description
  nodename	name	Node name
  nspname	name	Name of a namespace
  relname	name	Name of an object, such as a table, an index, or a view
  partname	name	Partition name
  n_dead_tuples	bigint	Number of dead rows in a table partition
  n_live_tuples	bigint	Number of live rows in a table partition
  changes_since_analyze	bigint	Number of changes generated by ANALYZE

• pg_autovac_status(oid)

  Description: Returns autovac information, such as nodename, nspname, relname, analyze, vacuum, thresholds of analyze and vacuum, and the number of analyzed or vacuumed tuples. Only users with the sysadmin permission can use this function.

  Return type: setofrecord

  The following table describes return parameters.

  Table 5 Return parameter description

  Return Parameter	Type	Description
  nspname	text	Name of a namespace
  relname	text	Name of an object, such as a table, an index, or a view
  nodename	text	Node name
  doanalyze	Boolean	Whether to execute ANALYZE
  anltuples	bigint	Number of ANALYZE tuples
  anlthresh	bigint	ANALYZE threshold
  dovacuum	Boolean	Whether to execute VACUUM
  vactuples	bigint	Number of VACUUM tuples
  vacthresh	bigint	VACUUM threshold

• pg_autovac_timeout(oid)

  Description: Returns the number of consecutive timeouts during the autovac operation on a table. If the table information is invalid or the node information is abnormal, NULL will be returned.

  Return type: bigint

• pg_stat_get_last_data_changed_time(oid)

  Description: Returns the time when INSERT, UPDATE, DELETE, or EXCHANGE/TRUNCATE/DROP PARTITION was last performed on a table. The data in the last_data_changed column of the PG_STAT_ALL_TABLES view is calculated by using this function. The performance of obtaining the last modification time by using the view is poor when the table has a large amount of data. In this case, you are advised to use the function.

  Return type: timestamptz

• pg_stat_set_last_data_changed_time(oid)

  Description: Manually changes the time when INSERT, UPDATE, DELETE, or EXCHANGE/TRUNCATE/DROP PARTITION was last performed.

  Return type: void

• pg_backend_pid()

  Description: Specifies the thread ID of the server thread attached to the current session.

  Return type: integer

• pg_stat_get_activity(integer)

  Description: Returns a record about the backend with the specified PID. A record for each active backend in the system is returned if NULL is specified. The returned result does not contain the connection_info column. The initial user, system administrators and users with the monadmin permission can view all data. Common users can only query their own results.

  Example:

  MogDB=# select * from pg_stat_get_activity(139881386280704);
   datid |       pid       | sessionid | usesysid | application_name | state  |                        query                         | waiting |          xact_start           |          query_start          |
      backend_start         |         state_change         | client_addr | client_hostname | client_port | enqueue |     query_id      |   srespool   | global_sessionid | unique_sql_id | trace_id
  -------+-----------------+-----------+----------+------------------+--------+------------------------------------------------------+---------+-------------------------------+-------------------------------+-----
  --------------------------+------------------------------+-------------+-----------------+-------------+---------+-------------------+--------------+------------------+---------------+----------
   16545 | 139881386280704 |        69 |       10 | gsql             | active | select * from pg_stat_get_activity(139881386280704); | f       | 2022-01-18 19:43:05.167718+08 | 2022-01-18 19:43:05.167718+08 | 2022
  -01-18 19:42:33.513507+08 | 2022-01-18 19:43:05.16773+08 |             |                 |          -1 |         | 72620543991624410 | default_pool | 1938253334#69#0  |    3751941862 |
  (1 row)

  Return type: setofrecord

  The following table describes return parameters.

  Table 6 Return parameter description

  Return Parameter	Type	Description
  datid	oid	OID of the database that the user session connects to in the backend
  pid	bigint	Backend thread ID
  sessionid	bigint	Session ID
  usesysid	oid	OID of the user logged in to the backend
  application_name	text	Name of the application connected to the backend
  state	text	Overall status of the backend
  query	text	Latest query at the backend. If state is active, this column shows the ongoing query. In all other states, it shows the last query that was executed.
  waiting	Boolean	Whether the backend is currently waiting on a lock. If yes, the value is true.
  xact_start	timestamp with time zone	Time when current transaction was started (null if no transaction is active).If the current query is the first of its transaction, the value of this column is the same as that of the query_start column.
  query_start	timestamp with time zone	Time when the currently active query was started, or time when the last query was started if state is not active
  backend_start	timestamp with time zone	Time when this process was started, that is, when the client connected to the server
  state_change	timestamp with time zone	Time when state was last modified
  client_addr	inet	IP address of the client connected to the backend. If this column is NULL, it indicates either the client is connected via a Unix socket on the server or this is an internal process, such as AUTOVACUUM.
  client_hostname	text	Host name of the connected client, as reported by a reverse DNS lookup of client_addr. This column will be non-null only for IP connections and only when log_hostname is enabled.
  client_port	integer	TCP port number that the client uses for communication with this backend (–1 if a Unix socket is used)
  enqueue	text	Unsupported currently
  query_id	bigint	ID of a query
  srespool	name	Name of the resource pool
  global_sessionid	text	Global session ID
  unique_sql_id	bigint	Unique SQL statement ID
  trace_id	text	Driver-specific trace ID, which is associated with an application request

• pg_stat_get_activity_with_conninfo(integer)

  Description: Returns a record about the backend with the specified PID. A record for each active backend in the system is returned if NULL is specified. The initial user, system administrators and users with the monadmin permission can view all data. Common users can only query their own results.

  Return type: setofrecord

  The following table describes return values.

  Table 7 Return value description

  Return Value	Return Type	Description
  datid	oid	OID of the database that the user session connects to in the backend
  pid	bigint	Backend thread ID
  sessionid	bigint	Session ID
  usesysid	oid	OID of the user logged in to the backend
  application_name	text	Name of the application connected to the backend
  state	text	Overall status of the backend
  query	text	Latest query at the backend. If state is active, this column shows the ongoing query. In all other states, it shows the last query that was executed.
  waiting	Boolean	Whether the backend is currently waiting on a lock. If yes, the value is true.
  xact_start	timestamp with time zone	Time when current transaction was started (null if no transaction is active). If the current query is the first of its transaction, the value of this column is the same as that of the query_start column.
  query_start	timestamp with time zone	Time when the currently active query was started, or time when the last query was started if state is not active
  backend_start	timestamp with time zone	Time when this process was started, that is, when the client connected to the server
  state_change	timestamp with time zone	Time when state was last modified
  client_addr	inet	IP address of the client connected to the backend If this column is NULL, it indicates either the client is connected via a Unix socket on the server or this is an internal process, such as AUTOVACUUM.
  client_hostname	text	Host name of the connected client, as reported by a reverse DNS lookup of client_addr. This column will be non-null only for IP connections and only when log_hostname is enabled.
  client_port	integer	TCP port number that the client uses for communication with this backend (–1 if a Unix socket is used)
  enqueue	text	Unsupported currently
  query_id	bigint	ID of a query
  connection_info	text	A string in JSON format recording the driver type, driver version, driver deployment path, and process owner of the connected database
  srespool	name	Name of the resource pool
  global_sessionid	text	Global session ID
  unique_sql_id	bigint	Unique SQL statement ID
  trace_id	text	Driver-specific trace ID, which is associated with an application request

• pg_user_iostat(text)

  Description: Displays the I/O load management information about the job currently executed by the user.

  Return type: record

  The following table describes return fields.

  Name	Type	Description
  userid	oid	User ID
  min_curr_iops	int4	Minimum I/O of the current user across database nodes. The IOPS is counted by ones for column storage and by ten thousands for row storage.
  max_curr_iops	int4	Maximum I/O of the current user across database nodes. The IOPS is counted by ones for column storage and by ten thousands for row storage.
  min_peak_iops	int4	Minimum peak I/O of the current user across database nodes. The IOPS is counted by ones for column storage and by ten thousands for row storage.
  max_peak_iops	int4	Maximum peak I/O of the current user across database nodes. The IOPS is counted by ones for column storage and by ten thousands for row storage.
  io_limits	int4	io_limits set for the resource pool specified by the user. The IOPS is counted by ones for column storage and by ten thousands for row storage.
  io_priority	text	io_priority set for the user. The IOPS is counted by ones for column storage and by ten thousands for row storage.
  curr_io_limits	int4	Real-time io_limits value when io_priority is used to control I/Os

• pg_stat_get_function_calls(oid)

  Description: Specifies the number of times the function has been called.

  Return type: bigint

• pg_stat_get_function_self_time(oid)

  Description: Specifies the time spent in only this function. The time spent on this function calling other functions is excluded.

  Return type: bigint

• pg_stat_get_backend_idset()

  Description: Sets the number of currently active server processes (from 1 to the number of active server processes).

  Return type: setofinteger

• pg_stat_get_backend_pid(integer)

  Description: Specifies the ID of the given server thread.

  Return type: bigint

• pg_stat_get_backend_dbid(integer)

  Description: Specifies the ID of the database connected to the given server process.

  Return type: oid

• pg_stat_get_backend_userid(integer)

  Description: Specifies the user ID of the given server process.

  Return type: oid

• pg_stat_get_backend_activity(integer)

  Description: Active command of the given server process, but only if the current user is a system administrator or the same user as that of the session being queried and track_activities is on

  Return type: text