On 25.09.2019 23:06, Alvaro Herrera wrote:
This patch fails the "rules" tests. Please fix.
Sorry,
New version of the patch with corrected expected output for rules test
is attached.
--
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 6612f95..a285f6d 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5339,6 +5339,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 e59cba7..a479f36 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 9fe4a47..7e06389 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 c6faa66..27f1c80 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 2178e1c..a00cc23 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 58ea5b9..c2a7e2b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7617,6 +7617,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 6791e0c..a8b80e7 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 */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 210e9cd..4cb4fa3 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1307,6 +1307,11 @@ mvtest_tvv| SELECT sum(mvtest_tv.totamt) AS grandtot
FROM mvtest_tv;
mvtest_tvvmv| SELECT mvtest_tvvm.grandtot
FROM mvtest_tvvm;
+pg_autoprepared_statements| SELECT p.statement,
+ p.parameter_types,
+ p.exec_count,
+ p.used_memory
+ FROM pg_autoprepared_statement() p(statement, parameter_types, exec_count, used_memory);
pg_available_extension_versions| SELECT e.name,
e.version,
(x.extname IS NOT NULL) AS installed,
