Re: Parent/child context relation in pg_get_backend_memory_contexts()

Hi,

On 2023-10-18 15:53:30 -0400, Stephen Frost wrote:
> > Here how pg_backend_memory_contexts would look like with this patch:
> >
> > postgres=# SELECT name, id, parent, parent_id, path
> > FROM pg_backend_memory_contexts
> > ORDER BY total_bytes DESC LIMIT 10;
> > name | id | parent | parent_id | path
> > -------------------------+-----+------------------+-----------+--------------
> > CacheMemoryContext | 27 | TopMemoryContext | 0 | {0}
> > Timezones | 124 | TopMemoryContext | 0 | {0}
> > TopMemoryContext | 0 | | |
> > MessageContext | 8 | TopMemoryContext | 0 | {0}
> > WAL record construction | 118 | TopMemoryContext | 0 | {0}
> > ExecutorState | 18 | PortalContext | 17 | {0,16,17}
> > TupleSort main | 19 | ExecutorState | 18 | {0,16,17,18}
> > TransactionAbortContext | 14 | TopMemoryContext | 0 | {0}
> > smgr relation table | 10 | TopMemoryContext | 0 | {0}
> > GUC hash table | 123 | GUCMemoryContext | 122 | {0,122}
> > (10 rows)
> >
> > An example query to calculate the total_bytes including its children for a
> > context (say CacheMemoryContext) would look like this:
> >
> > WITH contexts AS (
> > SELECT * FROM pg_backend_memory_contexts
> > )
> > SELECT sum(total_bytes)
> > FROM contexts
> > WHERE ARRAY[(SELECT id FROM contexts WHERE name = 'CacheMemoryContext')] <@
> > path;
>
> I wonder if we should perhaps just include
> "total_bytes_including_children" as another column? Certainly seems
> like a very useful thing that folks would like to see.

The "issue" is where to stop - should we also add that for some of the other
columns? They are a bit less important, but not that much.

> > We still need to use cte since ids are not persisted and might change in
> > each run of pg_backend_memory_contexts. Materializing the result can
> > prevent any inconsistencies due to id change. Also it can be even good for
> > performance reasons as well.
>
> I don't think we really want this to be materialized, do we? Where this
> is particularly interesting is when it's being dumped to the log ( ...
> though I wish we could do better than that and hope we do in the future)
> while something is ongoing in a given backend and if we do that a few
> times we are able to see what's changing in terms of allocations,
> whereas if we materialized it (when? transaction start? first time
> it's asked for?) then we'd only ever get the one view from whenever the
> snapshot was taken.

I think the comment was just about the need to use a CTE, because self-joining
with divergent versions of pg_backend_memory_contexts would not always work
out well.

Greetings,

Andres Freund