From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | pg_stat_all_tables data isnt accurate |
Date: | 2018-12-03 16:50:00 |
Message-ID: | CA+t6e1kCqAOUaVQrBGgb=iYaKrW4d0qwu-an_Zaov57Pb54+SQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi,
We are trying to use the info in pg_Stat_all_tables to debug our
applcation(see how much inserts/updates/deletes since the last reset).
However we found some scenarios where the data in the view isnt accurate :
1)When you preform a rollback all the inserts/updates/deletes you did are
added to the n_tup_ins/upd/del column :
postgres=# \d pgstat.pg_stat_all_tables;^C
postgres=# create table test(a int);
postgres=# select relname, n_live_tup,n_tup_ins,n_tup_del,n_tup_upd from
pg_Stat_all_tables where relname='test';
relname | n_live_tup | n_tup_ins | n_tup_del | n_tup_upd
---------+------------+-----------+-----------+-----------
test | 0 | 0 | 0 | 0
(1 row)
postgres=# start transaction
postgres-# ;
START TRANSACTION
postgres=# insert into test values(5);
INSERT 0 1
postgres=# insert into test values(6);
INSERT 0 1
postgres=# insert into test values(7);
INSERT 0 1
postgres=# rollback;
ROLLBACK
postgres=# select relname, n_live_tup,n_tup_ins,n_tup_del,n_tup_upd from
pg_Stat_all_tables where relname='test';
relname | n_live_tup | n_tup_ins | n_tup_del | n_tup_upd
---------+------------+-----------+-----------+-----------
test | 0 | 3 | 0 | 0
(1 row)
2)sometimes the n_tup_ins isnt accurate and it takes some time until it is
updated. Does someone has an explanation for it ? During that time analyze
isnt running so it seems might be something else.
From | Date | Subject | |
---|---|---|---|
Next Message | Nikolay Samokhvalov | 2018-12-03 17:35:54 | Re: pg_stat_all_tables data isnt accurate |
Previous Message | Shreeyansh Dba | 2018-12-03 12:15:16 | Re: pg_basebackup fails: could not receive data from WAL stream: server closed the connection unexpectedly |