Is there a way that we can show information about nested queries in pg_stat_activity? It's often inconvenient for users when somebody's executing a function and it doesn't seem to be finishing as quickly as anticipated. You can't really tell where in that function things broke down. There are a couple of possible ways to attack this problem, but the one that I like best is to just try to advertise the query text of all the nested queries that are in progress rather than only the top-level query. This runs up against the problem that we have only a fixed-length buffer with which to work, but that doesn't seem like a huge problem: if the outer query fills the buffer, nested queries won't be advertised. If not, the first level of nested query can be advertised using the space remaining. If there's still space left, this can be repeated for multiple levels of nested queries until we run out of bytes. This requires some way of separating one query string from the next, but that seems like a pretty solvable problem. It also requires figuring out how this would show up in the output of pg_stat_activity, which I'm not quite sure about. And it might have performance issues too, for some use cases, but it could be an optional feature, so that people who don't want to pay the cost of updating the pg_stat_activity information more frequently do not need to do so.
I'm curious to hear if other people agree that this is a problem, what they think about the above ideas for improving things, and if they've got any other suggestions. Thanks, -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
