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

On Sat, Jun 23, 2018 at 7:36 AM, Haribabu Kommi
<kommi(dot)haribabu(at)gmail(dot)com> wrote:
> On Sat, Jun 23, 2018 at 5:45 AM Euler Taveira <euler(at)timbira(dot)com(dot)br> wrote:
>>
>> 2018-06-22 12:06 GMT-03:00 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> > On Wed, Jun 20, 2018 at 10:19 AM, Euler Taveira <euler(at)timbira(dot)com(dot)br>
>> > wrote:
>> >> 2018-06-20 4:30 GMT-03:00 Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>:
>> >>> Attached is a simple patch with implementation. Comments?
>> >>>
>> >> Why don't you extend the existing function pg_stat_statements_reset()?
>> >
>> > Well, the existing function doesn't take any arguments. We could add
>> > an additional version of it that takes an argument, or we could
>> > replace the existing version with one that has an optional argument.
>> > But are either of those things any better than just adding a new
>> > function with a different name, like
>> > pg_stat_statements_reset_statement()?
>> >
>> From the user's POV, overloading is a good goal. It is better to
>> remember one function name than 3 different function names to do the
>> same task (reset statement statistics).
>
>
> I agree that function overloading is beneficial until unless it doesn't
> introduce
> confusion and difficulty in using it.
>
>
>> > I have not had such good experiences with function overloading, either
>> > in PostgreSQL or elsewhere, that I'm ready to say reusing the same
>> > name is definitely the right approach. For example, suppose we
>> > eventually end up with a function that resets all the statements, a
>> > function that resets just one statement, a function that resets all
>> > statements for one user, and a function that resets all statements
>> > where the statement text matches a certain regexp. If those all have
>> > separate names, everything is fine. If they all have the same name,
>> > there's no way that's not confusing.
>> >
>> I think part of your frustration with overloading is because there are
>> so many arguments and/or argument type combinations to remember.
>> Frankly, I never remember the order / type of arguments when a
>> function has more than 2 arguments (unless I use that function a lot).
>> If we want to consider overloading I think we should put all
>> possibilities for that function on the table and decide between
>> overload it or not. Bad overloading decisions tend to be very
>> frustrating for the user. In this case, all possibilities (queryid,
>> user, regex, database, statement regex) can be summarized as (i) 0
>> arguments that means all statements and (ii) 1 argument (queryid is
>> unique for all statements) -- because queryid can be obtain using
>> SELECT pg_stat_statements_reset(queryid) FROM pg_stat_statements WHERE
>> my-condition-here.
>
>
> In pg_stat_statements the uniqueness of the query is decided by (userid,
> dbid and queryid).
> I feel the reset function should take maximum of 3 input arguments and rest
> of the
> conditions can be filtered using the WHERE condition.
>

I'm slightly confused about the function overloading part here (not
sure whether we should try using the same function name or introduce a
function with new name), however, I think, passing userid, dbid and
queryid as input arguments to the user function (may be
pg_stat_statements_reset_statement or pg_stat_statements_reset) looks
like a good option as it would allow users to remove an entry for a
particular query across the databases not just in the database that
the user is currently connected to. The current patch definitely lacks
this flexibility.

> if we are going to support a single function that wants to reset all the
> statement
> statistics of a "user" or specific to a "database" and "specific queries"
> based on
> other input parameter values, I am not sure that the overloading function
> can cause
> confusion in using it? And also if the specified input query data doesn't
> found, better
> to ignore or raise an error?
>
> Regards,
> Haribabu Kommi
> Fujitsu Australia