Re: New function pg_stat_statements_reset_query() to reset statistics of a specific query

On 23/11/2018 00:09, Haribabu Kommi wrote:
>
> On Fri, Nov 23, 2018 at 1:54 AM Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com
> <mailto:peter(dot)eisentraut(at)2ndquadrant(dot)com>> wrote:
>
> On 19/11/2018 06:18, Haribabu Kommi wrote:
> > Amit suggested another option in another mail, so total viable 
> > solutions that are discussed as of now are,
> >
> > 1. Single API with NULL input treat as invalid value
> > 2. Multiple API to deal with NULL input of other values
> > 3. Single API with NULL value to treat them as current user, current
> > database
> >  and NULL queryid.
> > 4. Single API with -1 as invalid value, treat NULL as no matching.
> (Only
> > problem
> >  with this approach is till now -1 is also a valid queryid, but
> setting
> > -1 as queryid
> > needs to be avoided.
>
> Can you show examples of what these would look like?
>
>
> Following are some of the examples how the various options
> work.
>
> Option -1:
>
> select pg_stat_statements_reset(NULL,NULL,NULL);
> -- No change, just return. Because the function is strict.
>
> select pg_stat_statements_reset(10,10,NULL);
> -- Resets all the statements that are executed by userid 10 on database
> id 10.
>
> select pg_stat_statements_reset(NULL,10,NULL);
> -- Resets all the statements executed on database id 10

If the function is strict, none of these will do anything.

My preference is for NULL to mean *all* so this is my favorite option,
except that the first query should reset everything.

+1 with that change.

> Option - 2:
>
> select pg_stat_statements_reset(NULL,NULL,NULL);
> -- No change, just return. Function are of type strict.
>
> select pg_stat_statements_reset(10,10,1234)
> -- Resets the query statement 1234 executed by userid 10 on database id 10
>
> select pg_stat_statements_reset_by_userid(10)
> -- Resets all the statements executed by userid 10
>
> select pg_stat_statements_reset_by_dbid(10);
> -- Rests all the statements executed on database id 10.
>
> select pg_stat_statements_reset_by_userid_and_dbid(10, 10);
> -- Resets all the statements executed by userid 10 on datbase id 10
>
> Likewise total 7 API's.

I could live with this, but I prefer as described above.

I vote +0.

> Option -3:
>
> select pg_stat_statements_reset(NULL,NULL,NULL);
> --Similar like option-1, but when the userid or databaseid are NULL, it uses
> the current_role and current_database as the options. But for the queryid
> when the options is NULL, it deletes all the queries.
>
> Rest of the details are same as option-1.

I don't like this one at all. Something like resetting stats don't need
shortcuts and the dba can easily specify current_user and current_catalog.

-1 from me.

> Option -4:
>
> select pg_stat_statements_reset(NULL,NULL,NULL);
> -- No change, just return. Because the function is strict.
>
> select pg_stat_statements_reset(0,0,0);
> -- Resets all the statements
>
> select pg_stat_statements_reset(0,10,0);
> -- Resets all the statements executed on database id 10
>
> select pg_stat_statements_reset(10,0,0);
> -- Resets all the statements executed on userid 10.

This one is my very least favorite.

-1 from me.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support