Thanks to the new pg_stat_xact_user_functions and
pg_stat_xact_user_tables views in 9.1, it will be possible to
automatically "sample" which functions uses which functions/tables to
generate a nice directional graph of the dependency tree, based on
recent real-life activity, excluding any unused relations/functions
not-in-use anymore. It's actually a feature to not include these, as
they make the graph a lot more complicated.
If you want a graph on the activity during Mondays between 2:30pm and
2:31pm, such a graph could easily be generated, or if you want it for
30 days (which would probably include a lot more edges in the graph),
it can also be generated. :-)
It would be quite easy to automatically inject some small code
snippets to the top and bottom of each user function, to get the diff
of select * from pg_stat_xact_user_functions and
pg_stat_xact_user_tables between the entry point of each function and
the exit point.
It would be a lot nicer if it would be possible to automatically let
PostgreSQL sample such data for you, providing nice system views with
information on the sampled data per function, allowing you to query it
and ask,
- What functions has funciton public.myfunc(int) called and what
tables has it inserted/selected/updated/deleted from since the last
time I resetted the
statistics?
Just an idea...
--
Best regards,
Joel Jacobson
Glue Finance