Re: Parent/child context relation in pg_get_backend_memory_contexts()

On Thu, 11 Jul 2024 at 09:19, Robert Haas <robertmhaas(at)gmail(dot)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 <dgrowleyml(at)gmail(dot)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