lossing pg_stat's data - Mailing list pgsql-general

While testing on 8.3, i see that upon postmaster restart , i am loosing
data from pg_stat_user_tables. as i understand in 8.3 only way to reset
is by calling |pg_stat_reset().

Sorry for the long post:

Here is my test case:

sample=# SELECT version();

version
------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
(Ubuntu 4.1.2-0ubuntu4)
(1 row)



Created sample database

pg_bench on DB with scaling factor 100


*** Stats after loading the data:

sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+------------------------------
relid            | 114694
schemaname       | public
relname          | accounts
seq_scan         | 1
seq_tup_read     | 10000000
idx_scan         | 0
idx_tup_fetch    | 0
n_tup_ins        | 10000000
n_tup_upd        | 0
n_tup_del        | 0
n_tup_hot_upd    | 0
n_live_tup       | 10000000
n_dead_tup       | 0
last_vacuum      | 2008-08-06 16:29:23.153879-04
last_autovacuum  |
last_analyze     | 2008-08-06 16:29:23.153879-04
last_autoanalyze |

*** Running update to create dead tuples:

UPDATE accounts SET abalance = abalance +1;

****Stats after update:


sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+------------------------------
relid            | 114694
schemaname       | public
relname          | accounts
seq_scan         | 2
seq_tup_read     | 20000000
idx_scan         | 0
idx_tup_fetch    | 0
n_tup_ins        | 10000000
n_tup_upd        | 10000000
n_tup_del        | 0
n_tup_hot_upd    | 0
n_live_tup       | 10000000
n_dead_tup       | 10000000
last_vacuum      | 2008-08-06 16:29:23.153879-04
last_autovacuum  |
last_analyze     | 2008-08-06 16:29:23.153879-04
last_autoanalyze |


looking at pg_stat_activity, it shows as expected AUTOVAC started
vacuuming the table.

*** Restart the DB:

sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+---------
relid            | 114694
schemaname       | public
relname          | accounts
seq_scan         | 0
seq_tup_read     | 0
idx_scan         | 0
idx_tup_fetch    | 0
n_tup_ins        | 0
n_tup_upd        | 0
n_tup_del        | 0
n_tup_hot_upd    | 0
n_live_tup       | 0
n_dead_tup       | 0
last_vacuum      |
last_autovacuum  |
last_analyze     |
last_autoanalyze |


and now autovac process does not start, as start are reseted.

Is this expected behavior ?

Also later i tryed following:


sample=# ANALYZE accounts ;
ANALYZE
sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+---------
relid            | 114694
schemaname       | public
relname          | accounts
seq_scan         | 0
seq_tup_read     | 0
idx_scan         | 0
idx_tup_fetch    | 0
n_tup_ins        | 0
n_tup_upd        | 0
n_tup_del        | 0
n_tup_hot_upd    | 0
n_live_tup       | 0
n_dead_tup       | 0
last_vacuum      |
last_autovacuum  |
last_analyze     |
last_autoanalyze |

note: when i did ANALYZE accounts ; stats did't get updated.

sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+---------
relid            | 114694
schemaname       | public
relname          | accounts
seq_scan         | 0
seq_tup_read     | 0
idx_scan         | 0
idx_tup_fetch    | 0
n_tup_ins        | 0
n_tup_upd        | 0
n_tup_del        | 0
n_tup_hot_upd    | 0
n_live_tup       | 0
n_dead_tup       | 0
last_vacuum      |
last_autovacuum  |
last_analyze     |
last_autoanalyze |

note: Upon doing ANALYZE verbose accounts;

sample=# ANALYZE verbose accounts;
INFO:  analyzing "public.accounts"
INFO:  "accounts": scanned 3000 of 317461 pages, containing 94185 live
rows and 60165 dead rows; 3000 rows in sample, 9966688 estimated total rows
ANALYZE
sample=# SELECT * from pg_stat_user_tables where relname='accounts';
-[ RECORD 1 ]----+------------------------------
relid            | 114694
schemaname       | public
relname          | accounts
seq_scan         | 0
seq_tup_read     | 0
idx_scan         | 0
idx_tup_fetch    | 0
n_tup_ins        | 0
n_tup_upd        | 0
n_tup_del        | 0
n_tup_hot_upd    | 0
n_live_tup       | 9966688
n_dead_tup       | 6366680
last_vacuum      |
last_autovacuum  |
last_analyze     | 2008-08-06 16:47:13.404946-04
last_autoanalyze |

note: stats got updated

Thanks in advance.

--
Chirag Dave  416-673-4102
Database Administrator, Afilias Canada Corp.
cdave@ca.afilias.info