Hi,
this patch is based on discussions related to plpgsql2 project.
Currently we cannot to control plan cache from plpgsql directly. We can use
dynamic SQL if we can enforce oneshot plan - but it means little bit less
readable code (if we enforce dynamic SQL from performance reasons). It
means so the code cannot be checked by plpgsql check too.
The plan cache subsystem allows some control by options
CURSOR_OPT_GENERIC_PLAN and CURSOR_OPT_CUSTOM_PLAN. So we just a interface
how to use these options from PLpgSQL. I used Ada language feature (used in
PL/SQL too) - PRAGMA statement. It allows to set compiler directives. The
syntax of PRAGMA statements allows to set a level where entered compiler
directive should be applied. It can works on function level or block level.
Attached patch introduces PRAGMA plan_cache with options: DEFAULT,
FORCE_CUSTOM_PLAN, FORCE_GENERIC_PLAN. Plan cache is partially used every
time - the parser/analyzer result is cached every time.
Examples:
CREATE OR REPLACE FUNCTION foo(a int)
RETURNS int AS $$
DECLARE ..
BEGIN
DECLARE
/* block level (local scope) pragma */
PRAGMA plan_cache(FORCE_CUSTOM_PLAN);
BEGIN
SELECT /* slow query - dynamic sql is not necessary */
END;
END;
Benefits:
1. remove one case where dynamic sql is necessary now - security, static
check
2. introduce PRAGMAs - possible usage: autonomous transactions, implicit
namespaces settings (namespace for auto variables, namespace for function
arguments).
Comments, notes?
Regards
Pavel
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..66b3ce9 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -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))
{
@@ -4366,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);
}
@@ -5173,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
@@ -5252,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..3f236b5 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 finit_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();
+ finit_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
@@ -586,7 +626,8 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull
new->cursor_explicit_argrow = -1;
else
new->cursor_explicit_argrow = $5->dno;
- new->cursor_options = CURSOR_OPT_FAST_PLAN | $2;
+ new->cursor_options = CURSOR_OPT_FAST_PLAN | $2
+ | plpgsql_directives->cursor_options;
}
;
@@ -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
+finit_block_directives(void)
+{
+ PLpgSQL_directives *directives = plpgsql_directives;
+
+ plpgsql_directives = directives->prev;
+ pfree(directives);
+}
\ No newline at end of file
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..4ec8a88 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 */
@@ -1004,6 +1005,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 +1064,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
