Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_stat_statements_reset

pg_stat_statements_reset

From
Fabrice Chapuis
Date:
Hi,
This query seems not resetting stats for databae mydb

SELECT pg_stat_statements_reset(
    NULL,  -- userid: NULL means "all users"
    (SELECT oid FROM pg_database WHERE datname = 'mydb'),
    NULL   -- queryid: NULL means "all queries"
);
│ pg_stat_statements_reset │

(1 row)

Something wrong in the use of the function pg_stat_statements_reset?

Regards,

Fabrice

Re: pg_stat_statements_reset

From
Tom Lane
Date:
Fabrice Chapuis <fabrice636861@gmail.com> writes:
> This query seems not resetting stats for databae mydb

> SELECT pg_stat_statements_reset(
>     NULL,  -- userid: NULL means "all users"
>     (SELECT oid FROM pg_database WHERE datname = 'mydb'),
>     NULL   -- queryid: NULL means "all queries"
> );

Where did you get the idea that NULL means "all"?  AFAICS
that function is strict, meaning it won't run at all for
null input.

            regards, tom lane

Re: pg_stat_statements_reset

From
Guillaume Lelarge
Date:
Hello,

Le lun. 16 déc. 2024 à 16:47, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Fabrice Chapuis <fabrice636861@gmail.com> writes:
> This query seems not resetting stats for databae mydb

> SELECT pg_stat_statements_reset(
>     NULL,  -- userid: NULL means "all users"
>     (SELECT oid FROM pg_database WHERE datname = 'mydb'),
>     NULL   -- queryid: NULL means "all queries"
> );

Where did you get the idea that NULL means "all"?  AFAICS
that function is strict, meaning it won't run at all for
null input.


You should try:

select pg_stat_statements_reset(dbid => oid)
from pg_database
where datname='mydb';

Works on my test case (and I find it easier to understand, but that may be subjective).


--
Guillaume.

Re: pg_stat_statements_reset

From
Fabrice Chapuis
Date:
Thanks for your explanations Tom, Guillaume, your solution works fine for me too.

Regards

Fabrice

On Mon, Dec 16, 2024 at 4:56 PM Guillaume Lelarge <guillaume@lelarge.info> wrote:
Hello,

Le lun. 16 déc. 2024 à 16:47, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Fabrice Chapuis <fabrice636861@gmail.com> writes:
> This query seems not resetting stats for databae mydb

> SELECT pg_stat_statements_reset(
>     NULL,  -- userid: NULL means "all users"
>     (SELECT oid FROM pg_database WHERE datname = 'mydb'),
>     NULL   -- queryid: NULL means "all queries"
> );

Where did you get the idea that NULL means "all"?  AFAICS
that function is strict, meaning it won't run at all for
null input.


You should try:

select pg_stat_statements_reset(dbid => oid)
from pg_database
where datname='mydb';

Works on my test case (and I find it easier to understand, but that may be subjective).


--
Guillaume.