Re: Do we still need parent column in pg_backend_memory_context?

On Wed, 31 Jul 2024 at 05:19, Melih Mutlu <m.melihmutlu@gmail.com> wrote:
> After the patch [1] that adds a path column to pg_backend_memory_context, the parent context can also be found in the
patharray. Since there are currently two ways to retrieve information related to the parent of a context, I wonder
whetherwe still want to keep the parent column. 

My vote is to remove it.

I think the parent column is only maybe useful as a rough visual
indication of what the parent is.  It's dangerous to assume using it
is a reliable way to write a recursive query:

with recursive contexts as (
  select name, ident, level, path, parent from pg_backend_memory_contexts
),
c as (
  select path[level] as context_id, NULL::int as parent_id,* from
contexts where parent is null
  union all
  select c1.path[c1.level], c.context_id,c1.* from contexts c1 inner
join c on c.name = c1.parent
)
select count(*) as all_including_false_dups, count(distinct
context_id) as unique from c;

 all_including_false_dups | unique
--------------------------+--------
                      159 |    150

So, with the backend in the state I had it in during this query, the
recursive query shows 9 additional contexts because the recursive
query joining parent to name found a false parent with a name matching
the actual parent because the names are not unique. Given that I
didn't do anything special to create contexts with duplicate names, it
seems duplicates are not rare.

select name,count(*) from pg_backend_memory_contexts group by 1 order
by 2 desc limit 3;
    name     | count
-------------+-------
 index info  |    94
 dynahash    |    15
 ExprContext |     7
(3 rows)

I think the first two of the above won't have any children, but the
ExprContext ones can.

David