On Thu, 11 Jul 2024 at 09:19, Robert Haas <robertmh...@gmail.com> wrote: > FWIW, I would have done what Melih did. A path normally is listed in > root-to-leaf order, not leaf-to-root.
Melih and I talked about this in a meeting yesterday evening. I think I'm about on the fence about having the IDs in leaf-to-root or root-to-leaf. My main concern about which order is chosen is around how easy it is to write hierarchical queries. I think I'd feel better about having it in root-to-leaf order if "level" was 1-based rather than 0-based. That would allow querying CacheMemoryContext and all of its descendants with: 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] = c2.path[c2.level]; (With the v6 patch, you have to do level + 1.) Ideally, no CTE would be needed here, but unfortunately, there's no way to know the CacheMemoryContext's ID beforehand. We could make the ID more stable if we did a breadth-first traversal of the context. i.e., assign IDs in level order. This would stop TopMemoryContext's 2nd child getting a different ID if its first child became a parent itself. This allows easier ad-hoc queries, for example: select * from pg_backend_memory_contexts; -- Observe that CacheMemoryContext has ID=22 and level=2. Get the total of that and all of its descendants. select sum(total_bytes) from pg_backend_memory_contexts where path[2] = 22; -- or just it and direct children select sum(total_bytes) from pg_backend_memory_contexts where path[2] = 22 and level <= 3; Without the breadth-first assignment of context IDs, the sum() would cause another context to be created for aggregation and the 2nd query wouldn't work. Of course, it doesn't make it 100% guaranteed to be stable, but it's way less annoying to write ad-hoc queries. It's more stable the closer to the root you're interested in, which seems (to me) the most likely area of interest for most people. > On Fri, Jul 5, 2024 at 4:06 AM David Rowley <dgrowle...@gmail.com> wrote: > > I also imagined "path" would be called "context_ids". I thought that > > might better indicate what the column is without consulting the > > documentation. > > The only problem I see with this is that it doesn't make it clear that > we're being shown parentage or ancestry, rather than values for the > current node. I suspect path is fairly understandable, but if you > don't like that, what about parent_ids? I did a bit more work in the attached. I changed "level" to be 1-based and because it's the column before "path" I find it much more intuitive (assuming no prior knowledge) that the "path" column relates to "level" somehow as it's easy to see that "level" is the same number as the number of elements in "path". With 0-based levels, that's not the case. Please see the attached patch. I didn't update any documentation. David
v8_add_path_to_pg_backend_memory_contexts.patch
Description: Binary data
