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

On Fri, Nov 16, 2018 at 11:00 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
wrote:

> On Fri, Nov 16, 2018 at 9:43 AM Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
> wrote:
> >
> > On Thu, Nov 15, 2018 at 5:18 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
> >>
> >> > +--
> >> > +-- remove query ('SELECT $1 + $2 AS "TWO"') executed by
> >> > regress_stats_user2 in the current_database
> >> > +--
> >> > +SELECT pg_stat_statements_reset((SELECT r.oid FROM pg_roles AS r
> >> > WHERE r.rolname = 'regress_stats_user2'),
> >> > + (SELECT d.oid from pg_database As d where datname =
> current_database()),
> >> > + (SELECT s.queryid FROM pg_stat_statements AS s WHERE s.query =
> >> > 'SELECT $1 AS "ONE"'));
> >> >
> >> > The query in comments is different than what is actually used? And
> >> > how is able to remove the correct statement from hash (it seems you
> >> > intended to remove 'SELECT $1 AS "ONE"', but it removed 'SELECT $1 +
> >> > $2 AS "TWO"')?
> >> >
> >>
> >> One more point, the length of each line is too long in this statement,
> >> try to reduce it by starting parameters for pg_stat_statements_reset
> >> from next line or something like that.
> >
> >
> > Sorry for the mistake.
> > Attached patch synced the comment and SQL statement.
>
> Okay, but you haven't answered my question:
> "how is able to remove the correct statement from hash (it seems
> statement intended to remove 'SELECT $1 AS "ONE"', but it removed
> 'SELECT $1 + $2 AS "TWO"')"?
>

I missed to check that question.

SELECT s.queryid FROM pg_stat_statements AS s WHERE s.query =
'SELECT $1 AS "ONE"'

With the above query to get the queryid, but there are no such queries
present in the pg_stat_statements, so the above query returns NULL as
output, and with NULL as input to the _reset() function, it takes the
default
value of 0, and the reset query compares with rest of the two parameters
of userid and dbid. There is only one query that is present with that
combination, and that query is deleted.

In order to avoid such wrong results, i moved this as the first reset query,
so that there will be many queries that satisfy the condition in case if
such
mistakes happens.

Regards,
Haribabu Kommi
Fujitsu Australia