Add query or API to see what queries are running in the cluster

[pauldix]

We need visibility into what queries are running in the cluster on which nodes and for how long. This should be a new query type like:

list running queries

Which return a table of results like:

[
  {
    "name":"running queries",
    "columns": ["id", "start_time", "host", "query", "running_time", "user"],
    "points" : [...]
  }
]

[freeformz]

See also: http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW for insipration.

[Dieterbe]

brainstorm, not sure if this idea is useful at all.
but could also go into the systems database, or just look and feel like a database but actually isn't one.
and the query could be select query from queries where type=running

[pauldix]

Some additional thoughts on implementing this. It's going to be very tricky in the clustered setup. I would expect some sort of new state object that tracks the running queries on a server.

When a query comes into a server, it should have an id assigned. The protocol should be updated to track this id and send to the servers that run it locally.

The originatingServerId + queryId tuple should be enough to identify any query running in the cluster.

When a query comes in to list the running queries, you'll have to hit every server in the cluster to get the answer. If the query language is updated to handle this, then it can just be passed as a query to every server. Then have the coordinator intercept the query and pass off info from the query state object.

When a kill is sent, it should go to the originating server, which will then call out to all the other servers that it had sent the query to.

It doesn't make sense to push this stuff through Raft. It doesn't need to be replicated and besides, doing that would cause the number of queries running in the cluster to not be scalable.

Hope any of that makes sense/helps.

[tsenart]

Thanks for the help @pauldix. As I understood it, the requirements can be logically decomposed as such:

• Each server must hold state on it's running queries ids and their respective running durations.
• Each query must have a globally unique id, assigned by its coordinator[0]. The relevant parts of the communication protocol must be updated to include this id in the payload.
• Each server must respond to requests to return its state of running queries as well as to scatter and gather this request to the cluster.

[0] Please excuse the possible misleading nomenclature. Coordinator here means the server which the client communicates with. Correct me if there is a better name.

[jsternberg]

Closing in favor of #5939.