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)
```