Re: keeping a timestamp of the last stats reset (for a db, table and function)

Hello

you have to respect pg coding style:

a) not too long lines
b) not C++ line comments

Zdar

Pavel

2010/12/11 <tv(at)fuzzy(dot)cz>:
> Hi everyone,
>
> I just wrote my first patch, and I need to know whether I missed something
> or not. I haven't used C for a really long time, so sickbags on standby,
> and if you notice something really stupid don't hesitate to call me an
> asshole (according to Simon Phipps that proves we are a healthy open
> community).
>
> So what the patch does (or should do)? It tracks when were the stats for a
> given object (database, table or function) reset for the last time. This
> is useful when you do snapshots of the stats for analysis - when comparing
> two snapshots, you have to know whether the stats were reset (in that case
> the analysis usually yields random noise and automatic tools get confused
> by this).
>
> Tom Lane already recommended a workaround - firing the DBA who randomly
> resets statistics, but that's not a good solution I think. First, you have
> to be superior to the DBA to be able to fire him ;-) Second, everyone
> makes a mistake from time to time. Third, when there are functions to
> reset stats, it's nice to provide such info as it makes life much easier.
>
> And there are cases when you don't reset the stats explicitly but the data
> are actually gone - e.g. when after a restore or upgrade (OK, this is
> solvable using pg_postmaster_start_time).
>
> In short, I think it's a useful feature (I need it and I think there are
> others). And I think it's not disruptive.
>
> So what the patch actually does:
>
> - extends PgStat_StatDBEntry, PgStat_StatTableEntry and
> PgStat_StatFuncEntry with a new field (stat_reset_timestamp)
>
> - adds functions to read current value from these fields
> (pg_stat_get_db_last_stat_reset_time, pg_stat_get_last_stat_reset_time and
> pg_stat_get_function_last_stat_reset_time)
>
> - extends the system views with calls to these functions
> (pg_stat_database, pg_stat_user_functions and pg_stat_all_tables)
>
> The values are set like this:
>
> - when a database is created, current timestamp is stored in
> PgStat_StatDBEntry.stat_reset_timestamp
> - by default all tables/functions inherit this timestamp
> - when stats for a given table / function are reset, current timestamp is
> stored in the stat_reset_timestamp (this happens in
> pgstat_recv_resetsinglecounter)
> - when stats for the whole database are reset, everything starts from
> scratch (this happens in pgstat_recv_resetcounter)
>
> What I'm not sure about:
>
> - I really am not sure about the changes made in pg_proc.h. I'm not sure
> how to assign OIDs to the new functions (I've simply chosen values that
> are were not used in this file), and I'm not sure about the other columns
> (I've copied and modified another function with the same parameter/return
> types)
>
> - I'm not sure if there are any other ways how the stat entries can be
> created. I've found two ways - directly (when asked for the stats e.g.
> from pgstat_get_tab_entry), and indirectly (when processing stats from a
> backend e.g. in pgstat_recv_tabstat).
>
> regards
> Tomas
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>