diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 0fef0ca..52ebc2d 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9525,6 +9525,13 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx frontend/backend protocol + + mem_usage + int8 + + The memory reserved in the backend for the prepared statement. + + diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c index b945b15..1b24254 100644 --- a/src/backend/commands/prepare.c +++ b/src/backend/commands/prepare.c @@ -35,6 +35,7 @@ #include "utils/builtins.h" #include "utils/snapmgr.h" #include "utils/timestamp.h" +#include "utils/plancache.h" /* @@ -704,7 +705,7 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es, /* * This set returning function reads all the prepared statements and - * returns a set of (name, statement, prepare_time, param_types, from_sql). + * returns a set of (name, statement, prepare_time, param_types, from_sql, mem_usage). */ Datum pg_prepared_statement(PG_FUNCTION_ARGS) @@ -734,7 +735,7 @@ pg_prepared_statement(PG_FUNCTION_ARGS) * build tupdesc for result tuples. This must match the definition of the * pg_prepared_statements view in system_views.sql */ - tupdesc = CreateTemplateTupleDesc(5, false); + tupdesc = CreateTemplateTupleDesc(6, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", TEXTOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement", @@ -745,6 +746,9 @@ pg_prepared_statement(PG_FUNCTION_ARGS) REGTYPEARRAYOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 5, "from_sql", BOOLOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "mem_usage", + INT8OID, -1, 0); + /* * We put all the tuples into a tuplestore in one scan of the hashtable. @@ -766,8 +770,8 @@ pg_prepared_statement(PG_FUNCTION_ARGS) hash_seq_init(&hash_seq, prepared_queries); while ((prep_stmt = hash_seq_search(&hash_seq)) != NULL) { - Datum values[5]; - bool nulls[5]; + Datum values[6]; + bool nulls[6]; MemSet(nulls, 0, sizeof(nulls)); @@ -777,6 +781,7 @@ pg_prepared_statement(PG_FUNCTION_ARGS) values[3] = build_regtype_array(prep_stmt->plansource->param_types, prep_stmt->plansource->num_params); values[4] = BoolGetDatum(prep_stmt->from_sql); + values[5] = Int8GetDatum((int64)CachedPlanMemoryUsage(prep_stmt->plansource)); tuplestore_putvalues(tupstore, tupdesc, values, nulls); } diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c index 0ad3e3c..291b3f3 100644 --- a/src/backend/utils/cache/plancache.c +++ b/src/backend/utils/cache/plancache.c @@ -1451,6 +1451,42 @@ CachedPlanGetTargetList(CachedPlanSource *plansource, } /* + * CachedPlanMemUsage: Return memory used by the CachedPlanSource + * + * Returns the malloced memory used by the two MemoryContexts in + * CachedPlanSource and (if available) the MemoryContext in the generic plan. + * Does not care for the free memory in those MemoryContexts because it is very + * unlikely that it is reused for anythink else anymore and can be considered + * dead memory anyway. Also the size of the CachedPlanSource struct is added. + * + * This function is used only for the pg_prepared_statements view to allow + * client applications to monitor memory used by prepared statements and to + * selects candidates for eviction in memory contraint environments with + * automatic preparation of often called queries. + */ +Size +CachedPlanMemoryUsage(CachedPlanSource *plan) +{ + MemoryContextCounters counters; + MemoryContext context; + + counters.totalspace = 0; + + context = plan->context; + context->methods->stats(context,NULL,NULL,&counters); + + context = plan->query_context; + context->methods->stats(context,NULL,NULL,&counters); + + if( plan->gplan ) { + context = plan->gplan->context; + context->methods->stats(context,NULL,NULL,&counters); + } + + return counters.totalspace; +} + +/* * QueryListGetPrimaryStmt * Get the "primary" stmt within a list, ie, the one marked canSetTag. * diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index c4fc50d..de0277e 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -7633,9 +7633,9 @@ { oid => '2510', descr => 'get the prepared statements for this session', proname => 'pg_prepared_statement', prorows => '1000', proretset => 't', provolatile => 's', proparallel => 'r', prorettype => 'record', - proargtypes => '', proallargtypes => '{text,text,timestamptz,_regtype,bool}', - proargmodes => '{o,o,o,o,o}', - proargnames => '{name,statement,prepare_time,parameter_types,from_sql}', + proargtypes => '', proallargtypes => '{text,text,timestamptz,_regtype,bool,int8}', + proargmodes => '{o,o,o,o,o,0}', + proargnames => '{name,statement,prepare_time,parameter_types,from_sql,mem_usage}', prosrc => 'pg_prepared_statement' }, { oid => '2511', descr => 'get the open cursors for this session', proname => 'pg_cursor', prorows => '1000', proretset => 't', diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h index ab20aa0..4517f6a 100644 --- a/src/include/utils/plancache.h +++ b/src/include/utils/plancache.h @@ -182,4 +182,6 @@ extern CachedPlan *GetCachedPlan(CachedPlanSource *plansource, QueryEnvironment *queryEnv); extern void ReleaseCachedPlan(CachedPlan *plan, bool useResOwner); +extern Size CachedPlanMemoryUsage(CachedPlanSource *plansource); + #endif /* PLANCACHE_H */ diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 7e2b194..e2abdaf 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1425,8 +1425,9 @@ pg_prepared_statements| SELECT p.name, p.statement, p.prepare_time, p.parameter_types, - p.from_sql - FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql); + p.from_sql, + p.mem_usage + FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql, mem_usage); pg_prepared_xacts| SELECT p.transaction, p.gid, p.prepared,