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

On 23/11/2018 00:09, Haribabu Kommi wrote:
> 
> On Fri, Nov 23, 2018 at 1:54 AM Peter Eisentraut
>  > 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