2017-03-19 14:30 GMT+01:00 Petr Jelinek <[email protected]>:
> On 19/03/17 12:32, Pavel Stehule wrote:
> >
> >
> > 2017-03-18 19:30 GMT+01:00 Petr Jelinek <[email protected]
> > <mailto:[email protected]>>:
> >
> > On 16/03/17 17:15, David Steele wrote:
> > > On 2/1/17 3:59 PM, Pavel Stehule wrote:
> > >> Hi
> > >>
> > >> 2017-01-24 21:33 GMT+01:00 Pavel Stehule <[email protected]
> <mailto:[email protected]>
> > >> <mailto:[email protected] <mailto:[email protected]
> >>>:
> > >>
> > >> Perhaps that's as simple as renaming all the existing
> _ns_*
> > >> functions to _block_ and then adding support for
> pragmas...
> > >>
> > >> Since you're adding cursor_options to PLpgSQL_expr it
> should
> > >> probably be removed as an option to exec_*.
> > >>
> > >> I have to recheck it. Some cursor options going from
> dynamic
> > >> cursor variables and are related to dynamic query - not
> query
> > >> that creates query string.
> > >>
> > >> hmm .. so current state is better due using options like
> > >> CURSOR_OPT_PARALLEL_OK
> > >>
> > >> if (expr->plan == NULL)
> > >> exec_prepare_plan(estate, expr, (parallelOK ?
> > >> CURSOR_OPT_PARALLEL_OK : 0) |
> > >> expr->cursor_options);
> > >>
> > >> This options is not permanent feature of expression - and
> then I
> > >> cannot to remove cursor_option argument from exec_*
> > >>
> > >> I did minor cleaning - remove cursor_options from plpgsql_var
> > >>
> > >> + basic doc
> > >
> > > This patch still applies cleanly and compiles at cccbdde.
> > >
> > > Any reviewers want to have a look?
> > >
> >
> > I'll bite.
> >
> > I agree with Jim that it's not very nice to add yet another
> > block/ns-like layer. I don't see why pragma could not be added to
> either
> > PLpgSQL_stmt_block (yes pragma can be for whole function but function
> > body is represented by PLpgSQL_stmt_block as well so no issue
> there), or
> > to namespace code. In namespace since they are used for other thing
> > there would be bit of unnecessary propagation but it's 8bytes per
> > namespace, does not seem all that much.
> >
> > My preference would be to add it to PLpgSQL_stmt_block (unless we
> plan
> > to add posibility to add pragmas for other loops and other things)
> but I
> > am not sure if current block is easily (and in a fast way) accessible
> > from all places where it's needed. Maybe the needed info could be
> pushed
> > to estate from PLpgSQL_stmt_block during the execution.
> >
> >
> > There is maybe partial misunderstand of pragma - it is set of nested
> > configurations used in compile time only. It can be used in execution
> > time too - it change nothing.
> >
> > The pragma doesn't build a persistent tree. It is stack of
> > configurations that allows fast access to current configuration, and
> > fast leaving of configuration when the change is out of scope.
> >
> > I don't see any any advantage to integrate pragma to ns or to
> > stmt_block. But maybe I don't understand to your idea.
> >
> > I see a another possibility in code - nesting init_block_directives() to
> > plpgsql_ns_push and free_block_directives() to plpgsql_ns_pop()
> >
>
> That's more or less what I mean by "integrating" to ns :)
>
> The main idea is to not add 3rd layer of block push/pop that's sprinkled
> in "random" places.
>
ok fixed
I reworked a maintaining settings - now it use lazy copy - the copy of
settings is created only when pragma is used in namespace. It remove any
impact on current code without pragmas.
Regards
Pavel
>
> --
> Petr Jelinek http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index d356deb9f5..56da4d6163 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -802,6 +802,32 @@ $$ LANGUAGE plpgsql;
happen in a plain SQL command.
</para>
</sect2>
+
+ <sect2 id="plpgsql-declaration-pragma">
+ <title>Block level PRAGMA</title>
+
+ <indexterm>
+ <primary>PRAGMA</>
+ <secondary>in PL/pgSQL</>
+ </indexterm>
+
+ <para>
+ The block level <literal>PRAGMA</literal> allows to change some
+ <application>PL/pgSQL</application> compiler behave. Currently
+ only <literal>PRAGMA PLAN_CACHE</literal> is supported.
+
+<programlisting>
+CREATE FUNCTION enforce_fresh_plan(_id text) RETURNS boolean AS $$
+DECLARE
+ PRAGMA PLAN_CACHE(force_custom_plan);
+BEGIN
+ -- in this block every embedded query uses one shot plan
+ RETURN EXISTS(SELECT * FROM tab WHERE id = _id);
+END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+ </para>
+ </sect2>
</sect1>
<sect1 id="plpgsql-expressions">
@@ -4649,6 +4675,43 @@ $$ LANGUAGE plpgsql;
use of the <literal>now()</> function would still be a better idea.
</para>
+
+ <sect3 id="PRAGMA-PLAN_CACHE">
+ <title>PRAGMA PLAN_CACHE</title>
+
+ <para>
+ The plan cache behave can be controlled with <literal>PRAGMA PLAN_CACHE</>.
+ This <literal>PRAGMA</> can be used on function or on block level (per
+ function, per block). The following options are possible:
+ <literal>DEFAULT</literal> - default <application>PL/pgSQL</application>
+ implementation - the system try to decide between custom plan and generic
+ plan after five query executions, <literal>FORCE_CUSTOM_PLAN</literal>
+ - the execution plan is one shot plan - it is specific for every set of
+ used paramaters, <literal>FORCE_GENERIC_PLAN</literal> - the query plan
+ is generic from start.
+ </para>
+
+ <para>
+ <indexterm>
+ <primary>PRAGMA PLAN_CACHE</>
+ <secondary>in PL/pgSQL</>
+ </indexterm>
+ The plan for <command>INSERT</command> is generic from begin. The <literal>
+ PRAGMA PLAN_CACHE</literal> is related to function - etc. every command
+ in this function will use generic plan.
+<programlisting>
+CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
+ PRAGMA PLAN_CACHE(FORCE_GENERIC_PLAN);
+ DECLARE
+ curtime timestamp;
+ BEGIN
+ curtime := 'now';
+ INSERT INTO logtable VALUES (logtxt, curtime);
+ END;
+$$ LANGUAGE plpgsql;
+</programlisting>
+ </para>
+ </sect3>
</sect2>
</sect1>
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index bed343ea0c..70c970d20c 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -85,6 +85,10 @@ static const ExceptionLabelMap exception_label_map[] = {
{NULL, 0}
};
+PLpgSQL_settings default_settings = {
+ NULL,
+ 0 /* no special cursor option */
+};
/* ----------
* static prototypes
@@ -371,6 +375,7 @@ do_compile(FunctionCallInfo fcinfo,
* outermost namespace contains function parameters and other special
* variables (such as FOUND), and is named after the function itself.
*/
+ plpgsql_settings_init(&default_settings);
plpgsql_ns_init();
plpgsql_ns_push(NameStr(procStruct->proname), PLPGSQL_LABEL_BLOCK);
plpgsql_DumpExecTree = false;
@@ -849,6 +854,7 @@ plpgsql_compile_inline(char *proc_source)
function->extra_warnings = 0;
function->extra_errors = 0;
+ plpgsql_settings_init(&default_settings);
plpgsql_ns_init();
plpgsql_ns_push(func_name, PLPGSQL_LABEL_BLOCK);
plpgsql_DumpExecTree = false;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 49a4e622ff..1558b479f6 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2337,7 +2337,7 @@ exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt)
Assert(query);
if (query->plan == NULL)
- exec_prepare_plan(estate, query, curvar->cursor_options);
+ exec_prepare_plan(estate, query, query->cursor_options);
/*
* Set up short-lived ParamListInfo
@@ -3627,7 +3627,7 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
{
ListCell *l;
- exec_prepare_plan(estate, expr, 0);
+ exec_prepare_plan(estate, expr, expr->cursor_options);
stmt->mod_stmt = false;
foreach(l, SPI_plan_get_plan_sources(expr->plan))
{
@@ -4097,7 +4097,8 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
*/
query = stmt->query;
if (query->plan == NULL)
- exec_prepare_plan(estate, query, stmt->cursor_options);
+ exec_prepare_plan(estate, query,
+ query->cursor_options | stmt->cursor_options);
}
else if (stmt->dynquery != NULL)
{
@@ -4168,7 +4169,7 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
query = curvar->cursor_explicit_expr;
if (query->plan == NULL)
- exec_prepare_plan(estate, query, curvar->cursor_options);
+ exec_prepare_plan(estate, query, query->cursor_options);
}
/*
@@ -4367,7 +4368,7 @@ exec_assign_expr(PLpgSQL_execstate *estate, PLpgSQL_datum *target,
*/
if (expr->plan == NULL)
{
- exec_prepare_plan(estate, expr, 0);
+ exec_prepare_plan(estate, expr, expr->cursor_options);
if (target->dtype == PLPGSQL_DTYPE_VAR)
exec_check_rw_parameter(expr, target->dno);
}
@@ -5174,7 +5175,7 @@ exec_eval_expr(PLpgSQL_execstate *estate,
* If first time through, create a plan for this expression.
*/
if (expr->plan == NULL)
- exec_prepare_plan(estate, expr, 0);
+ exec_prepare_plan(estate, expr, expr->cursor_options);
/*
* If this is a simple expression, bypass SPI and use the executor
@@ -5253,8 +5254,8 @@ exec_run_select(PLpgSQL_execstate *estate,
* On the first call for this expression generate the plan
*/
if (expr->plan == NULL)
- exec_prepare_plan(estate, expr, parallelOK ?
- CURSOR_OPT_PARALLEL_OK : 0);
+ exec_prepare_plan(estate, expr, (parallelOK ?
+ CURSOR_OPT_PARALLEL_OK : 0) | expr->cursor_options);
/*
* If a portal was requested, put the query into the portal
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 93f89814b3..d074a4046e 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -35,6 +35,7 @@
* ----------
*/
static PLpgSQL_nsitem *ns_top = NULL;
+static PLpgSQL_settings *current_settings = NULL;
/* ----------
@@ -71,6 +72,15 @@ plpgsql_ns_pop(void)
Assert(ns_top != NULL);
while (ns_top->itemtype != PLPGSQL_NSTYPE_LABEL)
ns_top = ns_top->prev;
+
+ /* Release local settings copy if was created */
+ if (ns_top->local_settings != NULL)
+ {
+ current_settings = ns_top->local_settings->prev;
+ pfree(ns_top->local_settings);
+ ns_top->local_settings = NULL;
+ }
+
ns_top = ns_top->prev;
}
@@ -103,6 +113,7 @@ plpgsql_ns_additem(PLpgSQL_nsitem_type itemtype, int itemno, const char *name)
nse->itemtype = itemtype;
nse->itemno = itemno;
nse->prev = ns_top;
+ nse->local_settings = NULL;
strcpy(nse->name, name);
ns_top = nse;
}
@@ -227,6 +238,55 @@ plpgsql_ns_find_nearest_loop(PLpgSQL_nsitem *ns_cur)
}
+/* ----------
+ * Returns pointer to current compiler settings
+ * ----------
+ */
+PLpgSQL_settings *
+plpgsql_current_settings(void)
+{
+ return current_settings;
+}
+
+
+/* ----------
+ * Set a default compiler settings
+ * ----------
+ */
+void
+plpgsql_settings_init(PLpgSQL_settings *settings)
+{
+ current_settings = settings;
+}
+
+
+/* ----------
+ * Set compiler settings
+ * ----------
+ */
+void
+plpgsql_settings_set(PLpgSQL_settings *settings)
+{
+ PLpgSQL_nsitem *ns_cur = ns_top;
+
+ /*
+ * Modify settings directly, when ns has local settings data.
+ * When ns uses shared settings, create settings first.
+ */
+ while (ns_cur->itemtype != PLPGSQL_NSTYPE_LABEL)
+ ns_cur = ns_cur->prev;
+
+ if (ns_cur->local_settings == NULL)
+ {
+ ns_cur->local_settings = palloc(sizeof(PLpgSQL_settings));
+ ns_cur->local_settings->prev = current_settings;
+ current_settings = ns_cur->local_settings;
+ }
+
+ current_settings->cursor_options = settings->cursor_options;
+}
+
+
/*
* Statement type as a string, for use in error messages etc.
*/
@@ -1537,6 +1597,10 @@ static void
dump_expr(PLpgSQL_expr *expr)
{
printf("'%s'", expr->query);
+ if (expr->cursor_options & CURSOR_OPT_GENERIC_PLAN)
+ printf("/* GENERIC_PLAN! */");
+ if (expr->cursor_options & CURSOR_OPT_CUSTOM_PLAN)
+ printf("/* CUSTOM_PLAN! */");
}
void
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 29729df550..0228ede279 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -218,6 +218,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%type <ival> opt_scrollable
%type <fetch> opt_fetch_direction
+%type <ival> plan_cache_option
+
%type <keyword> unreserved_keyword
@@ -285,6 +287,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%token <keyword> K_FETCH
%token <keyword> K_FIRST
%token <keyword> K_FOR
+%token <keyword> K_FORCE_CUSTOM_PLAN
+%token <keyword> K_FORCE_GENERIC_PLAN
%token <keyword> K_FOREACH
%token <keyword> K_FORWARD
%token <keyword> K_FROM
@@ -317,6 +321,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%token <keyword> K_PG_EXCEPTION_CONTEXT
%token <keyword> K_PG_EXCEPTION_DETAIL
%token <keyword> K_PG_EXCEPTION_HINT
+%token <keyword> K_PLAN_CACHE
+%token <keyword> K_PRAGMA
%token <keyword> K_PRINT_STRICT_PARAMS
%token <keyword> K_PRIOR
%token <keyword> K_QUERY
@@ -350,9 +356,9 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%%
-pl_function : comp_options pl_block opt_semi
+pl_function : comp_options pragmas_opt pl_block opt_semi
{
- plpgsql_parse_result = (PLpgSQL_stmt_block *) $2;
+ plpgsql_parse_result = (PLpgSQL_stmt_block *) $3;
}
;
@@ -400,6 +406,42 @@ opt_semi :
| ';'
;
+plan_cache_option : K_DEFAULT
+ {
+ $$ = 0;
+ }
+ | K_FORCE_GENERIC_PLAN
+ {
+ $$ = CURSOR_OPT_GENERIC_PLAN;
+ }
+ | K_FORCE_CUSTOM_PLAN
+ {
+ $$ = CURSOR_OPT_CUSTOM_PLAN;
+ }
+ ;
+
+pragma : K_PRAGMA K_PLAN_CACHE '(' plan_cache_option ')' ';'
+ {
+ PLpgSQL_settings nsettings;
+
+ /*
+ * Currently only cursor_option is allowed. Is not necessary
+ * do merge of settings.
+ */
+ nsettings.cursor_options = $4;
+
+ plpgsql_settings_set(&nsettings);
+ }
+ ;
+
+pragmas : pragmas pragma
+ | pragma
+ ;
+
+pragmas_opt :
+ | pragmas
+ ;
+
pl_block : decl_sect K_BEGIN proc_sect exception_sect K_END opt_label
{
PLpgSQL_stmt_block *new;
@@ -450,6 +492,7 @@ decl_start : K_DECLARE
{
/* Forget any variables created before block */
plpgsql_add_initdatums(NULL);
+
/*
* Disable scanner lookup of identifiers while
* we process the decl_stmts
@@ -478,6 +521,7 @@ decl_stmt : decl_statement
errmsg("block label must be placed before DECLARE, not after"),
parser_errposition(@1)));
}
+ | pragma
;
decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull decl_defval
@@ -583,12 +627,12 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull
curname_def->query = pstrdup(buf);
new->default_val = curname_def;
+ $7->cursor_options |= CURSOR_OPT_FAST_PLAN | $2;
new->cursor_explicit_expr = $7;
if ($5 == NULL)
new->cursor_explicit_argrow = -1;
else
new->cursor_explicit_argrow = $5->dno;
- new->cursor_options = CURSOR_OPT_FAST_PLAN | $2;
}
;
@@ -2429,6 +2473,8 @@ unreserved_keyword :
| K_EXIT
| K_FETCH
| K_FIRST
+ | K_FORCE_CUSTOM_PLAN
+ | K_FORCE_GENERIC_PLAN
| K_FORWARD
| K_GET
| K_HINT
@@ -2452,6 +2498,7 @@ unreserved_keyword :
| K_PG_EXCEPTION_CONTEXT
| K_PG_EXCEPTION_DETAIL
| K_PG_EXCEPTION_HINT
+ | K_PLAN_CACHE
| K_PRINT_STRICT_PARAMS
| K_PRIOR
| K_QUERY
@@ -2608,6 +2655,7 @@ read_sql_construct(int until,
int startlocation = -1;
int parenlevel = 0;
PLpgSQL_expr *expr;
+ PLpgSQL_settings *settings = plpgsql_current_settings();
initStringInfo(&ds);
appendStringInfoString(&ds, sqlstart);
@@ -2691,6 +2739,7 @@ read_sql_construct(int until,
expr->paramnos = NULL;
expr->rwparam = -1;
expr->ns = plpgsql_ns_top();
+ expr->cursor_options = settings->cursor_options;
pfree(ds.data);
if (valid_sql)
@@ -2850,6 +2899,7 @@ make_execsql_stmt(int firsttoken, int location)
bool have_strict = false;
int into_start_loc = -1;
int into_end_loc = -1;
+ PLpgSQL_settings *settings = plpgsql_current_settings();
initStringInfo(&ds);
@@ -2939,6 +2989,7 @@ make_execsql_stmt(int firsttoken, int location)
expr->paramnos = NULL;
expr->rwparam = -1;
expr->ns = plpgsql_ns_top();
+ expr->cursor_options = settings->cursor_options;
pfree(ds.data);
check_sql_expr(expr->query, location, 0);
@@ -3675,6 +3726,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected)
StringInfoData ds;
char *sqlstart = "SELECT ";
bool any_named = false;
+ PLpgSQL_settings *settings = plpgsql_current_settings();
tok = yylex();
if (cursor->cursor_explicit_argrow < 0)
@@ -3823,6 +3875,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected)
expr->paramnos = NULL;
expr->rwparam = -1;
expr->ns = plpgsql_ns_top();
+ expr->cursor_options = settings->cursor_options;
pfree(ds.data);
/* Next we'd better find the until token */
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
index 553be8c93c..6a57c35e10 100644
--- a/src/pl/plpgsql/src/pl_scanner.c
+++ b/src/pl/plpgsql/src/pl_scanner.c
@@ -86,6 +86,7 @@ static const ScanKeyword reserved_keywords[] = {
PG_KEYWORD("not", K_NOT, RESERVED_KEYWORD)
PG_KEYWORD("null", K_NULL, RESERVED_KEYWORD)
PG_KEYWORD("or", K_OR, RESERVED_KEYWORD)
+ PG_KEYWORD("pragma", K_PRAGMA, RESERVED_KEYWORD)
PG_KEYWORD("strict", K_STRICT, RESERVED_KEYWORD)
PG_KEYWORD("then", K_THEN, RESERVED_KEYWORD)
PG_KEYWORD("to", K_TO, RESERVED_KEYWORD)
@@ -126,6 +127,8 @@ static const ScanKeyword unreserved_keywords[] = {
PG_KEYWORD("exit", K_EXIT, UNRESERVED_KEYWORD)
PG_KEYWORD("fetch", K_FETCH, UNRESERVED_KEYWORD)
PG_KEYWORD("first", K_FIRST, UNRESERVED_KEYWORD)
+ PG_KEYWORD("force_custom_plan", K_FORCE_CUSTOM_PLAN, UNRESERVED_KEYWORD)
+ PG_KEYWORD("force_generic_plan", K_FORCE_GENERIC_PLAN, UNRESERVED_KEYWORD)
PG_KEYWORD("forward", K_FORWARD, UNRESERVED_KEYWORD)
PG_KEYWORD("get", K_GET, UNRESERVED_KEYWORD)
PG_KEYWORD("hint", K_HINT, UNRESERVED_KEYWORD)
@@ -149,6 +152,7 @@ static const ScanKeyword unreserved_keywords[] = {
PG_KEYWORD("pg_exception_context", K_PG_EXCEPTION_CONTEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("pg_exception_detail", K_PG_EXCEPTION_DETAIL, UNRESERVED_KEYWORD)
PG_KEYWORD("pg_exception_hint", K_PG_EXCEPTION_HINT, UNRESERVED_KEYWORD)
+ PG_KEYWORD("plan_cache", K_PLAN_CACHE, UNRESERVED_KEYWORD)
PG_KEYWORD("print_strict_params", K_PRINT_STRICT_PARAMS, UNRESERVED_KEYWORD)
PG_KEYWORD("prior", K_PRIOR, UNRESERVED_KEYWORD)
PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index b7e103b514..536631133c 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -222,6 +222,7 @@ typedef struct PLpgSQL_expr
int dno;
char *query;
SPIPlanPtr plan;
+ int cursor_options;
Bitmapset *paramnos; /* all dnos referenced by this query */
int rwparam; /* dno of read/write param, or -1 if none */
@@ -264,7 +265,6 @@ typedef struct PLpgSQL_var
PLpgSQL_expr *default_val;
PLpgSQL_expr *cursor_explicit_expr;
int cursor_explicit_argrow;
- int cursor_options;
Datum value;
bool isnull;
@@ -344,6 +344,15 @@ typedef struct PLpgSQL_arrayelem
} PLpgSQL_arrayelem;
/*
+ * Compiler settings
+ */
+typedef struct PLpgSQL_settings
+{
+ struct PLpgSQL_settings *prev;
+ int cursor_options;
+} PLpgSQL_settings;
+
+/*
* Item in the compilers namespace tree
*/
typedef struct PLpgSQL_nsitem
@@ -355,6 +364,7 @@ typedef struct PLpgSQL_nsitem
*/
int itemno;
struct PLpgSQL_nsitem *prev;
+ PLpgSQL_settings *local_settings; /* has own copy of PRAGMA settings */
char name[FLEXIBLE_ARRAY_MEMBER]; /* nul-terminated string */
} PLpgSQL_nsitem;
@@ -1002,6 +1012,7 @@ typedef struct PLwdatum
List *idents; /* valid if composite name */
} PLwdatum;
+
/**********************************************************************
* Global variable declarations
**********************************************************************/
@@ -1119,6 +1130,10 @@ extern PLpgSQL_nsitem *plpgsql_ns_lookup(PLpgSQL_nsitem *ns_cur, bool localmode,
extern PLpgSQL_nsitem *plpgsql_ns_lookup_label(PLpgSQL_nsitem *ns_cur,
const char *name);
extern PLpgSQL_nsitem *plpgsql_ns_find_nearest_loop(PLpgSQL_nsitem *ns_cur);
+extern PLpgSQL_settings *plpgsql_current_settings(void);
+extern void plpgsql_settings_init(PLpgSQL_settings *settings);
+extern void plpgsql_settings_set(PLpgSQL_settings *settings);
+
/*
* Other functions in pl_funcs.c
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 04848c10a2..c23adf4475 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5684,3 +5684,38 @@ end;
$$;
ERROR: value for domain plpgsql_arr_domain violates check constraint "plpgsql_arr_domain_check"
CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment
+-- test of plan cache controlling
+-- these tests checks only syntax
+create table pragma_plan_cache_foo(a int);
+insert into pragma_plan_cache_foo values(10);
+create or replace function pragma_plan_cache(_a int)
+returns void as $$
+pragma plan_cache(default);
+pragma plan_cache(force_custom_plan);
+pragma plan_cache(force_generic_plan);
+declare
+ pragma plan_cache(default);
+ aux int;
+ pragma plan_cache(force_custom_plan);
+ pragma plan_cache(force_generic_plan);
+begin
+ select a into aux from pragma_plan_cache_foo where a = _a;
+ raise notice '<<%>>', aux;
+end;
+$$ language plpgsql;
+select pragma_plan_cache(0);
+NOTICE: <<<NULL>>>
+ pragma_plan_cache
+-------------------
+
+(1 row)
+
+select pragma_plan_cache(10);
+NOTICE: <<10>>
+ pragma_plan_cache
+-------------------
+
+(1 row)
+
+drop function pragma_plan_cache(int);
+drop table pragma_plan_cache_foo;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 31dcbdffdd..2847c9dafb 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4475,3 +4475,33 @@ begin
v_test := 0 || v_test; -- fail
end;
$$;
+
+
+-- test of plan cache controlling
+
+-- these tests checks only syntax
+
+create table pragma_plan_cache_foo(a int);
+insert into pragma_plan_cache_foo values(10);
+
+create or replace function pragma_plan_cache(_a int)
+returns void as $$
+pragma plan_cache(default);
+pragma plan_cache(force_custom_plan);
+pragma plan_cache(force_generic_plan);
+declare
+ pragma plan_cache(default);
+ aux int;
+ pragma plan_cache(force_custom_plan);
+ pragma plan_cache(force_generic_plan);
+begin
+ select a into aux from pragma_plan_cache_foo where a = _a;
+ raise notice '<<%>>', aux;
+end;
+$$ language plpgsql;
+
+select pragma_plan_cache(0);
+select pragma_plan_cache(10);
+
+drop function pragma_plan_cache(int);
+drop table pragma_plan_cache_foo;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
