2017-01-24 6:38 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:
> Hi
>
> 2017-01-23 21:59 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com>:
>
>> On 1/23/17 2:10 PM, Pavel Stehule wrote:
>>
>>> Comments, notes?
>>>
>>
>> +1 on the idea. It'd also be nice if we could expose control of plans for
>> dynamic SQL, though I suspect that's not terribly useful without some kind
>> of global session storage.
>>
>> A couple notes on a quick read-through:
>>
>> Instead of paralleling all the existing namespace stuff, I wonder if it'd
>> be better to create explicit block infrastructure. AFAIK PRAGMAs are going
>> to have a lot of the same requirements (certainly the nesting is the same),
>> and we might want more of this king of stuff in the future. (I've certainly
>> wished I could set a GUC in a plpgsql block and have it's settings revert
>> when exiting the block...)
>>
>
> I am not sure if I understand. ?? Setting GUC by PRAGMA can work - the
> syntax supports it and GUC API supports nesting. Not sure about exception
> handling - but it should not be problem probably.
>
> Please, can you show some examples.
>
>
>> 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
>> finit_ would be better named free_.
>
>
> good idea
>
> Regards
>
> Pavel
>
>
>>
>> --
>> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
>> Experts in Analytics, Data Architecture and PostgreSQL
>> Data in Trouble? Get it in Treble! http://BlueTreble.com
>> 855-TREBLE2 (855-873-2532)
>>
>
>
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 b48146a..9971ed2 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))
{
@@ -4096,7 +4096,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)
{
@@ -4167,7 +4168,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);
}
/*
@@ -4366,7 +4367,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);
}
@@ -5173,7 +5174,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
@@ -5252,8 +5253,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..5086a53 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
@@ -582,11 +622,12 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull
new->default_val = curname_def;
new->cursor_explicit_expr = $7;
+ new->cursor_explicit_expr->cursor_options |=
+ CURSOR_OPT_FAST_PLAN | $2;
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 +2382,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 +2470,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 +2495,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 +2735,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 +2984,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 +3869,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 +4056,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
