On Wed, 3 Jul 2024 at 01:08, Melih Mutlu <m.melihmu...@gmail.com> wrote: > An example query to get total used bytes including children by using level > info would look like: > > WITH contexts AS ( > SELECT * FROM pg_backend_memory_contexts > ) > SELECT sum(total_bytes) > FROM contexts > WHERE path[( SELECT level+1 FROM contexts WHERE name = 'CacheMemoryContext')] > = > (SELECT path[level+1] FROM contexts WHERE name = 'CacheMemoryContext');
I've been wondering about the order of the "path" column. When we talked, I had in mind that the TopMemoryContext should always be at the end of the array rather than the start, but I see you've got it the other way around. With the order you have it, that query could be expressed as: WITH c AS (SELECT * FROM pg_backend_memory_contexts) SELECT c1.* FROM c c1, c c2 WHERE c2.name = 'CacheMemoryContext' AND c1.path[c2.level + 1] = c2.path[c2.level + 1]; Whereas, with the way I had in mind, it would need to look like: WITH c AS (SELECT * FROM pg_backend_memory_contexts) SELECT c1.* FROM c c1, c c2 WHERE c2.name = 'CacheMemoryContext' AND c1.path[c1.level - c2.level + 1] = c2.path[1]; I kind of think the latter makes more sense, as if for some reason you know the level and context ID of the context you're looking up, you can do: SELECT * FROM pg_backend_memory_contexts WHERE path[<known level> + level + 1] = <known context id>; I also imagined "path" would be called "context_ids". I thought that might better indicate what the column is without consulting the documentation. I think it might also be easier to document what context_ids is: "Array of transient identifiers to describe the memory context hierarchy. The first array element contains the ID for the current context and each subsequent ID is the parent of the previous element. Note that these IDs are unstable between multiple invocations of the view. See the example query below for advice on how to use this column effectively." There are also a couple of white space issues with the patch. If you're in a branch with the patch applied directly onto master, then "git diff master --check" should show where they are. If you do reverse the order of the "path" column, then I think modifying convert_path_to_datum() is the best way to do that. If you were to do it in the calling function, changing "path = list_delete_last(path);" to use list_delete_first() is less efficient. David
