Re: Parent/child context relation in pg_get_backend_memory_contexts()

On Wed, 3 Jul 2024 at 01:08, Melih Mutlu <m.melihmutlu@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