Hi
2017-01-24 21:33 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:
>
>
>
>>
>>> 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
>
> Regards
>
> Pavel
>
+ basic doc
Regards
Pavel
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index d3272e1..97c59db 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 b25b3f1..304fc91 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -51,6 +51,8 @@ bool plpgsql_check_syntax = false;
PLpgSQL_function *plpgsql_curr_compile;
+PLpgSQL_directives *plpgsql_directives;
+
/* A context appropriate for short-term allocs during compilation */
MemoryContext plpgsql_compile_tmp_cxt;
@@ -83,6 +85,11 @@ static const ExceptionLabelMap exception_label_map[] = {
{NULL, 0}
};
+PLpgSQL_directives default_directives = {
+ NULL,
+ true, /* is_function_scope */
+ 0 /* no special cursor option */
+};
/* ----------
* static prototypes
@@ -374,6 +381,9 @@ do_compile(FunctionCallInfo fcinfo,
plpgsql_DumpExecTree = false;
plpgsql_start_datums();
+ /* set default compile directives */
+ plpgsql_directives = &default_directives;
+
switch (function->fn_is_trigger)
{
case PLPGSQL_NOT_TRIGGER:
@@ -852,6 +862,9 @@ plpgsql_compile_inline(char *proc_source)
plpgsql_DumpExecTree = false;
plpgsql_start_datums();
+ /* set default compile directives */
+ plpgsql_directives = &default_directives;
+
/* Set up as though in a function returning VOID */
function->fn_rettype = VOIDOID;
function->fn_retset = false;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 6fc3db0..0a01bbe 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2335,7 +2335,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
@@ -3625,7 +3625,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))
{
@@ -4095,7 +4095,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)
{
@@ -4166,7 +4167,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);
}
/*
@@ -4365,7 +4366,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);
}
@@ -5172,7 +5173,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
@@ -5251,8 +5252,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 906fe01..65fea0e 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -1535,6 +1535,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 4a4cd6a..3c0d52b 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -108,6 +108,9 @@ static PLpgSQL_expr *read_cursor_args(PLpgSQL_var *cursor,
static List *read_raise_options(void);
static void check_raise_parameters(PLpgSQL_stmt_raise *stmt);
+static void init_block_directives(void);
+static void free_block_directives(void);
+
%}
%expect 0
@@ -216,6 +219,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
@@ -283,6 +288,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
@@ -315,6 +322,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
@@ -348,9 +357,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;
}
;
@@ -398,6 +407,34 @@ 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_directives->cursor_options = $4;
+ }
+ ;
+
+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;
@@ -414,6 +451,7 @@ pl_block : decl_sect K_BEGIN proc_sect exception_sect K_END opt_label
check_labels($1.label, $6, @6);
plpgsql_ns_pop();
+ free_block_directives();
$$ = (PLpgSQL_stmt *)new;
}
@@ -448,6 +486,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
@@ -476,6 +515,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
@@ -581,12 +621,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;
}
;
@@ -2341,11 +2381,13 @@ expr_until_loop :
opt_block_label :
{
plpgsql_ns_push(NULL, PLPGSQL_LABEL_BLOCK);
+ init_block_directives();
$$ = NULL;
}
| LESS_LESS any_identifier GREATER_GREATER
{
plpgsql_ns_push($2, PLPGSQL_LABEL_BLOCK);
+ init_block_directives();
$$ = $2;
}
;
@@ -2427,6 +2469,8 @@ unreserved_keyword :
| K_EXIT
| K_FETCH
| K_FIRST
+ | K_FORCE_CUSTOM_PLAN
+ | K_FORCE_GENERIC_PLAN
| K_FORWARD
| K_GET
| K_HINT
@@ -2450,6 +2494,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
@@ -2689,6 +2734,7 @@ read_sql_construct(int until,
expr->paramnos = NULL;
expr->rwparam = -1;
expr->ns = plpgsql_ns_top();
+ expr->cursor_options = plpgsql_directives->cursor_options;
pfree(ds.data);
if (valid_sql)
@@ -2937,6 +2983,7 @@ make_execsql_stmt(int firsttoken, int location)
expr->paramnos = NULL;
expr->rwparam = -1;
expr->ns = plpgsql_ns_top();
+ expr->cursor_options = plpgsql_directives->cursor_options;
pfree(ds.data);
check_sql_expr(expr->query, location, 0);
@@ -3821,6 +3868,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 = plpgsql_directives->cursor_options;
pfree(ds.data);
/* Next we'd better find the until token */
@@ -4007,3 +4055,29 @@ make_case(int location, PLpgSQL_expr *t_expr,
return (PLpgSQL_stmt *) new;
}
+
+/*
+ * Prepare and install local copy of compiler directives
+ */
+static void
+init_block_directives(void)
+{
+ PLpgSQL_directives *directives = palloc(sizeof(PLpgSQL_directives));
+
+ directives->prev = plpgsql_directives;
+ directives->cursor_options = plpgsql_directives->cursor_options;
+ directives->is_function_scope = false;
+ plpgsql_directives = directives;
+}
+
+/*
+ * Uninstall local copy of compiler directives
+ */
+static void
+free_block_directives(void)
+{
+ PLpgSQL_directives *directives = plpgsql_directives;
+
+ plpgsql_directives = directives->prev;
+ pfree(directives);
+}
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
index c401213..a41f669 100644
--- a/src/pl/plpgsql/src/pl_scanner.c
+++ b/src/pl/plpgsql/src/pl_scanner.c
@@ -84,6 +84,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)
@@ -124,6 +125,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)
@@ -147,6 +150,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 3421eed..74d1b7c 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -224,6 +224,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 */
@@ -266,7 +267,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;
@@ -1004,6 +1004,16 @@ typedef struct PLwdatum
List *idents; /* valid if composite name */
} PLwdatum;
+/*
+ * Compiler directives
+ */
+typedef struct PLpgSQL_directives
+{
+ struct PLpgSQL_directives *prev;
+ bool is_function_scope;
+ int cursor_options;
+} PLpgSQL_directives;
+
/**********************************************************************
* Global variable declarations
**********************************************************************/
@@ -1053,6 +1063,8 @@ extern PLpgSQL_plugin **plpgsql_plugin_ptr;
/*
* Functions in pl_comp.c
*/
+extern PLpgSQL_directives *plpgsql_directives;
+
extern PLpgSQL_function *plpgsql_compile(FunctionCallInfo fcinfo,
bool forValidator);
extern PLpgSQL_function *plpgsql_compile_inline(char *proc_source);
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 79513e4..3923d13 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 877d3ad..ed3ba8f 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
