Обсуждение: pg_stat_all_tables column value reseting problem.

Dear friends,

    I would like to know the particular table's insert,update and delete
record history.
I have done some googling and got to know about pg_stat_all_tables  and
pg_stat_user_tables. These tables having this below field , which has the
tables insert,update and delete history,

    n_tup_ins        | bigint  |
    n_tup_upd       | bigint  |
    n_tup_del        | bigint  |

But some time It was getting reset.. I don't know which scenario It is
happening.

Done the blow testing:

      => inserting,updating and deleting some record in a particular table
      => checked this pg_stat_all_tables  entry, It has displayed perfect
count of the history.
      => restarted the server for check the these
(n_tup_ins,n_tup_upd,n_tup_del) entries.
            but values weren't reseted, history remains.
      => Two days after I have checked this pg_stat_all_tables entry again,
but proper records wasn't there. Value of n_tup_ins,n_tup_upd and n_tup_del
was 0. I don't know why it has happened.

Please let me know the reason and which scenario it will get reset.
Whether I have to do any configuration related changes ?

Note:

     I am using PostgreSQL 8.3.7.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-stat-all-tables-column-value-reseting-problem-tp3767851p3767851.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Thanks for ur reply Ragu..

Yes, I have executed this select pg_stat_reset(); as a root user.
It has reseted the values.

But last time ( when I was posted this query) I didn't executed it.
But values were rested automatically.

I would like to know,

   => Whether postgres automatically running this  select pg_stat_reset();
statement ?
   => If it runs Which scenario its executing this statement and what is the
interval time between each execution ?

Please let me know..
Thanks..

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-stat-all-tables-column-value-reseting-problem-tp3767851p3933652.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
> owner@postgresql.org] On Behalf Of leslie samuel
> Sent: Friday, March 18, 2011 1:17 AM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] pg_stat_all_tables column value reseting problem.
>
> Thanks for ur reply Ragu..
>
> Yes, I have executed this select pg_stat_reset(); as a root user.
> It has reseted the values.
>
> But last time ( when I was posted this query) I didn't executed it.
> But values were rested automatically.
>
> I would like to know,
>
>    => Whether postgres automatically running this  select
> pg_stat_reset();
> statement ?
>    => If it runs Which scenario its executing this statement and what
> is the
> interval time between each execution ?
>
> Please let me know..
> Thanks..
>

If you stop/restart the database in immediate mode, or if it crashes the stats will be reset.  What mode did you use
forshutdown? 

Brad.

For stop/Restart the postmaster, I am using pg_ctlcluster --force command.

I have read about pg_ctlcluster in the net they are saying this below
things,

pg_ctlcluste With the --force option the "fast" mode is used which rolls
back all active transactions, disconnects clients immediately and thus shuts
down cleanly. If that does not work, shutdown is attempted again in
"immediate" mode, which can leave the cluster in an inconsistent state and
thus will lead to a recovery run at the next start.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-stat-all-tables-column-value-reseting-problem-tp3767851p4062822.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
> owner@postgresql.org] On Behalf Of leslie samuel
> Sent: Saturday, March 19, 2011 8:49 AM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] pg_stat_all_tables column value reseting problem.
>
> For stop/Restart the postmaster, I am using pg_ctlcluster --force
> command.
>
> I have read about pg_ctlcluster in the net they are saying this below
> things,
>
> pg_ctlcluste With the --force option the "fast" mode is used which
> rolls
> back all active transactions, disconnects clients immediately and thus
> shuts
> down cleanly. If that does not work, shutdown is attempted again in
> "immediate" mode, which can leave the cluster in an inconsistent state
> and
> thus will lead to a recovery run at the next start.

Check your DB logs.  If you are going through recovery on startup, that is your answer.

Brad.