pg_stat_xact_all_tables
A statistics view showing statistics about accesses to each table during the current transaction
pg_stat_xact_all_tables is a statistics view showing statistics about accesses to each table in the current database during the current transaction.
pg_stat_xact_all_tables was added in PostgreSQL 9.1.
Definition by PostgreSQL version
pg_stat_xact_all_tables (PostgreSQL 16)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 15)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 14)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 13)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 12)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 11)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 10)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 9.6)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 9.5)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 9.4)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 9.3)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 9.2)
View "pg_catalog.pg_stat_xact_all_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_all_tables
pg_stat_xact_all_tables (PostgreSQL 9.1)
View "pg_catalog.pg_stat_xact_all_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_all_tables
Change history
- PostgreSQL 9.1
- added (commit 46aa77c7)
Examples
Using pg_stat_xact_all_tables to show all tables where inserts occurred during a transaction where a table was created and populated:
postgres=# BEGIN;
BEGIN
Time: 0.403 ms
postgres=*# CREATE TABLE foo (id INT, val TEXT);
CREATE TABLE
postgres=*# INSERT INTO foo values(generate_series(1,100));
INSERT 0 100
postgres=*# SELECT schemaname, relname, n_tup_ins
FROM pg_stat_xact_all_tables
WHERE n_tup_ins > 0;
schemaname | relname | n_tup_ins
------------+--------------+-----------
pg_catalog | pg_type | 2
pg_catalog | pg_depend | 6
public | foo | 100
pg_catalog | pg_index | 1
pg_catalog | pg_attribute | 19
pg_catalog | pg_class | 3
(6 rows)
postgres=*# commit;
COMMIT
postgres=*# SELECT schemaname, relname, n_tup_ins
FROM pg_stat_xact_all_tables
WHERE n_tup_ins > 0;
schemaname | relname | n_tup_ins
------------+---------+-----------
(0 rows)
References
- PostgreSQL documentation: pg_stat_xact_all_tables