On 01.08.2019 19:56, Konstantin Knizhnik wrote:
On 31.07.2019 19:56, Heikki Linnakangas wrote:
On 09/07/2019 23:59, Konstantin Knizhnik wrote:
Fixed patch version of the path is attached.
Much of the patch and the discussion has been around the raw parsing,
and guessing which literals are actually parameters that have been
inlined into the SQL text. Do we really need to do that, though? The
documentation mentions pgbouncer and other connection poolers, where
you don't have session state, as a use case for this. But such
connection poolers could and should still be using the extended query
protocol, with Parse+Bind messages and query parameters, even if they
don't use named prepared statements. I'd want to encourage
applications and middleware to use out-of-band query parameters, to
avoid SQL injection attacks, regardless of whether they use prepared
statements or cache query plans. So how about dropping all the raw
parse tree stuff, and doing the automatic caching of plans based on
the SQL string, somewhere in the exec_parse_message? Check the
autoprepare cache in exec_parse_message(), if it was an "unnamed"
prepared statement, i.e. if the prepared statement name is an empty
string.
(I'm actually not sure if exec_parse/bind_message is the right place
for this, but I saw that your current patch did it in
exec_simple_query, and exec_parse/bind_message are the equivalent of
that for the extended query protocol).
- Heikki
I decided to implement your proposal. Much simple version of
autoprepare patch is attached.
At my computer I got the following results:
pgbench -M simple -S 22495 TPS
pgbench -M extended -S 47633 TPS
pgbench -M prepared -S 47683 TPS
So autoprepare speedup execution of queries sent using extended
protocol more than twice and it is almost the same as with explicitly
prepared statements.
I failed to create regression test for it because I do not know how to
force psql to use extended protocol. Any advice is welcome.
Slightly improved and rebased version of the patch.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 0000000..c4d96e6
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+<!-- doc/src/sgml/autoprepare.sgml -->
+
+ <chapter id="autoprepare">
+ <title>Autoprepared statements</title>
+
+ <indexterm zone="autoprepare">
+ <primary>autoprepared statements</primary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> makes it possible <firstterm>prepare</firstterm>
+ frequently used statements to eliminate cost of their compilation
+ and optimization on each execution of the query. On simple queries
+ (like ones in <filename>pgbench -S</filename>) using prepared statements
+ increase performance more than two times.
+ </para>
+
+ <para>
+ Unfortunately not all database applications are using prepared statements
+ and, moreover, it is not always possible. For example, in case of using
+ <productname>pgbouncer</productname> or any other session pooler,
+ there is no session state (transactions of one client may be executed at different
+ backends) and so prepared statements can not be used.
+ </para>
+
+ <para>
+ Autoprepare mode allows to overcome this limitation.
+ In this mode Postgres stores plans for all queries pass using extended protocol.
+ Speed of execution of autoprepared statements is almost the same as of explicitly prepared statements.
+ </para>
+
+ <para>
+ By default autoprepare mode is switched off. To enable it, assign non-zero
+ value to GUC variable <varname>autoprepare_limit</varname> or <varname>autoprepare_memory_limit</varname>.
+ This variables specify limit for number of autoprepared statement or memory used by them.
+ Autoprepare is enabled if one of thme is non zero. Value -1 means unlimited.
+ Please notice that event autoprepare is anabled, Postgres makes a decision about using
+ generalized plan vs. customized execution plans based on the results
+ of comparison of average time of five customized plans with
+ time of generalized plan.
+ </para>
+
+ <para>
+ Too large number of autoprepared statements can cause memory overflow
+ (especially if there are many active clients, because prepared statements cache
+ is local to the backend). So using unlimited autoprepare hash is debgerous and not recommended.
+ Postgres is using LRU policy to keep in memory most frequently used queries.
+ </para>
+
+ <para>
+ Autoprepare hash is local to the backend. It is implicitely reseted on any change of database schema or
+ session variables.
+ </para>
+
+ <para>
+ It is possible to inspect autoprepared queries in the backend using
+ <literal>pg_autoprepared_statements</literal> view. It shows original text of the
+ query, types of the extracted parameters (replacing literals),
+ query execution counter and used memory.
+ </para>
+
+ </chapter>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index cdc30fa..28c9343 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5326,6 +5326,39 @@ SELECT * FROM parent WHERE key = 2400;
</listitem>
</varlistentry>
+ <varlistentry id="guc-autoprepare-limit" xreflabel="autoprepare_limit">
+ <term><varname>autoprepare_limit</varname> (<type>integer/type>)
+ <indexterm>
+ <primary><varname>autoprepare_limit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Maximal number of autoprepared queries.
+ Zero value disables autoprepare, -1 means unlimited number of autoprepared queries.
+ Too large number of prepared queries can cause backend memory overflow and slowdown execution speed
+ (because of increased lookup time). Default value is <literal>0</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autoprepare-memory-limit" xreflabel="autoprepare_memory_limit">
+ <term><varname>autoprepare_memory_limit</varname> (<type>integer/type>)
+ <indexterm>
+ <primary><varname>autoprepare_memory_limit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Maximal size of memory used by autoprepared queries.
+ Zero value disables autoprepare, -1 means means that there is no memory limit.
+ Default value is <literal>0</literal>. Calculating memory used by prepared queries adds some extra overhead,
+ so positive value of this parameter may cause some slowdown.
+ <varname>autoprepare_limit</varname> is much faster way to limit number of autoprepared statements.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
</sect1>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 3da2365..4bd9f31 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -22,6 +22,7 @@
<!ENTITY json SYSTEM "json.sgml">
<!ENTITY mvcc SYSTEM "mvcc.sgml">
<!ENTITY parallel SYSTEM "parallel.sgml">
+<!ENTITY autoprepare SYSTEM "autoprepare.sgml">
<!ENTITY perform SYSTEM "perform.sgml">
<!ENTITY queries SYSTEM "queries.sgml">
<!ENTITY rangetypes SYSTEM "rangetypes.sgml">
diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml
index 3e115f1..ab9f279 100644
--- a/doc/src/sgml/postgres.sgml
+++ b/doc/src/sgml/postgres.sgml
@@ -109,6 +109,7 @@
&mvcc;
&perform;
∥
+ &autoprepare;
</part>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index ea4c85e..f5b9481 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -332,6 +332,9 @@ CREATE VIEW pg_prepared_xacts AS
CREATE VIEW pg_prepared_statements AS
SELECT * FROM pg_prepared_statement() AS P;
+CREATE VIEW pg_autoprepared_statements AS
+ SELECT * FROM pg_autoprepared_statement() AS P;
+
CREATE VIEW pg_seclabels AS
SELECT
l.objoid, l.classoid, l.objsubid,
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 7e0a041..795b1e5 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -48,7 +48,6 @@ static HTAB *prepared_queries = NULL;
static void InitQueryHashTable(void);
static ParamListInfo EvaluateParams(PreparedStatement *pstmt, List *params,
const char *queryString, EState *estate);
-static Datum build_regtype_array(Oid *param_types, int num_params);
/*
* Implements the 'PREPARE' utility statement.
@@ -787,7 +786,7 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
* pointing to a one-dimensional Postgres array of regtypes. An empty
* array is returned as a zero-element array, not NULL.
*/
-static Datum
+Datum
build_regtype_array(Oid *param_types, int num_params)
{
Datum *tmp_ary;
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index e8d8e6f..423c7f7 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -71,6 +71,8 @@
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
+#include "utils/builtins.h"
+#include "utils/fmgrprotos.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/ps_status.h"
@@ -171,6 +173,8 @@ static ProcSignalReason RecoveryConflictReason;
static MemoryContext row_description_context = NULL;
static StringInfoData row_description_buf;
+
+
/* ----------------------------------------------------------------
* decls for routines only used in this file
* ----------------------------------------------------------------
@@ -197,6 +201,163 @@ static void enable_statement_timeout(void);
static void disable_statement_timeout(void);
+/* ----------------
+ * autoprepare stuff
+ * ----------------
+ */
+typedef struct
+{
+ char* query_string;
+ Oid* param_types;
+ int num_params;
+} AutoprepareStatementKey;
+
+typedef struct
+{
+ AutoprepareStatementKey key;
+ CachedPlanSource* plan;
+ dlist_node lru; /* double linked list to implement LRU */
+ int64 exec_count; /* counter of execution of this query */
+ Size used_memory; /* memory used by prepared statement */
+} AutoprepareStatement;
+
+static AutoprepareStatement* autoprepare_stmt;
+static HTAB* autoprepare_hash;
+static dlist_head autoprepare_lru;
+static MemoryContext autoprepare_context;
+
+#define AUTOPREPARE_HASH_SIZE 113
+
+/*
+ * Plan cache access statistic
+ */
+static size_t autoprepare_cached_plans;
+static size_t autoprepare_used_memory;
+
+static uint32 autoprepare_hash_fn(const void *key, Size keysize)
+{
+ uint32 hash = 0;
+ int i;
+ AutoprepareStatementKey* ask = (AutoprepareStatementKey*)key;
+ for (i = 0; i < ask->num_params; i++)
+ hash ^= ask->param_types[i];
+ return string_hash(ask->query_string, strlen(ask->query_string)) ^ hash;
+}
+
+static int autoprepare_match_fn(const void *key1, const void *key2, Size keysize)
+{
+ AutoprepareStatementKey* ask1 = (AutoprepareStatementKey*)key1;
+ AutoprepareStatementKey* ask2 = (AutoprepareStatementKey*)key2;
+ if (ask1->num_params != ask2->num_params)
+ return 1;
+ if (memcmp(ask1->param_types, ask2->param_types, ask1->num_params*sizeof(Oid)) != 0)
+ return 1;
+
+ return strcmp(ask1->query_string, ask2->query_string);
+}
+
+/*
+ * This set returning function reads all the autoprepared statements and
+ * returns a set of (name, statement, prepare_time, param_types, from_sql).
+ */
+Datum
+pg_autoprepared_statement(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ TupleDesc tupdesc;
+ Tuplestorestate *tupstore;
+ MemoryContext per_query_ctx;
+ MemoryContext oldcontext;
+
+ /* check to see if caller supports us returning a tuplestore */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not " \
+ "allowed in this context")));
+
+ /* need to build tuplestore in query context */
+ per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ /*
+ * build tupdesc for result tuples. This must match the definition of the
+ * pg_prepared_statements view in system_views.sql
+ */
+ tupdesc = CreateTemplateTupleDesc(4);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "statement",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "parameter_types",
+ REGTYPEARRAYOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "exec_count",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "used_memory",
+ INT8OID, -1, 0);
+
+ /*
+ * We put all the tuples into a tuplestore in one scan of the hashtable.
+ * This avoids any issue of the hashtable possibly changing between calls.
+ */
+ tupstore =
+ tuplestore_begin_heap(rsinfo->allowedModes & SFRM_Materialize_Random,
+ false, work_mem);
+
+ /* generate junk in short-term context */
+ MemoryContextSwitchTo(oldcontext);
+
+ /* hash table might be uninitialized */
+ if (autoprepare_hash)
+ {
+ HASH_SEQ_STATUS hash_seq;
+ AutoprepareStatement *autoprep_stmt;
+
+ hash_seq_init(&hash_seq, autoprepare_hash);
+ while ((autoprep_stmt = hash_seq_search(&hash_seq)) != NULL)
+ {
+ if (autoprep_stmt->plan != NULL)
+ {
+ Datum values[4];
+ bool nulls[4];
+ MemSet(nulls, 0, sizeof(nulls));
+
+ values[0] = CStringGetTextDatum(autoprep_stmt->key.query_string);
+ values[1] = build_regtype_array(autoprep_stmt->key.param_types,
+ autoprep_stmt->key.num_params);
+ values[2] = Int64GetDatum(autoprep_stmt->exec_count);
+ values[3] = Int64GetDatum(autoprep_stmt->used_memory);
+
+ tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+ }
+ }
+ }
+
+ /* clean up and return the tuplestore */
+ tuplestore_donestoring(tupstore);
+
+ rsinfo->returnMode = SFRM_Materialize;
+ rsinfo->setResult = tupstore;
+ rsinfo->setDesc = tupdesc;
+
+ return (Datum) 0;
+}
+
+void ResetAutoprepareCache(void)
+{
+ if (autoprepare_hash != NULL)
+ {
+ hash_destroy(autoprepare_hash);
+ MemoryContextReset(autoprepare_context);
+ dlist_init(&autoprepare_lru);
+ autoprepare_cached_plans = 0;
+ autoprepare_used_memory = 0;
+ autoprepare_hash = 0;
+ }
+}
+
/* ----------------------------------------------------------------
* routines to obtain user input
* ----------------------------------------------------------------
@@ -1393,14 +1554,105 @@ exec_parse_message(const char *query_string, /* string to execute */
}
else
{
- /* Unnamed prepared statement --- release any prior unnamed stmt */
- drop_unnamed_stmt();
- /* Create context for parsing */
- unnamed_stmt_context =
- AllocSetContextCreate(MessageContext,
- "unnamed prepared statement",
- ALLOCSET_DEFAULT_SIZES);
- oldcontext = MemoryContextSwitchTo(unnamed_stmt_context);
+ /* check if autoprepare is enabled */
+ if (autoprepare_limit || autoprepare_memory_limit)
+ {
+ bool found;
+ AutoprepareStatementKey key;
+ key.query_string = (char*)query_string;
+ key.param_types = paramTypes;
+ key.num_params = numParams;
+
+ /*
+ * Construct autoprepare context if not constructed yet.
+ */
+ if (!autoprepare_context)
+ autoprepare_context = AllocSetContextCreate(CacheMemoryContext,
+ "autoprepare context",
+ ALLOCSET_DEFAULT_SIZES);
+
+ oldcontext = MemoryContextSwitchTo(autoprepare_context);
+
+ if (autoprepare_hash == NULL) /*initialize hash if not done yet */
+ {
+ static HASHCTL info;
+ info.keysize = sizeof(AutoprepareStatementKey);
+ info.entrysize = sizeof(AutoprepareStatement);
+ info.hash = autoprepare_hash_fn;
+ info.match = autoprepare_match_fn;
+ autoprepare_hash = hash_create("autoprepare_hash", autoprepare_limit != 0 ? autoprepare_limit : AUTOPREPARE_HASH_SIZE,
+ &info, HASH_ELEM | HASH_FUNCTION | HASH_COMPARE);
+ dlist_init(&autoprepare_lru);
+ }
+ autoprepare_stmt = (AutoprepareStatement*)hash_search(autoprepare_hash, &key, HASH_ENTER, &found);
+ if (found)
+ {
+ autoprepare_stmt->exec_count += 1;
+ dlist_delete(&autoprepare_stmt->lru); /* accessed entry will be moved to the head of LRU list */
+ if (autoprepare_stmt->plan != NULL && !autoprepare_stmt->plan->is_valid)
+ {
+ /* Drop invalidated plan: it will be reconstructed later */
+ if (autoprepare_memory_limit)
+ autoprepare_used_memory -= autoprepare_stmt->used_memory;
+ DropCachedPlan(autoprepare_stmt->plan);
+ autoprepare_stmt->plan = NULL;
+ autoprepare_stmt->used_memory = 0;
+ }
+ else
+ {
+ dlist_insert_after(&autoprepare_lru.head, &autoprepare_stmt->lru); /* prepend entry to the head of LRU list */
+ goto ParseCompleted;
+ }
+ }
+ else
+ {
+ /* Initialize autoprepare hash entry */
+ autoprepare_cached_plans += 1;
+ autoprepare_stmt->plan = NULL;
+ autoprepare_stmt->used_memory = 0;
+ autoprepare_stmt->exec_count = 1;
+
+ /* Copy query text and parameter type info to CacheMemoryContext */
+ autoprepare_stmt->key.query_string = pstrdup(query_string);
+ autoprepare_stmt->key.param_types = (Oid*)palloc(sizeof(Oid)*numParams);
+ memcpy(autoprepare_stmt->key.param_types, paramTypes, sizeof(Oid)*numParams);
+
+ /* LRU eviction policy */
+ while ((autoprepare_limit > 0 && autoprepare_cached_plans > autoprepare_limit)
+ || (autoprepare_memory_limit > 0 && autoprepare_used_memory > (size_t)autoprepare_memory_limit*1024))
+ {
+ /* Drop least recently accessed query */
+ AutoprepareStatement* victim = dlist_container(AutoprepareStatement, lru, autoprepare_lru.head.prev);
+ dlist_delete(&victim->lru);
+ if (victim->plan)
+ {
+ if (autoprepare_memory_limit)
+ autoprepare_used_memory -= victim->used_memory;
+ DropCachedPlan(victim->plan);
+ }
+ hash_search(autoprepare_hash, victim, HASH_REMOVE, NULL);
+ pfree(victim->key.query_string);
+ pfree(victim->key.param_types);
+ autoprepare_cached_plans -= 1;
+ }
+ }
+ dlist_insert_after(&autoprepare_lru.head, &autoprepare_stmt->lru); /* prepend entry to the head of LRU list */
+
+ /* Autoprepare statements also parsed in MessageContext */
+ MemoryContextSwitchTo(MessageContext);
+ }
+ else
+ {
+ /* Unnamed prepared statement --- release any prior unnamed stmt */
+ drop_unnamed_stmt();
+ /* Create context for parsing */
+ unnamed_stmt_context =
+ AllocSetContextCreate(MessageContext,
+ "unnamed prepared statement",
+ ALLOCSET_DEFAULT_SIZES);
+ oldcontext = MemoryContextSwitchTo(unnamed_stmt_context);
+ autoprepare_stmt = NULL;
+ }
}
/*
@@ -1539,13 +1791,21 @@ exec_parse_message(const char *query_string, /* string to execute */
}
else
{
- /*
- * We just save the CachedPlanSource into unnamed_stmt_psrc.
- */
SaveCachedPlan(psrc);
- unnamed_stmt_psrc = psrc;
+ if (autoprepare_stmt)
+ {
+ autoprepare_stmt->plan = psrc;
+ autoprepare_stmt->used_memory = CachedPlanMemoryUsage(autoprepare_stmt->plan);
+ autoprepare_used_memory += autoprepare_stmt->used_memory;
+ }
+ else
+ /*
+ * We just save the CachedPlanSource into unnamed_stmt_psrc.
+ */
+ unnamed_stmt_psrc = psrc;
}
+ ParseCompleted:
MemoryContextSwitchTo(oldcontext);
/*
@@ -1633,7 +1893,7 @@ exec_bind_message(StringInfo input_message)
else
{
/* special-case the unnamed statement */
- psrc = unnamed_stmt_psrc;
+ psrc = autoprepare_stmt ? autoprepare_stmt->plan : unnamed_stmt_psrc;
if (!psrc)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_PSTATEMENT),
@@ -2444,7 +2704,7 @@ exec_describe_statement_message(const char *stmt_name)
else
{
/* special-case the unnamed statement */
- psrc = unnamed_stmt_psrc;
+ psrc = autoprepare_stmt ? autoprepare_stmt->plan : unnamed_stmt_psrc;
if (!psrc)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_PSTATEMENT),
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 7f6f0b6..e0ab57f 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -679,10 +679,12 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_AlterSystemStmt:
PreventInTransactionBlock(isTopLevel, "ALTER SYSTEM");
AlterSystemSetConfigFile((AlterSystemStmt *) parsetree);
+ ResetAutoprepareCache();
break;
case T_VariableSetStmt:
ExecSetVariableStmt((VariableSetStmt *) parsetree, isTopLevel);
+ ResetAutoprepareCache();
break;
case T_VariableShowStmt:
@@ -966,6 +968,8 @@ ProcessUtilitySlow(ParseState *pstate,
/* All event trigger calls are done only when isCompleteQuery is true */
needCleanup = isCompleteQuery && EventTriggerBeginCompleteQuery();
+ ResetAutoprepareCache();
+
/* PG_TRY block is to ensure we call EventTriggerEndCompleteQuery */
PG_TRY();
{
diff --git a/src/backend/utils/cache/inval.c b/src/backend/utils/cache/inval.c
index f09e3a9..6168eee 100644
--- a/src/backend/utils/cache/inval.c
+++ b/src/backend/utils/cache/inval.c
@@ -113,6 +113,7 @@
#include "utils/relmapper.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "tcop/pquery.h"
/*
@@ -646,6 +647,7 @@ InvalidateSystemCaches(void)
InvalidateCatalogSnapshot();
ResetCatalogCaches();
+ ResetAutoprepareCache();
RelationCacheInvalidate(); /* gets smgr and relmap too */
for (i = 0; i < syscache_callback_count; i++)
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index abc3062..93225a5 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -2022,3 +2022,39 @@ ResetPlanCache(void)
cexpr->is_valid = false;
}
}
+
+/*
+ * 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;
+}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index eb78522..068ada5 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -533,6 +533,9 @@ int tcp_keepalives_interval;
int tcp_keepalives_count;
int tcp_user_timeout;
+int autoprepare_limit;
+int autoprepare_memory_limit;
+
/*
* SSL renegotiation was been removed in PostgreSQL 9.5, but we tolerate it
* being set to zero (meaning never renegotiate) for backward compatibility.
@@ -2267,6 +2270,31 @@ static struct config_int ConfigureNamesInt[] =
check_max_stack_depth, assign_max_stack_depth, NULL
},
+ /*
+ * Limits for implicit preparing of frequently executed queries
+ */
+ {
+ {"autoprepare_limit", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Maximal number of autoprepared queries."),
+ gettext_noop("0 disable autoprepare, -1 means unlimited number of autoprepared queries. Too large number of prepared queries can cause backend memory overflow and slowdown execution speed (because of increased lookup time)")
+ },
+ &autoprepare_limit,
+ 0, -1, INT_MAX,
+ NULL, NULL, NULL
+ },
+ {
+ {"autoprepare_memory_limit", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Maximal size of memory used by autoprepared queries."),
+ gettext_noop("0 disables autoprepare, -1 means that there is no memory limit. "
+ "Calculating memory used by prepared queries adds some extra overhead, "
+ "so non-zero value of this parameter may cause some slowdown. autoprepare_limit is much faster way to limit number of autoprepared statements"),
+ GUC_UNIT_KB
+ },
+ &autoprepare_memory_limit,
+ 0, -1, INT_MAX,
+ NULL, NULL, NULL
+ },
+
{
{"temp_file_limit", PGC_SUSET, RESOURCES_DISK,
gettext_noop("Limits the total size of all temporary files used by each process."),
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b88e886..57254c7 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7611,6 +7611,13 @@
proargmodes => '{o,o,o,o,o}',
proargnames => '{name,statement,prepare_time,parameter_types,from_sql}',
prosrc => 'pg_prepared_statement' },
+{ oid => '4035', descr => 'get the autoprepared statements for this session',
+ proname => 'pg_autoprepared_statement', prorows => '1000', proretset => 't',
+ provolatile => 's', proparallel => 'r', prorettype => 'record',
+ proargtypes => '', proallargtypes => '{text,_regtype,int8,int8}',
+ proargmodes => '{o,o,o,o}',
+ proargnames => '{statement,parameter_types,exec_count,used_memory}',
+ prosrc => 'pg_autoprepared_statement' },
{ oid => '2511', descr => 'get the open cursors for this session',
proname => 'pg_cursor', prorows => '1000', proretset => 't',
provolatile => 's', proparallel => 'r', prorettype => 'record',
diff --git a/src/include/commands/prepare.h b/src/include/commands/prepare.h
index 2ce8324..11322ce 100644
--- a/src/include/commands/prepare.h
+++ b/src/include/commands/prepare.h
@@ -56,5 +56,6 @@ extern TupleDesc FetchPreparedStatementResultDesc(PreparedStatement *stmt);
extern List *FetchPreparedStatementTargetList(PreparedStatement *stmt);
extern void DropAllPreparedStatements(void);
+extern Datum build_regtype_array(Oid *param_types, int num_params);
#endif /* PREPARE_H */
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.h
index 9e24cfa..db3e448 100644
--- a/src/include/tcop/pquery.h
+++ b/src/include/tcop/pquery.h
@@ -42,4 +42,6 @@ extern uint64 PortalRunFetch(Portal portal,
long count,
DestReceiver *dest);
+extern void ResetAutoprepareCache(void);
+
#endif /* PQUERY_H */
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index a93ed77..8a2718b 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -255,6 +255,9 @@ extern int log_min_duration_statement;
extern int log_temp_files;
extern double log_xact_sample_rate;
+extern int autoprepare_limit;
+extern int autoprepare_memory_limit;
+
extern int temp_file_limit;
extern int num_temp_buffers;
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index de2555e..dc73e59 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -222,4 +222,6 @@ extern void ReleaseCachedPlan(CachedPlan *plan, bool useResOwner);
extern CachedExpression *GetCachedExpression(Node *expr);
extern void FreeCachedExpression(CachedExpression *cexpr);
+extern Size CachedPlanMemoryUsage(CachedPlanSource *plansource);
+
#endif /* PLANCACHE_H */
