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