From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Cached plans and statement generalization
Date: Mon, 5 Aug 2019 20:02:09 +0300
Message-ID: <e228a05c-501d-8b66-67b5-bae212cb7b70@postgrespro.ru> (raw)
In-Reply-To: <6c91d4ba-1a77-ba64-39ee-fda26dcfc9bd@postgrespro.ru>
References: <CA+TgmoZAHc4wqL3HvVmTd87s=bobp_8_7R9H=B_kH6q4GcA=Yg@mail.gmail.com>
<CA+q6zcWKsaiFz6oWA1JXdJWNwSSk+wUyuOVO2nryCCSxrr3KRw@mail.gmail.com>
<EDA4195584F5064680D8130B1CA91C453D0182@G01JPEXMBYT04>
<6ea1db86-b300-b57c-0f9d-a55dd3966175@postgrespro.ru>
<9A6E5062D5D4DB458C80C2B2920BD71D6820CB@g01jpexmbkw23>
<554d0730-f6fc-d1be-fe78-e63973ec621f@postgrespro.ru>
<35be0423-36bc-cadc-bef8-02d475751f7d@postgrespro.ru>
<7603a101-6b79-2328-2b4b-3bc3217992e5@postgrespro.ru>
<CA+hUKGJK2GZewMumkXtPjyATMu2tyEmg53R23WCETY=y48RxQw@mail.gmail.com>
<f71ca2cb-b2c0-d6ca-4532-822b25f1f81e@postgrespro.ru>
<CA+hUKGKuxM94eVyBii=0e1bWpa-A05f2L6H-JSij0H5o7t+ZqA@mail.gmail.com>
<3304c34b-4a9c-909c-4ee1-d8e7f6dcb2de@postgrespro.ru>
<CA+hUKGL9_ohxMBq04N8qwn-vEaaan2N-06FaXsTz9+seDddN3g@mail.gmail.com>
<66f47595-1b93-ef67-fdd6-c5414e0f94c3@postgrespro.ru>
<e9cce21f-942f-31f4-54e4-ace7f2de1a5f@iki.fi>
<6c91d4ba-1a77-ba64-39ee-fda26dcfc9bd@postgrespro.ru>
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
Attachments:autoprepare-extended-2.patch (text/x-patch, 25.3K) downloaddiff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgmlnew file mode 100644index 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.sgmlindex 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.sgmlindex 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.sgmlindex 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.sqlindex 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.cindex 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.cindex 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.cindex 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.cindex 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.cindex 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.cindex 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.datindex 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.hindex 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.hindex 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.hindex 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.hindex 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 */
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: k.knizhnik@postgrespro.ru, hlinnaka@iki.fi, thomas.munro@gmail.com
Subject: Re: [HACKERS] Cached plans and statement generalization
In-Reply-To: <e228a05c-501d-8b66-67b5-bae212cb7b70@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox