Re: pg_plan_advice

On Tue, Mar 31, 2026 at 7:25 PM Lukas Fittl <lukas(at)fittl(dot)com> wrote:
>
> On Mon, Mar 30, 2026 at 7:53 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> >
> >
> > The module doesn't have a built-in way to do that right now. Are you
> > thinking we would document that pg_get_dsm_registry_allocations() can
> > be used?
>
> Yeah, for example. Alternatively we could provide a function/view that
> lists all advice across all stashes, so you can more easily see the
> result size of that and estimate what the in-memory use is. But
> pointing to pg_get_dsm_registry_allocations seems easier.

Actually, that won't work in practice with the code as of v23 -
pg_get_dsm_registry_allocations() always returns the fixed 64 byte
allocation from GetNamedDSMSegment, but is oblivious to the individual
DSA allocations (even after adding hundreds of entries):

SELECT * FROM pg_get_dsm_registry_allocations();

name | type | size
-----------------+---------+------
pg_stash_advice | segment | 64
(1 row)

Is there a reason you didn't use GetNamedDSA / GetNamedDSHash for the
other allocations? (which we have as of fe07100e82b09)

With the adjustments in the attached patch, this gets reported as expected:

SELECT * FROM pg_get_dsm_registry_allocations();

name | type | size
-----------------------+---------+-----------
pg_stash_advice | segment | 24
pg_stash_advice_stash | hash | 1048576
pg_stash_advice_dsa | area | 803209216
pg_stash_advice_entry | hash | 1048576
(4 rows)

>
> > > In practice for a good amount of our user base these days the question
> > > will be "Does my cloud provider give me access to create stash
> > > entries", so its maybe worth thinking about if we could also allow
> > > pg_maintain to manage entries by default?
> >
> > Wouldn't it make more sense for the cloud provider to grant execute
> > permissions on these functions to pg_maintain if they are so inclined?
> > This is a brand-new facility, so I think we had better be conservative
> > in terms of default permissions.
>
> I guess. I'm always worried that providers get that wrong and forget
> to give end users the permissions - but I suppose end users can
> complain to their providers if that's the case.
>
> I've done another look over pg_set_stashed_advice and I think its in
> good shape. The only trailing thought I have is that we could consider
> running a fuzzer against the pg_set_advice function in particular,
> just to see if anything pops up (beyond having the ability to make a
> very large memory allocation through a large advice string, which is
> maybe a problem?).

Obviously I meant "I've done another look over pg_stash_advice and I
think its in good shape".

I've done some basic fuzzing with the pg_set_stashed_advice function,
including concurrently setting advice, and that didn't yield any
surprises.

Thanks,
Lukas

--
Lukas Fittl