Re: Do we still need parent column in pg_backend_memory_context?

On Wed, 31 Jul 2024 at 05:19, Melih Mutlu <m(dot)melihmutlu(at)gmail(dot)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 path array. Since there are currently two ways to retrieve information related to the parent of a context, I wonder whether we 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