Hi
>
> >
> > some examples based on Ada doc
> >
> > FUNCTION xxx RETURN int AS
> > PRAGMA yyy -- pragma has function scope
> > BEGIN
> >
> > FUNCTION xxx RETURN int AS
> > BEGIN
> > DECLARE
> > PRAGMA yyy -- pragma has block scope
>
> ok, sub-block makes sense over statement level IMO.
>
I am sending proof concept (parser only implementation) - it allows to
control query plan usage on function and on block level
Examples
CREATE OR REPLACE FUNCTION fx()
RETURNS int AS $$
PRAGMA use_query_plan_cache(off); -- disable query plan cache on function
level
DECLARE r record;
BEGIN
FOR r IN SELECT ... -- some complex query, where we prefer on one shot
plan
LOOP
DECLARE
PRAGMA use_query_plan_cache(on); -- enable query plan cache for block
BEGIN
... statements inside cycle reuses query plan
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;
or
BEGIN
...
DECLARE
PRAGMA use_query_plan_cache(off);
BEGIN
-- these queries has fresh plan only
SELECT ...
SELECT ...
END; -- end of PRAGMA scope
...
-- usual behave
END;
The behave is static - controlled on compile time only - the controlled
feature can be enabled/disabled. The impact on runtime is zero
* the syntax is verbose - readable - I prefer strong clean signal for
readers so something internals is different
* consistent with Ada, PL/SQL
* remove one reason for dynamic SQL
* allows to mix queries with without query plan cache - interesting for
patter FOR IN slow query LOOP fast query; END LOOP;
* there is small risk of compatibility break - if somebody use variables
named PRAGMA, because new reserved keyword is necessary - fails on syntax
error - so it is easy identified.
* this syntax can be reused - autonomous_transaction like PL/SQL. I read a
manual of Gnu Ada - and this is used often for implementation legacy
(obsolete) behave, functionality.
Notes, comments?
Regards
Pavel
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index 3c52d71..a5fd040 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -288,6 +288,7 @@ do_compile(FunctionCallInfo fcinfo,
int *in_arg_varnos = NULL;
PLpgSQL_variable **out_arg_variables;
MemoryContext func_cxt;
+ PLpgSQL_settings settings;
/*
* Setup the scanner input and error info. We assume that this function
@@ -373,6 +374,11 @@ do_compile(FunctionCallInfo fcinfo,
plpgsql_DumpExecTree = false;
plpgsql_start_datums();
+ /* Prepare default for PRAGMA directives */
+ settings.prev = NULL;
+ settings.use_query_plan_cache = true;
+ plpgsql_settings_init(&settings);
+
switch (function->fn_is_trigger)
{
case PLPGSQL_NOT_TRIGGER:
@@ -796,6 +802,7 @@ plpgsql_compile_inline(char *proc_source)
PLpgSQL_variable *var;
int parse_rc;
MemoryContext func_cxt;
+ PLpgSQL_settings settings;
/*
* Setup the scanner input and error info. We assume that this function
@@ -851,6 +858,11 @@ plpgsql_compile_inline(char *proc_source)
plpgsql_DumpExecTree = false;
plpgsql_start_datums();
+ /* Prepare default for PRAGMA directives */
+ settings.prev = NULL;
+ settings.use_query_plan_cache = true;
+ plpgsql_settings_init(&settings);
+
/* 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_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 906fe01..c7ee968 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -33,6 +33,7 @@
* ----------
*/
static PLpgSQL_nsitem *ns_top = NULL;
+static PLpgSQL_settings *settings_top = NULL;
/* ----------
@@ -226,6 +227,66 @@ plpgsql_ns_find_nearest_loop(PLpgSQL_nsitem *ns_cur)
/*
+ * Compilator settings routines
+ */
+
+void
+plpgsql_settings_init(PLpgSQL_settings *defval)
+{
+ settings_top = defval;
+}
+
+/*
+ * Creates new settings based on previous settings
+ */
+void
+plpgsql_settings_clone(void)
+{
+ PLpgSQL_settings *new = palloc(sizeof(PLpgSQL_settings));
+
+ Assert(settings_top != NULL);
+
+ memcpy(new, settings_top, sizeof(PLpgSQL_settings));
+ new->prev = settings_top;
+ settings_top = new;
+}
+
+/*
+ * apply a pragma to current settings
+ */
+void
+plpgsql_settings_pragma(PLpgSQL_pragma_type typ, bool value)
+{
+ Assert(settings_top != NULL);
+
+ switch (typ)
+ {
+ case PLPGSQL_PRAGMA_QUERY_PLAN_CACHE:
+ settings_top->use_query_plan_cache = value;
+ }
+}
+
+/*
+ * restore previous compiler settings
+ */
+void
+plpgsql_settings_pop(void)
+{
+ PLpgSQL_settings *prev;
+
+ Assert(settings_top != NULL);
+ prev = settings_top->prev;
+ pfree(settings_top);
+ settings_top = prev;
+}
+
+PLpgSQL_settings *
+plpgsql_settings_top(void)
+{
+ return settings_top;
+}
+
+/*
* Statement type as a string, for use in error messages etc.
*/
const char *
@@ -1534,7 +1595,7 @@ dump_getdiag(PLpgSQL_stmt_getdiag *stmt)
static void
dump_expr(PLpgSQL_expr *expr)
{
- printf("'%s'", expr->query);
+ printf("%s'%s'", expr->use_query_plan_cache ? "*" : "", expr->query);
}
void
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 4a4cd6a..2e8287b 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -216,6 +216,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%type <ival> opt_scrollable
%type <fetch> opt_fetch_direction
+%type <boolean> onoff
+
%type <keyword> unreserved_keyword
@@ -306,6 +308,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt);
%token <keyword> K_NOT
%token <keyword> K_NOTICE
%token <keyword> K_NULL
+%token <keyword> K_OFF
+%token <keyword> K_ON
%token <keyword> K_OPEN
%token <keyword> K_OPTION
%token <keyword> K_OR
@@ -315,9 +319,11 @@ 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_PRAGMA
%token <keyword> K_PRINT_STRICT_PARAMS
%token <keyword> K_PRIOR
%token <keyword> K_QUERY
+%token <keyword> K_QUERY_PLAN_CACHE
%token <keyword> K_RAISE
%token <keyword> K_RELATIVE
%token <keyword> K_RESULT_OID
@@ -348,9 +354,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 +404,30 @@ opt_semi :
| ';'
;
+onoff : K_ON
+ {
+ $$ = true;
+ }
+ | K_OFF
+ {
+ $$ = false;
+ }
+ ;
+
+pragma : K_PRAGMA K_QUERY_PLAN_CACHE '(' onoff ')' ';'
+ {
+ plpgsql_settings_pragma(PLPGSQL_PRAGMA_QUERY_PLAN_CACHE, $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 +444,7 @@ pl_block : decl_sect K_BEGIN proc_sect exception_sect K_END opt_label
check_labels($1.label, $6, @6);
plpgsql_ns_pop();
+ plpgsql_settings_pop();
$$ = (PLpgSQL_stmt *)new;
}
@@ -448,6 +479,10 @@ decl_start : K_DECLARE
{
/* Forget any variables created before block */
plpgsql_add_initdatums(NULL);
+
+ /* clone compiler settings */
+ plpgsql_settings_clone();
+
/*
* Disable scanner lookup of identifiers while
* we process the decl_stmts
@@ -476,6 +511,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
@@ -579,6 +615,7 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull
}
strcpy(cp2, "'::pg_catalog.refcursor");
curname_def->query = pstrdup(buf);
+ curname_def->use_query_plan_cache = plpgsql_settings_top()->use_query_plan_cache;
new->default_val = curname_def;
new->cursor_explicit_expr = $7;
@@ -2442,6 +2479,8 @@ unreserved_keyword :
| K_NEXT
| K_NO
| K_NOTICE
+ | K_OFF
+ | K_ON
| K_OPEN
| K_OPTION
| K_PERFORM
@@ -2453,6 +2492,7 @@ unreserved_keyword :
| K_PRINT_STRICT_PARAMS
| K_PRIOR
| K_QUERY
+ | K_QUERY_PLAN_CACHE
| K_RAISE
| K_RELATIVE
| K_RESULT_OID
@@ -2689,6 +2729,7 @@ read_sql_construct(int until,
expr->paramnos = NULL;
expr->rwparam = -1;
expr->ns = plpgsql_ns_top();
+ expr->use_query_plan_cache = plpgsql_settings_top()->use_query_plan_cache;
pfree(ds.data);
if (valid_sql)
@@ -2937,6 +2978,7 @@ make_execsql_stmt(int firsttoken, int location)
expr->paramnos = NULL;
expr->rwparam = -1;
expr->ns = plpgsql_ns_top();
+ expr->use_query_plan_cache = plpgsql_settings_top()->use_query_plan_cache;
pfree(ds.data);
check_sql_expr(expr->query, location, 0);
@@ -3821,6 +3863,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected)
expr->paramnos = NULL;
expr->rwparam = -1;
expr->ns = plpgsql_ns_top();
+ expr->use_query_plan_cache = plpgsql_settings_top()->use_query_plan_cache;
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 c401213..cf5bd39 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)
@@ -139,6 +140,8 @@ static const ScanKeyword unreserved_keywords[] = {
PG_KEYWORD("next", K_NEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("no", K_NO, UNRESERVED_KEYWORD)
PG_KEYWORD("notice", K_NOTICE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("off", K_OFF, UNRESERVED_KEYWORD)
+ PG_KEYWORD("on", K_ON, UNRESERVED_KEYWORD)
PG_KEYWORD("open", K_OPEN, UNRESERVED_KEYWORD)
PG_KEYWORD("option", K_OPTION, UNRESERVED_KEYWORD)
PG_KEYWORD("perform", K_PERFORM, UNRESERVED_KEYWORD)
@@ -150,6 +153,7 @@ static const ScanKeyword unreserved_keywords[] = {
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)
+ PG_KEYWORD("query_plan_cache", K_QUERY_PLAN_CACHE, UNRESERVED_KEYWORD)
PG_KEYWORD("raise", K_RAISE, UNRESERVED_KEYWORD)
PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("result_oid", K_RESULT_OID, UNRESERVED_KEYWORD)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 3421eed..e3c4435 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -35,6 +35,14 @@
#define _(x) dgettext(TEXTDOMAIN, x)
/*
+ * Compiler directives
+ */
+typedef enum PLpgSQL_pragma_type
+{
+ PLPGSQL_PRAGMA_QUERY_PLAN_CACHE
+} PLpgSQL_pragma_type;
+
+/*
* Compiler's namespace item types
*/
typedef enum PLpgSQL_nsitem_type
@@ -224,6 +232,7 @@ typedef struct PLpgSQL_expr
int dno;
char *query;
SPIPlanPtr plan;
+ bool use_query_plan_cache;
Bitmapset *paramnos; /* all dnos referenced by this query */
int rwparam; /* dno of read/write param, or -1 if none */
@@ -1004,6 +1013,15 @@ typedef struct PLwdatum
List *idents; /* valid if composite name */
} PLwdatum;
+/*
+ * Compiler directives
+ */
+typedef struct PLpgSQL_settings
+{
+ struct PLpgSQL_settings *prev;
+ bool use_query_plan_cache;
+} PLpgSQL_settings;
+
/**********************************************************************
* Global variable declarations
**********************************************************************/
@@ -1123,6 +1141,15 @@ extern PLpgSQL_nsitem *plpgsql_ns_lookup_label(PLpgSQL_nsitem *ns_cur,
extern PLpgSQL_nsitem *plpgsql_ns_find_nearest_loop(PLpgSQL_nsitem *ns_cur);
/*
+ * Function for compiler directives processing in pl_func.c
+ */
+extern void plpgsql_settings_init(PLpgSQL_settings *defval);
+extern void plpgsql_settings_clone(void);
+extern void plpgsql_settings_pragma(PLpgSQL_pragma_type typ, bool value);
+extern PLpgSQL_settings *plpgsql_settings_top(void);
+extern void plpgsql_settings_pop(void);
+
+/*
* Other functions in pl_funcs.c
*/
extern const char *plpgsql_stmt_typename(PLpgSQL_stmt *stmt);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
