Re: Adding column "mem_usage" to view pg_prepared_statements - Mailing list pgsql-hackers

Hello Andres,

how do you want to generalize it? Are you thinking about a view solely for the display of the memory usage of different
objects?Like functions or views (that also have a plan associated with it, when I think about it)? While being
interestingI still believe monitoring the mem usage of prepared statements is a bit more important than that of other
objectsbecause of how they change memory consumption of the server without using any DDL or configuration options and I
amnot aware of other objects with the same properties, or are there some? And for the other volatile objects like
tablesand indexes and their contents PostgreSQL already has it's information functions.  

Regardless of that here is the patch for now. I didn't want to fiddle to much with MemoryContexts yet, so it still
doesn'trecurse in child contexts, but I will change that also when I try to build a more compact MemoryContext
implementationand see how that works out. 

Thanks for pointing out the relevant information in the statement column of the view.

Regards,
Daniel Migowski

-----Ursprüngliche Nachricht-----
Von: Andres Freund <andres@anarazel.de>
Gesendet: Samstag, 27. Juli 2019 21:12
An: Daniel Migowski <dmigowski@ikoffice.de>
Cc: pgsql-hackers@lists.postgresql.org
Betreff: Re: Adding column "mem_usage" to view pg_prepared_statements

Hi,

On 2019-07-27 18:29:23 +0000, Daniel Migowski wrote:
> I just implemented a small change that adds another column "mem_usage"
> to the system view "pg_prepared_statements". It returns the memory
> usage total of CachedPlanSource.context,
> CachedPlanSource.query_content and if available
> CachedPlanSource.gplan.context.

FWIW, it's generally easier to comment if you actually provide the patch, even if it's just POC, as that gives a better
handleon how much additional complexity it introduces. 

I think this could be a useful feature. I'm not so sure we want it tied to just cached statements however - perhaps we
oughtto generalize it a bit more. 


Regarding the prepared statements specific considerations: I don't think we ought to explicitly reference
CachedPlanSource.query_content,and CachedPlanSource.gplan.context. 

In the case of actual prepared statements (rather than oneshot plans) CachedPlanSource.query_context IIRC should live
underCachedPlanSource.context.  I think there's no relevant cases where gplan.context isn't a child of
CachedPlanSource.contexteither, but not quite sure. 

Then we ought to just include child contexts in the memory computation (cf. logic in MemoryContextStatsInternal(),
althoughyou obviously wouldn't need all that). That way, if the cached statements has child contexts, we're going to
stayaccurate. 


> Also I wonder why the "prepare test as" is part of the statement
> column. I isn't even part of the real statement that is prepared as
> far as I would assume. Would prefer to just have the "select *..." in
> that column.

It's the statement that was executed. Note that you'll not see that in the case of protocol level prepared statements.
Itwill sometimes include relevant information, e.g. about the types specified as part of the prepare (as in PREPARE
foo(int,float, ...) AS ...). 

Greetings,

Andres Freund