Hi hackers, pg_get_backend_memory_contexts() (and pg_backend_memory_contexts view) does not display parent/child relation between contexts reliably. Current version of this function only shows the name of parent context for each context. The issue here is that it's not guaranteed that context names are unique. So, this makes it difficult to find the correct parent of a context.
How can knowing the correct parent context be useful? One important use-case can be that it would allow us to sum up all the space used by a particular context and all other subcontexts which stem from that context. Calculating this sum is helpful since currently (total/used/free)_bytes returned by this function does not include child contexts. For this reason, only looking into the related row in pg_backend_memory_contexts does not help us to understand how many bytes that context is actually taking. Simplest approach to solve this could be just adding two new fields, id and parent_id, in pg_get_backend_memory_contexts() and ensuring each context has a unique id. This way allows us to build a correct memory context "tree". Please see the attached patch which introduces those two fields. Couldn't find an existing unique identifier to use. The patch simply assigns an id during the execution of pg_get_backend_memory_contexts() and does not store those id's anywhere. This means that these id's may be different in each call. With this change, here's a query to find how much space used by each context including its children: > WITH RECURSIVE cte AS ( > SELECT id, total_bytes, id as root, name as root_name > FROM memory_contexts > UNION ALL > SELECT r.id, r.total_bytes, cte.root, cte.root_name > FROM memory_contexts r > INNER JOIN cte ON r.parent_id = cte.id > ), > memory_contexts AS ( > SELECT * FROM pg_backend_memory_contexts > ) > SELECT root as id, root_name as name, sum(total_bytes) > FROM cte > GROUP BY root, root_name > ORDER BY sum DESC; You should see that TopMemoryContext is the one with highest allocated space since all other contexts are simply created under TopMemoryContext. Also; even though having a correct link between parent/child contexts can be useful to find out many other things as well by only writing SQL queries, it might require complex recursive queries similar to the one in case of total_bytes including children. Maybe, we can also consider adding such frequently used and/or useful information as new fields in pg_get_backend_memory_contexts() too. I appreciate any comment/feedback on this. Thanks, -- Melih Mutlu Microsoft
0001-Adding-id-parent_id-into-pg_backend_memory_contexts.patch
Description: Binary data
