Re: New SQL counter statistics view (pg_stat_sql)

On Thu, Sep 15, 2016 at 6:01 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Sep 2, 2016 at 2:33 AM, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
> wrote:
> > On Wed, Aug 31, 2016 at 3:19 AM, Alvaro Herrera <
> alvherre(at)2ndquadrant(dot)com>
> > wrote:
> >> Haribabu Kommi wrote:
> >>
> >>> Apart from the above, here are the following list of command tags that
> >>> are generated in the code, I took only the first word of the command
> tag
> >>> just to see how many categories present. The number indicates the
> >>> subset of operations or number of types it is used. Like create table,
> >>> create function and etc.
> >>
> >> Sounds about right. I suppose all those cases that you aggregated here
> >> would expand to full tags in the actual code. I furthermore suppose
> >> that some of these could be ignored, such as the transaction ones and
> >> things like load, lock, move, fetch, discard, deallocate (maybe lump
> >> them all together under "other", or some other rough categorization, as
> >> Tom suggests).
> >
> > Following is the pg_stat_sql view with the SQL categories that I
> considered
> > that are important. Rest of the them will be shown under others category.
> >
> > postgres=# \d pg_stat_sql
> > View "pg_catalog.pg_stat_sql"
> > Column | Type | Modifiers
> > -----------------+--------------------------+-----------
> > inserts | bigint |
> > deletes | bigint |
> > updates | bigint |
> > selects | bigint |
> > declare_cursors | bigint |
> > closes | bigint |
> > creates | bigint |
> > drops | bigint |
> > alters | bigint |
> > imports | bigint |
> > truncates | bigint |
> > copies | bigint |
> > grants | bigint |
> > revokes | bigint |
> > clusters | bigint |
> > vacuums | bigint |
> > analyzes | bigint |
> > refreshs | bigint |
> > locks | bigint |
> > checkpoints | bigint |
> > reindexes | bigint |
> > deallocates | bigint |
> > others | bigint |
> > stats_reset | timestamp with time zone |
> >
> >
> > If any additions/deletions, I can accommodate them.
>
> I think it is not a good idea to make the command names used here the
> plural forms of the command tags. Instead of "inserts", "updates",
> "imports", etc. just use "INSERT", "UPDATE", "IMPORT". That's simpler
> and less error prone - e.g. you won't end up with things like
> "refreshs", which is not a word.

Thanks for your suggestion. I also thought of changing the name while
writing
"refreshs" as a column name of the view originally. A small restriction
with the
change of names to command names is that, user needs to execute the query
as follows.

select pg_stat_sql.select from pg_stat_sql;

Updated patch is attached with the corrections. Apart from name change,
added documentation and tests.

Regards,
Hari Babu
Fujitsu Australia