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