SQL Statment is "-"

[dcop7]

Hello,
In some cases, the query statement in some dashboards shows "-" instead of the SQL statement itself.
For an example, the "Single query details", in the SQL, the dashboard query is:

select
  tag_data->>'query' as "Value"
from stat_statements
where dbname = '$dbname' and tag_data @> '{"queryid": "$queryid"}'
order by time desc
limit 1

But since the limit 1 it will only return the first value, sometimes it returns "-" instead of the actual SQL statement. The SQL statement is in the 2nd or 3rd result of the query. There should be a filter to remove these '-', like so:

select
  tag_data->>'query' as "Value"
from stat_statements
where dbname = '$dbname' and tag_data @> '{"queryid": "$queryid"}' and tag_data->>'query' <> '-'
order by time desc
limit 1

Just need to add: and tag_data->>'query' <> '-'

[pashagolub]

Thanks for your request.

Would you please provide us with the screenshot and software participated (Influx vs Postgres, Grafana version, etc.)

[dcop7]

Grafana: version 6.7.4
Using Postgres

[RachadAbiChahine]

for the "-" in my case I got this when the get_statement does not have the right to get the actual query ( as its done by another user per exemple )

[dcop7]

But for me, it is due to limit 1, the first result is "-", the second result has the actual query.

[markwort]

@dcop7 are the queryid and users identical for all results?
you've blacked them out from your screenshot, perhaps you can provide a md5 hashed version of the query results?

[dcop7]

It is the same query ID, one has "-", another has the full query.

[dcop7]

When I click in the Query ID, it opens the Single query details.
The SQL shows "-". But if I press F5 a few times, it eventual show the SQL statement instead of "-"

[markwort]

Ok, that doesn't help me understand this issue.

Again, please show the username or userid along side the list of queryids.

[dcop7]

The rows 1 and 3 has the same query ID, but in the first one the query is "-" and the row 3 it has the full statment.

[markwort]

ok, thank you for confirming that...
Do you have direct access to the database where the metrics are stored, so that you could check some more things?

[dcop7]

Yes, I think so. What do you need?

[markwort]

I think there are a few possible cases:

• you have multiple primaries and replicas and pgwatch2 is seing some of the query texts on one of them, but not on the other, due to some issues involving the facts that pg_stat_statement data is not replicated and the query strings might not be present, but the rest might, because they are located in different files. That is probably the more unlikely one.
• there have been points in time where the outcome of the query showed no text for the query column, due to missing privileges, but now it does.
• you have different database clusters reporting under the same name, that have similar user and query id by chance (can happen when the bootstrap procedure for creating users and tables from a "clean" cluster runs in exactly the same order), and those different clusters have different privileges (some allow accessing the query field and some don't).

So you'd have to do a little digging based on timestamps of the entries, check where exactly those entries are being sent from etc.

Sorry that I can't help more specifically right now.

All in all I do believe that this is a configuration issue and not a general one.
Your proposed fix should be fine, but since I don't think this is necessary to fix for most users, I would leave it as is for now.
You're free to adapt your dashboards in the meantime, of course...

Best regards
Julian

[kmoppel-cognite]

@dcop7 What metrics config are you using? Can it be that "stat_statements_no_query_text" and normal "stat_statements" metrics both are activated? That would explain it...

[dcop7]

Hello @kmoppel-cognite,
Yes, that is it! I have both active. Thanks for that!