Re: Access issue for system queries

[arun chirappurath]

Ok, I'll check it out. Thank you.

On Sat, 30 Mar, 2024, 10:36 Julien Rouhaud,  wrote:

> On Sat, Mar 30, 2024 at 12:47 PM arun chirappurath 
> wrote:
> >
> > I have granted access to pg_read_all_stats and pg_read_allsettings to
> user..still they are not able to receive results from this query.its
> empty..we can run SELECT * FROM pg_stat_statements alone..but not below
> statement..what could be the reason?
> >
> > WITH statements AS (
> > SELECT * FROM pg_stat_statements pss
> >  JOIN pg_roles pr ON (userid=oid)
> > WHERE rolname = current_user
> > )
> > SELECT calls,
> >min_exec_time,
> >max_exec_time,
> >mean_exec_time,
> >stddev_exec_time,
> >(stddev_exec_time/mean_exec_time) AS coeff_of_variance,
> >query
> > FROM statements
> > WHERE calls > 500
> > AND shared_blks_hit > 0
> > ORDER BY mean_exec_time DESC
> > LIMIT 10
>
> Probably because your current user didn't run any query more than 500
> times?  Or maybe because you have some other tools that calls
> pg_stat_statements_reset() frequently enough.
>

Re: Access issue for system queries

[Julien Rouhaud]

On Sat, Mar 30, 2024 at 12:47 PM arun chirappurath  wrote:
>
> I have granted access to pg_read_all_stats and pg_read_allsettings to 
> user..still they are not able to receive results from this query.its 
> empty..we can run SELECT * FROM pg_stat_statements alone..but not below 
> statement..what could be the reason?
>
> WITH statements AS (
> SELECT * FROM pg_stat_statements pss
>  JOIN pg_roles pr ON (userid=oid)
> WHERE rolname = current_user
> )
> SELECT calls,
>min_exec_time,
>max_exec_time,
>mean_exec_time,
>stddev_exec_time,
>(stddev_exec_time/mean_exec_time) AS coeff_of_variance,
>query
> FROM statements
> WHERE calls > 500
> AND shared_blks_hit > 0
> ORDER BY mean_exec_time DESC
> LIMIT 10

Probably because your current user didn't run any query more than 500
times?  Or maybe because you have some other tools that calls
pg_stat_statements_reset() frequently enough.

Access issue for system queries

[arun chirappurath]

Dear all,

I have granted access to pg_read_all_stats and pg_read_allsettings to
user..still they are not able to receive results from this query.its
empty..we can run SELECT * FROM pg_stat_statements alone..but not below
statement..what could be the reason?

WITH statements AS (
SELECT * FROM pg_stat_statements pss
 JOIN pg_roles pr ON (userid=oid)
WHERE rolname = current_user
)
SELECT calls,
   min_exec_time,
   max_exec_time,
   mean_exec_time,
   stddev_exec_time,
   (stddev_exec_time/mean_exec_time) AS coeff_of_variance,
   query
FROM statements
WHERE calls > 500
AND shared_blks_hit > 0
ORDER BY mean_exec_time DESC
LIMIT 10

Regards,
Arun