Re: Add privileges test for pg_stat_statements to improve coverage - Mailing list pgsql-hackers

From kuroda.keisuke@nttcom.co.jp
Subject Re: Add privileges test for pg_stat_statements to improve coverage
Date
Msg-id 0e19a9992c7bca5faf4b1baac17ddd09@nttcom.co.jp
Whole thread Raw
In response to Re: Add privileges test for pg_stat_statements to improve coverage  (Michael Paquier <michael@paquier.xyz>)
Responses Re: Add privileges test for pg_stat_statements to improve coverage
List pgsql-hackers
Hi Michael-san,
Thank you for your reply and comment!

attach v4 fixed patch.

> We have two entries here with the same query and the same query ID,
> because they have a different userid.  Shouldn't this query reflect
> this information rather than have the reader guess it?  This is going
> to require a join with pg_authid to grab the role name, and an ORDER
> BY on the role name.

I agree.
The information of different userids is mixed up.
It is easier to understand if the role name is displayed.
Join with pg_roles (view of pg_authid) to output the role name.

> I'd recommend to add a GROUP BY on calls and rows, with a
> count(query), rather than print the same row without the query text
> multiple times.

Indeed, same row have been output multiple times.
If we use GROUP BY, we would expect the following.

```
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, count(ss.query),
ss.calls, ss.rows
   FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
   GROUP BY r.rolname, queryid_bool, ss.calls, ss.rows
   ORDER BY r.rolname, count(ss.query), ss.calls, ss.rows;
        rolname       | queryid_bool | count | calls | rows
---------------------+--------------+-------+-------+------
  postgres            |              |     1 |     1 |    3
  postgres            |              |     2 |     1 |    1
  regress_stats_user1 | t            |     1 |     1 |    1
(3 rows)
```

However, in this test I would like to see '<insufficient permissions>'
output
and the SQL text 'SELECT $1+$2 AS “TWO”' executed by
regress_stats_user1.

The attached patch executes the following SQL.
What do you think?

```
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls,
ss.rows
   FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
   ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
        rolname       | queryid_bool |          query           | calls |
rows
---------------------+--------------+--------------------------+-------+------
  postgres            |              | <insufficient privilege> |     1 |
    1
  postgres            |              | <insufficient privilege> |     1 |
    1
  postgres            |              | <insufficient privilege> |     1 |
    3
  regress_stats_user1 | t            | SELECT $1+$2 AS "TWO"    |     1 |
    1
(4 rows)
```
Attachment

pgsql-hackers by date:

Previous
From: Masahiko Sawada
Date:
Subject: Re: xid_wraparound tests intermittent failure.
Next
From: Sutou Kouhei
Date:
Subject: Re: Separate HEAP WAL replay logic into its own file