pg_stat_xact_sys_tables
pg_stat_xact_sys_tables
is a statistics view showing statistics about accesses to each system table in the current database during the current transaction.
pg_stat_xact_sys_tables
was added in PostgreSQL 9.1.
Definition by PostgreSQL version
pg_stat_xact_sys_tables (PostgreSQL 15)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Collation | Nullable | Default ---------------+--------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 14)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Collation | Nullable | Default ---------------+--------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 13)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Collation | Nullable | Default ---------------+--------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 12)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Collation | Nullable | Default ---------------+--------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 11)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Collation | Nullable | Default ---------------+--------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 10)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Collation | Nullable | Default ---------------+--------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 9.6)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Modifiers ---------------+--------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 9.5)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Modifiers ---------------+--------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 9.4)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Modifiers ---------------+--------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 9.3)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Modifiers ---------------+--------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 9.2)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Modifiers ---------------+--------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint |
Documentation: pg_stat_xact_sys_tables
pg_stat_xact_sys_tables (PostgreSQL 9.1)
View "pg_catalog.pg_stat_xact_sys_tables" Column | Type | Modifiers ---------------+--------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint |
Documentation: pg_stat_xact_sys_tables
Change history
- PostgreSQL 9.1
- added (commit 46aa77c7)
Examples
Using pg_stat_xact_sys_tables
to show all system tables where inserts occurred during a transaction where a table was created:
postgres=*# CREATE TABLE foo (id INT, val TEXT); CREATE TABLE postgres=*# SELECT schemaname, relname, n_tup_ins FROM pg_stat_xact_sys_tables WHERE n_tup_ins > 0; schemaname | relname | n_tup_ins ------------+--------------+----------- pg_catalog | pg_type | 2 pg_catalog | pg_depend | 6 pg_catalog | pg_index | 1 pg_catalog | pg_attribute | 19 pg_catalog | pg_class | 3 (5 rows) postgres=*# commit; COMMIT postgres=# SELECT schemaname, relname, n_tup_ins FROM pg_stat_xact_sys_tables WHERE n_tup_ins > 0; schemaname | relname | n_tup_ins ------------+---------+----------- (0 rows)
References
- PostgreSQL documentation: pg_stat_xact_sys_tables