VIP: plpgsql - early embedded sql plan preparation
Started by Pavel Stehuleover 14 years ago3 messages
Hello
I am returning back to discus two months ago about usage of
plpgsql_lint. I integrated this module to plpgsql's core. This feature
is controlled via plpgsql GUC variable prepare_plans and via plpgsql
option. It works with all plpgsql's regress tests without tests where
dynamic sql or refcursors are used. Early plan preparation is disabled
default so it should not to break current applications.
This feature can live as contrib module too, but integration has one
advantage - there is still a possibility to use other plpgsql
extensions - mainly plpgsql debugger.
I didn't work on documentation yet, so I there is small example:
CREATE TYPE tp AS (a int, b int);
CREATE OR REPLACE FUNCTION test()
RETURNS int AS $$
DECLARE v tp;
BEGIN
v := (10,20);
IF false THEN
RAISE NOTICE '%', v.z;
END IF;
RETURN v.a;
END;
$$ LANGUAGE plpgsql;
postgres=# SELECT test();
test
------
10
(1 row)
with enabled early planning it found a bug in not executed code
CREATE OR REPLACE FUNCTION public.test()
RETURNS integer
LANGUAGE plpgsql
AS $function$
#prepare_plans on_start
DECLARE v tp;
BEGIN
v := (10,20);
IF false THEN
RAISE NOTICE '%', v.z;
END IF;
RETURN v.a;
END;
$function$
postgres=# select test();
ERROR: record "v" has no field "z"
LINE 1: SELECT v.z
^
QUERY: SELECT v.z
CONTEXT: PL/pgSQL function "test" line 7 at RAISE
you can set GUC
postgres=# set plpgsql.prepare_plans to on_start;
SET
and you can overwrite this global setting with directive
#prepare_plans on_demand
CREATE OR REPLACE FUNCTION public.test()
RETURNS integer
LANGUAGE plpgsql
AS $function$
#prepare_plans on_demand
DECLARE v tp;
BEGIN
v := (10,20);
IF false THEN
RAISE NOTICE '%', v.z;
END IF;
RETURN v.a;
END;
$function$
Regards
Pavel Stehule
Attachments:
prepare_plans.patchtext/x-patch; charset=US-ASCII; name=prepare_plans.patchDownload
*** a/src/pl/plpgsql/src/gram.y
--- b/src/pl/plpgsql/src/gram.y
***************
*** 293,298 **** static List *read_raise_options(void);
--- 293,300 ----
%token <keyword> K_NOT
%token <keyword> K_NOTICE
%token <keyword> K_NULL
+ %token <keyword> K_ON_DEMAND
+ %token <keyword> K_ON_START
%token <keyword> K_OPEN
%token <keyword> K_OPTION
%token <keyword> K_OR
***************
*** 300,305 **** static List *read_raise_options(void);
--- 302,308 ----
%token <keyword> K_PG_EXCEPTION_CONTEXT
%token <keyword> K_PG_EXCEPTION_DETAIL
%token <keyword> K_PG_EXCEPTION_HINT
+ %token <keyword> K_PREPARE_PLANS
%token <keyword> K_PRIOR
%token <keyword> K_QUERY
%token <keyword> K_RAISE
***************
*** 354,359 **** comp_option : '#' K_OPTION K_DUMP
--- 357,370 ----
{
plpgsql_curr_compile->resolve_option = PLPGSQL_RESOLVE_COLUMN;
}
+ | '#' K_PREPARE_PLANS K_ON_DEMAND
+ {
+ plpgsql_curr_compile->prepare_plans_option = PLPGSQL_PREPARE_PLANS_ON_DEMAND;
+ }
+ | '#' K_PREPARE_PLANS K_ON_START
+ {
+ plpgsql_curr_compile->prepare_plans_option = PLPGSQL_PREPARE_PLANS_ON_START;
+ }
;
opt_semi :
***************
*** 2232,2241 **** unreserved_keyword :
--- 2243,2255 ----
| K_NEXT
| K_NO
| K_NOTICE
+ | K_ON_DEMAND
+ | K_ON_START
| K_OPTION
| K_PG_EXCEPTION_CONTEXT
| K_PG_EXCEPTION_DETAIL
| K_PG_EXCEPTION_HINT
+ | K_PREPARE_PLANS
| K_PRIOR
| K_QUERY
| K_RELATIVE
*** a/src/pl/plpgsql/src/pl_comp.c
--- b/src/pl/plpgsql/src/pl_comp.c
***************
*** 354,359 **** do_compile(FunctionCallInfo fcinfo,
--- 354,360 ----
function->fn_cxt = func_cxt;
function->out_param_varno = -1; /* set up for no OUT param */
function->resolve_option = plpgsql_variable_conflict;
+ function->prepare_plans_option = PLPGSQL_PREPARE_PLANS_UNDEF;
/*
* Initialize the compiler, particularly the namespace stack. The
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
***************
*** 210,215 **** static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate,
--- 210,217 ----
PLpgSQL_expr *dynquery, List *params,
const char *portalname, int cursorOptions);
+ static bool expr_prepare_plan(PLpgSQL_stmt *stmt,
+ PLpgSQL_expr *expr, void *context);
/* ----------
* plpgsql_exec_function Called by the call handler for
***************
*** 310,315 **** plpgsql_exec_function(PLpgSQL_function *func, FunctionCallInfo fcinfo)
--- 312,375 ----
exec_set_found(&estate, false);
/*
+ * Prepare plans for all embeded SQL when it's requested
+ */
+ if ((func->prepare_plans_option == PLPGSQL_PREPARE_PLANS_ON_START) ||
+ (func->prepare_plans_option == PLPGSQL_PREPARE_PLANS_UNDEF &&
+ plpgsql_prepare_plans == PLPGSQL_PREPARE_PLANS_ON_START))
+ {
+ int i;
+ PLpgSQL_rec *recvars;
+
+ /*
+ * walker can chanhe a type to REC variable. This can be a unwanted
+ * change of behave - so we save all REC variables before walker's run
+ * and later we revert to back all values.
+ */
+ recvars = palloc(sizeof(PLpgSQL_rec) * estate.ndatums);
+ for (i = 0; i < estate.ndatums; i++)
+ {
+ if (estate.datums[i]->dtype == PLPGSQL_DTYPE_REC)
+ {
+ PLpgSQL_rec *rec = (PLpgSQL_rec *) estate.datums[i];
+
+ recvars[i].tup = rec->tup;
+ recvars[i].tupdesc = rec->tupdesc;
+ recvars[i].freetup = rec->freetup;
+ recvars[i].freetupdesc = rec->freetupdesc;
+
+ /* don't release a original record */
+ rec->freetup = false;
+ rec->freetupdesc = false;
+ }
+ }
+
+ plpgsql_expr_walker(func, (PLpgSQL_stmt *) func->action,
+ expr_prepare_plan,
+ &estate);
+ for (i = 0; i < estate.ndatums; i++)
+ {
+ if (estate.datums[i]->dtype == PLPGSQL_DTYPE_REC)
+ {
+ PLpgSQL_rec *rec = (PLpgSQL_rec *) estate.datums[i];
+
+ if (rec->freetup)
+ heap_freetuple(rec->tup);
+
+ if (rec->freetupdesc)
+ FreeTupleDesc(rec->tupdesc);
+
+ rec->tup = recvars[i].tup;
+ rec->tupdesc = recvars[i].tupdesc;
+ rec->freetup = recvars[i].freetup;
+ rec->freetupdesc = recvars[i].freetupdesc;
+ }
+ }
+
+ pfree(recvars);
+ }
+
+ /*
* Let the instrumentation plugin peek at this function
*/
if (*plugin_ptr && (*plugin_ptr)->func_beg)
***************
*** 676,681 **** plpgsql_exec_trigger(PLpgSQL_function *func,
--- 736,800 ----
exec_set_found(&estate, false);
/*
+ * Prepare plans for all embeded SQL when it's requested
+ */
+ if ((func->prepare_plans_option == PLPGSQL_PREPARE_PLANS_ON_START) ||
+ (func->prepare_plans_option == PLPGSQL_PREPARE_PLANS_UNDEF &&
+ plpgsql_prepare_plans == PLPGSQL_PREPARE_PLANS_ON_START))
+ {
+ int i;
+ PLpgSQL_rec *recvars;
+
+ /*
+ * walker can chanhe a type to REC variable. This can be a unwanted
+ * change of behave - so we save all REC variables before walker's run
+ * and later we revert to back all values.
+ */
+ recvars = palloc(sizeof(PLpgSQL_rec) * estate.ndatums);
+ for (i = 0; i < estate.ndatums; i++)
+ {
+ if (estate.datums[i]->dtype == PLPGSQL_DTYPE_REC)
+ {
+ PLpgSQL_rec *rec = (PLpgSQL_rec *) estate.datums[i];
+
+ recvars[i].tup = rec->tup;
+ recvars[i].tupdesc = rec->tupdesc;
+ recvars[i].freetup = rec->freetup;
+ recvars[i].freetupdesc = rec->freetupdesc;
+
+ /* don't release a original record */
+ rec->freetup = false;
+ rec->freetupdesc = false;
+ }
+ }
+
+ plpgsql_expr_walker(func, (PLpgSQL_stmt *) func->action,
+ expr_prepare_plan,
+ &estate);
+ for (i = 0; i < estate.ndatums; i++)
+ {
+ if (estate.datums[i]->dtype == PLPGSQL_DTYPE_REC)
+ {
+ PLpgSQL_rec *rec = (PLpgSQL_rec *) estate.datums[i];
+
+ if (rec->freetup)
+ heap_freetuple(rec->tup);
+
+ if (rec->freetupdesc)
+ FreeTupleDesc(rec->tupdesc);
+
+ rec->tup = recvars[i].tup;
+ rec->tupdesc = recvars[i].tupdesc;
+ rec->freetup = recvars[i].freetup;
+ rec->freetupdesc = recvars[i].freetupdesc;
+ }
+ }
+
+ pfree(recvars);
+
+ }
+
+ /*
* Let the instrumentation plugin peek at this function
*/
if (*plugin_ptr && (*plugin_ptr)->func_beg)
***************
*** 6028,6030 **** exec_dynquery_with_params(PLpgSQL_execstate *estate,
--- 6147,6610 ----
return portal;
}
+
+ /*
+ * Sometime we must initialize a unknown record variable with NULL
+ * of type that is derived from some plan. This is necessary for later
+ * using a rec variable. Last parameter 'use_element_type' is true, when
+ * we would to assign a element type of result array.
+ *
+ */
+ static void
+ set_rectype_for_query(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt *stmt,
+ PLpgSQL_rec *rec,
+ PLpgSQL_expr *query,
+ bool use_element_type)
+ {
+ bool *nulls;
+ HeapTuple tup;
+ CachedPlanSource *plansource = NULL;
+ const char *err_text = estate->err_text;
+
+ estate->err_text = NULL;
+ estate->err_stmt = stmt;
+
+ if (rec->freetup)
+ heap_freetuple(rec->tup);
+
+ if (rec->freetupdesc)
+ FreeTupleDesc(rec->tupdesc);
+
+ if (query->plan != NULL)
+ {
+ SPIPlanPtr plan = query->plan;
+
+ if (plan == NULL || plan->magic != _SPI_PLAN_MAGIC)
+ elog(ERROR, "cached plan is not valid plan");
+
+ if (list_length(plan->plancache_list) != 1)
+ elog(ERROR, "plan is not single execution plan");
+
+ plansource = (CachedPlanSource *) linitial(plan->plancache_list);
+
+ rec->tupdesc = CreateTupleDescCopy(plansource->resultDesc);
+ rec->freetupdesc = true;
+ }
+ else
+ elog(ERROR, "there are no plan for query: \"%s\"",
+ query->query);
+
+ /*
+ * try to get a element type, when result is a array (used with FOREACH ARRAY stmt)
+ */
+ if (use_element_type)
+ {
+ Oid elemtype;
+ TupleDesc tupdesc;
+
+ /* result should be a array */
+ if (rec->tupdesc->natts != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg_plural("query \"%s\" returned %d column",
+ "query \"%s\" returned %d columns",
+ rec->tupdesc->natts,
+ query->query,
+ rec->tupdesc->natts)));
+
+ /* check the type of the expression - must be an array */
+ elemtype = get_element_type(rec->tupdesc->attrs[0]->atttypid);
+ if (!OidIsValid(elemtype))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("FOREACH expression must yield an array, not type %s",
+ format_type_be(rec->tupdesc->attrs[0]->atttypid))));
+
+ /* we can't know typmod now */
+ tupdesc = lookup_rowtype_tupdesc_noerror(elemtype, -1, true);
+ if (tupdesc != NULL)
+ {
+ if (rec->freetupdesc)
+ FreeTupleDesc(rec->tupdesc);
+ rec->tupdesc = CreateTupleDescCopy(tupdesc);
+ rec->freetupdesc = true;
+ ReleaseTupleDesc(tupdesc);
+ }
+ else
+ elog(ERROR, "cannot to identify real type for record type variable");
+ }
+
+ /*
+ * When record is assigned to composite type, then
+ * we should to unpack composite type.
+ */
+ if (rec->tupdesc->tdtypeid == RECORDOID &&
+ rec->tupdesc->tdtypmod == -1 &&
+ rec->tupdesc->natts == 1 &&
+ stmt->cmd_type == PLPGSQL_STMT_ASSIGN)
+ {
+ TupleDesc tupdesc;
+
+ tupdesc = lookup_rowtype_tupdesc_noerror(rec->tupdesc->attrs[0]->atttypid,
+ rec->tupdesc->attrs[0]->atttypmod,
+ true);
+ if (tupdesc != NULL)
+ {
+ if (rec->freetupdesc)
+ FreeTupleDesc(rec->tupdesc);
+ rec->tupdesc = CreateTupleDescCopy(tupdesc);
+ rec->freetupdesc = true;
+ ReleaseTupleDesc(tupdesc);
+ }
+ }
+
+ /*
+ * When returned tupdesc contains only
+ * unpined record: rec := func_with_out_parameters(). IN this case
+ * we must to dig more deep - we have to find oid of function and
+ * get their parameters,
+ *
+ * This is support for assign statement
+ * recvar := func_with_out_parameters(..)
+ */
+ if (rec->tupdesc->tdtypeid == RECORDOID &&
+ rec->tupdesc->tdtypmod == -1 &&
+ rec->tupdesc->natts == 1 &&
+ rec->tupdesc->attrs[0]->atttypid == RECORDOID &&
+ rec->tupdesc->attrs[0]->atttypmod == -1)
+ {
+ PlannedStmt *_stmt;
+ Plan *_plan;
+ TargetEntry *tle;
+
+ /*
+ * When tupdesc is related to unpined record, we will try
+ * to check plan if it is just function call and if it is
+ * then we can try to derive a tupledes from function's
+ * description.
+ */
+ _stmt = (PlannedStmt *) linitial(plansource->plan->stmt_list);
+
+ if (IsA(_stmt, PlannedStmt) && _stmt->commandType == CMD_SELECT)
+ {
+ _plan = _stmt->planTree;
+ if (IsA(_plan, Result) && list_length(_plan->targetlist) == 1)
+ {
+ tle = (TargetEntry *) linitial(_plan->targetlist);
+ if (((Node *) tle->expr)->type == T_FuncExpr)
+ {
+ FuncExpr *fn = (FuncExpr *) tle->expr;
+ FmgrInfo flinfo;
+ FunctionCallInfoData fcinfo;
+ TupleDesc rd;
+ Oid rt;
+
+ fmgr_info(fn->funcid, &flinfo);
+ flinfo.fn_expr = (Node *) fn;
+ fcinfo.flinfo = &flinfo;
+
+ get_call_result_type(&fcinfo, &rt, &rd);
+ if (rd == NULL)
+ elog(ERROR, "function does not return composite type is not possible to identify composite type");
+
+ FreeTupleDesc(rec->tupdesc);
+ BlessTupleDesc(rd);
+
+ rec->tupdesc = rd;
+ }
+ }
+ }
+ }
+
+ /* last recheck */
+ if (rec->tupdesc->tdtypeid == RECORDOID &&
+ rec->tupdesc->tdtypmod == -1 &&
+ rec->tupdesc->natts == 1 &&
+ rec->tupdesc->attrs[0]->atttypid == RECORDOID &&
+ rec->tupdesc->attrs[0]->atttypmod == -1)
+ elog(ERROR, "cannot to identify real type for record type variable");
+
+ /* initialize rec by NULLs - variable should be valid */
+ nulls = (bool *) palloc(rec->tupdesc->natts * sizeof(bool));
+ memset(nulls, true, rec->tupdesc->natts * sizeof(bool));
+
+ tup = heap_form_tuple(rec->tupdesc, NULL, nulls);
+ if (HeapTupleIsValid(tup))
+ {
+ rec->tup = tup;
+ rec->freetup = true;
+ }
+ else
+ {
+ rec->tup = NULL;
+ rec->freetup = false;
+ }
+
+ estate->err_text = err_text;
+ }
+
+ static bool
+ expr_prepare_plan(PLpgSQL_stmt *stmt, PLpgSQL_expr *expr, void *context)
+ {
+ PLpgSQL_execstate *estate = (PLpgSQL_execstate *) context;
+ int cursorOptions = 0;
+ const char *err_text = estate->err_text;
+
+ /* overwrite a estate variables */
+ estate->err_text = NULL;
+ estate->err_stmt = stmt;
+
+ if (expr == NULL)
+ return false;
+
+ switch (stmt->cmd_type)
+ {
+ case PLPGSQL_STMT_OPEN:
+ {
+ PLpgSQL_stmt_open *stmt_open = (PLpgSQL_stmt_open *) stmt;
+ PLpgSQL_var *curvar = (PLpgSQL_var *) estate->datums[stmt_open->curvar];
+
+ cursorOptions = curvar->cursor_options;
+ }
+ break;
+
+ case PLPGSQL_STMT_FORC:
+ {
+ PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt;
+ PLpgSQL_var *curvar = (PLpgSQL_var *) estate->datums[stmt_forc->curvar];
+
+ /*
+ * change a cursorOption only whenn this call is related to
+ * curvar->cursor_explicit_expr
+ */
+ if (curvar->cursor_explicit_expr == expr)
+ cursorOptions = curvar->cursor_options;
+ }
+ break;
+ }
+
+ /*
+ * If first time through, create a plan for this expression.
+ */
+ if (expr->plan == NULL)
+ {
+ exec_prepare_plan(estate, expr, cursorOptions);
+ }
+
+ /*
+ * very common practic in PLpgSQL is is using a record type. But any using of
+ * untyped record breaks a check. A solution is an prediction of record type based
+ * on plans - a following switch covers all PLpgSQL statements where a record
+ * variable can be assigned.
+ *
+ * when record is target of dynamic SQL statement, then raise exception
+ *
+ */
+ switch (stmt->cmd_type)
+ {
+ case PLPGSQL_STMT_ASSIGN:
+ {
+ PLpgSQL_stmt_assign *stmt_assign = (PLpgSQL_stmt_assign *) stmt;
+ PLpgSQL_datum *target = (estate->datums[stmt_assign->varno]);
+
+ if (target->dtype == PLPGSQL_DTYPE_REC)
+ {
+ set_rectype_for_query(estate, stmt,
+ (PLpgSQL_rec *) target,
+ stmt_assign->expr,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_EXECSQL:
+ {
+ PLpgSQL_stmt_execsql *stmt_execsql = (PLpgSQL_stmt_execsql *) stmt;
+ ListCell *l;
+
+ stmt_execsql->mod_stmt = false;
+ foreach(l, expr->plan->plancache_list)
+ {
+ CachedPlanSource *plansource = (CachedPlanSource *) lfirst(l);
+ ListCell *l2;
+
+ foreach(l2, plansource->plan->stmt_list)
+ {
+ PlannedStmt *p = (PlannedStmt *) lfirst(l2);
+
+ if (IsA(p, PlannedStmt) &&
+ p->canSetTag)
+ {
+ if (p->commandType == CMD_INSERT ||
+ p->commandType == CMD_UPDATE ||
+ p->commandType == CMD_DELETE)
+ stmt_execsql->mod_stmt = true;
+ }
+ }
+ }
+
+ if (stmt_execsql->rec != NULL)
+ {
+ set_rectype_for_query(estate, stmt,
+ (PLpgSQL_rec *) (estate->datums[stmt_execsql->rec->dno]),
+ stmt_execsql->sqlstmt,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_FETCH:
+ {
+ PLpgSQL_stmt_fetch *stmt_fetch = (PLpgSQL_stmt_fetch *) stmt;
+
+ /* fetch can not determinate a record datatype for refcursors */
+ if (stmt_fetch->rec != NULL)
+ {
+ PLpgSQL_var *curvar = (PLpgSQL_var *)( estate->datums[stmt_fetch->curvar]);
+ PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[stmt_fetch->rec->dno]);
+
+ if (curvar->cursor_explicit_expr == NULL)
+ elog(ERROR, "cannot to determinate record type for refcursor");
+
+ set_rectype_for_query(estate, stmt,
+ rec,
+ curvar->cursor_explicit_expr,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_FORS:
+ {
+ PLpgSQL_stmt_fors *stmt_fors = (PLpgSQL_stmt_fors *) stmt;
+
+ if (stmt_fors->rec != NULL)
+ {
+ set_rectype_for_query(estate, stmt,
+ (PLpgSQL_rec *) (estate->datums[stmt_fors->rec->dno]),
+ stmt_fors->query,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_FORC:
+ {
+ PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt;
+ PLpgSQL_var *curvar = (PLpgSQL_var *) (estate->datums[stmt_forc->curvar]);
+
+ if (stmt_forc->rec != NULL && curvar->cursor_explicit_expr == expr)
+ {
+ PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[stmt_forc->rec->dno]);
+
+ set_rectype_for_query(estate, stmt,
+ rec,
+ curvar->cursor_explicit_expr,
+ false);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_FOREACH_A:
+ {
+ PLpgSQL_stmt_foreach_a *stmt_foreach_a = (PLpgSQL_stmt_foreach_a *) stmt;
+ PLpgSQL_datum *loop_var = estate->datums[stmt_foreach_a->varno];
+
+ if (loop_var->dtype == PLPGSQL_DTYPE_REC)
+ {
+ set_rectype_for_query(estate, stmt,
+ (PLpgSQL_rec *) loop_var,
+ stmt_foreach_a->expr,
+ true);
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_CASE:
+ {
+ PLpgSQL_stmt_case *stmt_case = (PLpgSQL_stmt_case *) stmt;
+ TupleDesc tupdesc;
+ Oid result_oid;
+
+ /*
+ * this is special case - a result type of expression should to
+ * overwrite a expected int datatype.
+ */
+ if (stmt_case->t_expr == expr)
+ {
+ CachedPlanSource *plansource = NULL;
+ const char *err_text = estate->err_text;
+
+ estate->err_text = NULL;
+ estate->err_stmt = stmt;
+
+
+ if (expr->plan != NULL)
+ {
+ SPIPlanPtr plan = expr->plan;
+ PLpgSQL_var *t_var = (PLpgSQL_var *) estate->datums[stmt_case->t_varno];
+
+ if (plan == NULL || plan->magic != _SPI_PLAN_MAGIC)
+ elog(ERROR, "cached plan is not valid plan");
+
+ if (list_length(plan->plancache_list) != 1)
+ elog(ERROR, "plan is not single execution plan");
+
+ plansource = (CachedPlanSource *) linitial(plan->plancache_list);
+ tupdesc = CreateTupleDescCopy(plansource->resultDesc);
+
+ if (tupdesc->natts != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg_plural("query \"%s\" returned %d column",
+ "query \"%s\" returned %d columns",
+ tupdesc->natts,
+ expr->query,
+ tupdesc->natts)));
+
+ result_oid = tupdesc->attrs[0]->atttypid;
+
+ /*
+ * When expected datatype is different from real, change it. Note that
+ * what we're modifying here is an execution copy of the datum, so
+ * this doesn't affect the originally stored function parse tree.
+ */
+ if (t_var->datatype->typoid != result_oid)
+ t_var->datatype = plpgsql_build_datatype(result_oid,
+ -1,
+ estate->func->fn_input_collation);
+ FreeTupleDesc(tupdesc);
+ }
+ else
+ elog(ERROR, "there are no plan for query: \"%s\"",
+ expr->query);
+
+ estate->err_text = err_text;
+ }
+ }
+ break;
+
+ case PLPGSQL_STMT_DYNEXECUTE:
+ {
+ PLpgSQL_stmt_dynexecute *stmt_dynexecute = (PLpgSQL_stmt_dynexecute *) stmt;
+
+ if (stmt_dynexecute->into && stmt_dynexecute->rec != NULL)
+ elog(ERROR, "cannot to determine a result of dynamic SQL");
+ }
+ break;
+
+ case PLPGSQL_STMT_DYNFORS:
+ {
+ PLpgSQL_stmt_dynfors *stmt_dynfors = (PLpgSQL_stmt_dynfors *) stmt;
+
+ if (stmt_dynfors->rec != NULL)
+ elog(ERROR, "cannot to determinate a result of dynamic SQL");
+ }
+ break;
+ }
+
+ estate->err_text = err_text;
+
+ return false;
+ }
*** a/src/pl/plpgsql/src/pl_funcs.c
--- b/src/pl/plpgsql/src/pl_funcs.c
***************
*** 292,681 **** plpgsql_getdiag_kindname(int kind)
return "unknown";
}
!
! /**********************************************************************
! * Release memory when a PL/pgSQL function is no longer needed
*
! * The code for recursing through the function tree is really only
! * needed to locate PLpgSQL_expr nodes, which may contain references
! * to saved SPI Plans that must be freed. The function tree itself,
! * along with subsidiary data, is freed in one swoop by freeing the
! * function's permanent memory context.
! **********************************************************************/
! static void free_stmt(PLpgSQL_stmt *stmt);
! static void free_block(PLpgSQL_stmt_block *block);
! static void free_assign(PLpgSQL_stmt_assign *stmt);
! static void free_if(PLpgSQL_stmt_if *stmt);
! static void free_case(PLpgSQL_stmt_case *stmt);
! static void free_loop(PLpgSQL_stmt_loop *stmt);
! static void free_while(PLpgSQL_stmt_while *stmt);
! static void free_fori(PLpgSQL_stmt_fori *stmt);
! static void free_fors(PLpgSQL_stmt_fors *stmt);
! static void free_forc(PLpgSQL_stmt_forc *stmt);
! static void free_foreach_a(PLpgSQL_stmt_foreach_a *stmt);
! static void free_exit(PLpgSQL_stmt_exit *stmt);
! static void free_return(PLpgSQL_stmt_return *stmt);
! static void free_return_next(PLpgSQL_stmt_return_next *stmt);
! static void free_return_query(PLpgSQL_stmt_return_query *stmt);
! static void free_raise(PLpgSQL_stmt_raise *stmt);
! static void free_execsql(PLpgSQL_stmt_execsql *stmt);
! static void free_dynexecute(PLpgSQL_stmt_dynexecute *stmt);
! static void free_dynfors(PLpgSQL_stmt_dynfors *stmt);
! static void free_getdiag(PLpgSQL_stmt_getdiag *stmt);
! static void free_open(PLpgSQL_stmt_open *stmt);
! static void free_fetch(PLpgSQL_stmt_fetch *stmt);
! static void free_close(PLpgSQL_stmt_close *stmt);
! static void free_perform(PLpgSQL_stmt_perform *stmt);
! static void free_expr(PLpgSQL_expr *expr);
! static void
! free_stmt(PLpgSQL_stmt *stmt)
{
switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
{
case PLPGSQL_STMT_BLOCK:
! free_block((PLpgSQL_stmt_block *) stmt);
! break;
case PLPGSQL_STMT_ASSIGN:
! free_assign((PLpgSQL_stmt_assign *) stmt);
! break;
case PLPGSQL_STMT_IF:
! free_if((PLpgSQL_stmt_if *) stmt);
! break;
case PLPGSQL_STMT_CASE:
! free_case((PLpgSQL_stmt_case *) stmt);
! break;
case PLPGSQL_STMT_LOOP:
! free_loop((PLpgSQL_stmt_loop *) stmt);
! break;
case PLPGSQL_STMT_WHILE:
! free_while((PLpgSQL_stmt_while *) stmt);
! break;
case PLPGSQL_STMT_FORI:
! free_fori((PLpgSQL_stmt_fori *) stmt);
! break;
case PLPGSQL_STMT_FORS:
! free_fors((PLpgSQL_stmt_fors *) stmt);
! break;
case PLPGSQL_STMT_FORC:
! free_forc((PLpgSQL_stmt_forc *) stmt);
! break;
! case PLPGSQL_STMT_FOREACH_A:
! free_foreach_a((PLpgSQL_stmt_foreach_a *) stmt);
! break;
! case PLPGSQL_STMT_EXIT:
! free_exit((PLpgSQL_stmt_exit *) stmt);
! break;
! case PLPGSQL_STMT_RETURN:
! free_return((PLpgSQL_stmt_return *) stmt);
! break;
! case PLPGSQL_STMT_RETURN_NEXT:
! free_return_next((PLpgSQL_stmt_return_next *) stmt);
! break;
! case PLPGSQL_STMT_RETURN_QUERY:
! free_return_query((PLpgSQL_stmt_return_query *) stmt);
! break;
! case PLPGSQL_STMT_RAISE:
! free_raise((PLpgSQL_stmt_raise *) stmt);
! break;
! case PLPGSQL_STMT_EXECSQL:
! free_execsql((PLpgSQL_stmt_execsql *) stmt);
! break;
! case PLPGSQL_STMT_DYNEXECUTE:
! free_dynexecute((PLpgSQL_stmt_dynexecute *) stmt);
! break;
case PLPGSQL_STMT_DYNFORS:
! free_dynfors((PLpgSQL_stmt_dynfors *) stmt);
! break;
! case PLPGSQL_STMT_GETDIAG:
! free_getdiag((PLpgSQL_stmt_getdiag *) stmt);
! break;
! case PLPGSQL_STMT_OPEN:
! free_open((PLpgSQL_stmt_open *) stmt);
! break;
! case PLPGSQL_STMT_FETCH:
! free_fetch((PLpgSQL_stmt_fetch *) stmt);
! break;
! case PLPGSQL_STMT_CLOSE:
! free_close((PLpgSQL_stmt_close *) stmt);
! break;
! case PLPGSQL_STMT_PERFORM:
! free_perform((PLpgSQL_stmt_perform *) stmt);
! break;
! default:
! elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type);
! break;
! }
! }
! static void
! free_stmts(List *stmts)
! {
! ListCell *s;
! foreach(s, stmts)
! {
! free_stmt((PLpgSQL_stmt *) lfirst(s));
! }
! }
! static void
! free_block(PLpgSQL_stmt_block *block)
! {
! free_stmts(block->body);
! if (block->exceptions)
! {
! ListCell *e;
! foreach(e, block->exceptions->exc_list)
! {
! PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e);
! free_stmts(exc->action);
! }
! }
! }
! static void
! free_assign(PLpgSQL_stmt_assign *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_if(PLpgSQL_stmt_if *stmt)
! {
! free_expr(stmt->cond);
! free_stmts(stmt->true_body);
! free_stmts(stmt->false_body);
! }
! static void
! free_case(PLpgSQL_stmt_case *stmt)
! {
! ListCell *l;
! free_expr(stmt->t_expr);
! foreach(l, stmt->case_when_list)
! {
! PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l);
! free_expr(cwt->expr);
! free_stmts(cwt->stmts);
! }
! free_stmts(stmt->else_stmts);
! }
! static void
! free_loop(PLpgSQL_stmt_loop *stmt)
! {
! free_stmts(stmt->body);
! }
! static void
! free_while(PLpgSQL_stmt_while *stmt)
! {
! free_expr(stmt->cond);
! free_stmts(stmt->body);
! }
! static void
! free_fori(PLpgSQL_stmt_fori *stmt)
! {
! free_expr(stmt->lower);
! free_expr(stmt->upper);
! free_expr(stmt->step);
! free_stmts(stmt->body);
! }
! static void
! free_fors(PLpgSQL_stmt_fors *stmt)
! {
! free_stmts(stmt->body);
! free_expr(stmt->query);
! }
! static void
! free_forc(PLpgSQL_stmt_forc *stmt)
! {
! free_stmts(stmt->body);
! free_expr(stmt->argquery);
! }
! static void
! free_foreach_a(PLpgSQL_stmt_foreach_a *stmt)
! {
! free_expr(stmt->expr);
! free_stmts(stmt->body);
! }
! static void
! free_open(PLpgSQL_stmt_open *stmt)
! {
! ListCell *lc;
! free_expr(stmt->argquery);
! free_expr(stmt->query);
! free_expr(stmt->dynquery);
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
! }
! }
! static void
! free_fetch(PLpgSQL_stmt_fetch *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_close(PLpgSQL_stmt_close *stmt)
! {
! }
! static void
! free_perform(PLpgSQL_stmt_perform *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_exit(PLpgSQL_stmt_exit *stmt)
! {
! free_expr(stmt->cond);
! }
! static void
! free_return(PLpgSQL_stmt_return *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_return_next(PLpgSQL_stmt_return_next *stmt)
! {
! free_expr(stmt->expr);
! }
! static void
! free_return_query(PLpgSQL_stmt_return_query *stmt)
! {
! ListCell *lc;
! free_expr(stmt->query);
! free_expr(stmt->dynquery);
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
! }
! }
! static void
! free_raise(PLpgSQL_stmt_raise *stmt)
! {
! ListCell *lc;
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
! }
! foreach(lc, stmt->options)
! {
! PLpgSQL_raise_option *opt = (PLpgSQL_raise_option *) lfirst(lc);
! free_expr(opt->expr);
! }
! }
! static void
! free_execsql(PLpgSQL_stmt_execsql *stmt)
! {
! free_expr(stmt->sqlstmt);
! }
! static void
! free_dynexecute(PLpgSQL_stmt_dynexecute *stmt)
! {
! ListCell *lc;
! free_expr(stmt->query);
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
! }
! }
! static void
! free_dynfors(PLpgSQL_stmt_dynfors *stmt)
! {
! ListCell *lc;
! free_stmts(stmt->body);
! free_expr(stmt->query);
! foreach(lc, stmt->params)
! {
! free_expr((PLpgSQL_expr *) lfirst(lc));
}
}
! static void
! free_getdiag(PLpgSQL_stmt_getdiag *stmt)
! {
! }
! static void
! free_expr(PLpgSQL_expr *expr)
{
if (expr && expr->plan)
{
SPI_freeplan(expr->plan);
expr->plan = NULL;
}
}
void
plpgsql_free_function_memory(PLpgSQL_function *func)
{
- int i;
-
/* Better not call this on an in-use function */
Assert(func->use_count == 0);
! /* Release plans associated with variable declarations */
! for (i = 0; i < func->ndatums; i++)
! {
! PLpgSQL_datum *d = func->datums[i];
!
! switch (d->dtype)
! {
! case PLPGSQL_DTYPE_VAR:
! {
! PLpgSQL_var *var = (PLpgSQL_var *) d;
- free_expr(var->default_val);
- free_expr(var->cursor_explicit_expr);
- }
- break;
- case PLPGSQL_DTYPE_ROW:
- break;
- case PLPGSQL_DTYPE_REC:
- break;
- case PLPGSQL_DTYPE_RECFIELD:
- break;
- case PLPGSQL_DTYPE_ARRAYELEM:
- free_expr(((PLpgSQL_arrayelem *) d)->subscript);
- break;
- default:
- elog(ERROR, "unrecognized data type: %d", d->dtype);
- }
- }
func->ndatums = 0;
-
- /* Release plans in statement tree */
- if (func->action)
- free_block(func->action);
func->action = NULL;
/*
--- 292,653 ----
return "unknown";
}
! /*
! * call a plpgsql_expr_walker for any statement in list
*
! */
! static bool
! plpgsql_expr_walker_list(PLpgSQL_function *func, List *stmts,
! bool (*expr_walker)(),
! void *context)
! {
! ListCell *lc;
+ foreach(lc, stmts)
+ {
+ PLpgSQL_stmt *stmt = (PLpgSQL_stmt *) lfirst(lc);
! if (plpgsql_expr_walker(func, stmt, expr_walker, context))
! return true;
! }
! return false;
! }
!
! /*
! * walk over all expressions inside statements tree
! *
! * stmt_walker is function called for every stmt and should be NULL
! *
! */
! bool
! plpgsql_expr_walker(PLpgSQL_function *func,
! PLpgSQL_stmt *stmt,
! bool (*expr_walker)(),
! void *context)
{
+ ListCell *l;
+
+ if (stmt == NULL)
+ return false;
+
switch ((enum PLpgSQL_stmt_types) stmt->cmd_type)
{
case PLPGSQL_STMT_BLOCK:
! {
! PLpgSQL_stmt_block *stmt_block = (PLpgSQL_stmt_block *) stmt;
! int i;
! PLpgSQL_datum *d;
!
! for (i = 0; i < stmt_block->n_initvars; i++)
! {
! d = func->datums[stmt_block->initvarnos[i]];
!
! switch(d->dtype)
! {
! case PLPGSQL_DTYPE_VAR:
! {
! PLpgSQL_var *var = (PLpgSQL_var *) d;
!
! if (expr_walker(stmt, var->default_val, context))
! return true;
!
! /*
! * theoretically there is place for walk over var->cursor_explicit_expr,
! * but we would not to call process too early. In this moment a some
! * record parameters should be unknown. So we will wait on better moment
! *
! * if (expr_walker(stmt, var->cursor_explicit_expr, context))
! * return true;
! */
! }
! break;
! case PLPGSQL_DTYPE_ROW:
! case PLPGSQL_DTYPE_REC:
! case PLPGSQL_DTYPE_RECFIELD:
! break;
! case PLPGSQL_DTYPE_ARRAYELEM:
! if (expr_walker(stmt, ((PLpgSQL_arrayelem *) d)->subscript, context))
! return true;
! default:
! elog(ERROR, "unrecognized data type: %d", d->dtype);
! }
! }
!
! if (plpgsql_expr_walker_list(func, stmt_block->body, expr_walker, context))
! return true;
!
! if (stmt_block->exceptions)
! {
! foreach(l, stmt_block->exceptions->exc_list)
! {
! if (plpgsql_expr_walker_list(func, ((PLpgSQL_exception *) lfirst(l))->action,
! expr_walker,
! context))
! return true;
! }
! }
!
! return false;
! }
!
case PLPGSQL_STMT_ASSIGN:
! return expr_walker(stmt, ((PLpgSQL_stmt_assign *) stmt)->expr, context);
!
case PLPGSQL_STMT_IF:
! {
! PLpgSQL_stmt_if *stmt_if = (PLpgSQL_stmt_if *) stmt;
!
! if (expr_walker(stmt, stmt_if->cond, context))
! return true;
!
! if (plpgsql_expr_walker_list(func, stmt_if->true_body, expr_walker, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_if->false_body, expr_walker, context);
! }
!
case PLPGSQL_STMT_CASE:
! {
! PLpgSQL_stmt_case *stmt_case = (PLpgSQL_stmt_case *) stmt;
!
! if (expr_walker(stmt, stmt_case->t_expr, context))
! return true;
!
! foreach(l, stmt_case->case_when_list)
! {
! PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l);
!
! if (expr_walker(stmt, cwt->expr, context))
! return true;
!
! if (plpgsql_expr_walker_list(func, cwt->stmts, expr_walker, context))
! return true;
! }
!
! return plpgsql_expr_walker_list(func, stmt_case->else_stmts, expr_walker, context);
! }
!
case PLPGSQL_STMT_LOOP:
! return plpgsql_expr_walker_list(func, ((PLpgSQL_stmt_loop *) stmt)->body, expr_walker, context);
!
case PLPGSQL_STMT_WHILE:
! {
! PLpgSQL_stmt_while *stmt_while = (PLpgSQL_stmt_while *) stmt;
!
! if (expr_walker(stmt, stmt_while->cond, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_while->body, expr_walker, context);
! }
!
case PLPGSQL_STMT_FORI:
! {
! PLpgSQL_stmt_fori *stmt_fori = (PLpgSQL_stmt_fori *) stmt;
!
! if (expr_walker(stmt, stmt_fori->lower, context))
! return true;
!
! if (expr_walker(stmt, stmt_fori->upper, context))
! return true;
!
! if (expr_walker(stmt, stmt_fori->step, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_fori->body, expr_walker, context);
! }
!
case PLPGSQL_STMT_FORS:
! {
! PLpgSQL_stmt_fors *stmt_fors = (PLpgSQL_stmt_fors *) stmt;
!
! if (expr_walker(stmt, stmt_fors->query, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_fors->body, expr_walker, context);
! }
!
case PLPGSQL_STMT_FORC:
! {
! PLpgSQL_stmt_forc *stmt_forc = (PLpgSQL_stmt_forc *) stmt;
! PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_forc->curvar];
!
! if (expr_walker(stmt, stmt_forc->argquery, context))
! return true;
!
! if (expr_walker(stmt, var->cursor_explicit_expr, context))
! return true;
!
! return plpgsql_expr_walker_list(func, stmt_forc->body, expr_walker, context);
! }
!
case PLPGSQL_STMT_DYNFORS:
! {
! PLpgSQL_stmt_dynfors * stmt_dynfors = (PLpgSQL_stmt_dynfors *) stmt;
! if (expr_walker(stmt, stmt_dynfors->query, context))
! return true;
! foreach(l, stmt_dynfors->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! return plpgsql_expr_walker_list(func, stmt_dynfors->body, expr_walker, context);
! }
! case PLPGSQL_STMT_FOREACH_A:
! {
! PLpgSQL_stmt_foreach_a *stmt_foreach_a = (PLpgSQL_stmt_foreach_a *) stmt;
! if (expr_walker(stmt, stmt_foreach_a->expr, context))
! return true;
! return plpgsql_expr_walker_list(func, stmt_foreach_a->body, expr_walker, context);
! }
! case PLPGSQL_STMT_EXIT:
! return expr_walker(stmt, ((PLpgSQL_stmt_exit *) stmt)->cond, context);
! case PLPGSQL_STMT_PERFORM:
! return expr_walker(stmt, ((PLpgSQL_stmt_perform *) stmt)->expr, context);
! case PLPGSQL_STMT_RETURN:
! return expr_walker(stmt, ((PLpgSQL_stmt_return *) stmt)->expr, context);
! case PLPGSQL_STMT_RETURN_NEXT:
! return expr_walker(stmt, ((PLpgSQL_stmt_return_next *) stmt)->expr, context);
! case PLPGSQL_STMT_RETURN_QUERY:
! {
! PLpgSQL_stmt_return_query *stmt_rq = (PLpgSQL_stmt_return_query *) stmt;
! if (expr_walker(stmt, stmt_rq->query, context))
! return true;
! if (expr_walker(stmt, stmt_rq->dynquery, context))
! return true;
! foreach(l, stmt_rq->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! return false;
! }
! case PLPGSQL_STMT_RAISE:
! {
! PLpgSQL_stmt_raise *stmt_raise = (PLpgSQL_stmt_raise *) stmt;
! foreach(l, stmt_raise->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! foreach(l, stmt_raise->options)
! {
! if (expr_walker(stmt, ((PLpgSQL_raise_option *) lfirst(l))->expr, context))
! return true;
! }
! return false;
! }
! case PLPGSQL_STMT_EXECSQL:
! return expr_walker(stmt, ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt, context);
! case PLPGSQL_STMT_DYNEXECUTE:
! {
! PLpgSQL_stmt_dynexecute *stmt_dynexecute = (PLpgSQL_stmt_dynexecute *) stmt;
! if (expr_walker(stmt, stmt_dynexecute->query, context))
! return true;
! foreach(l, stmt_dynexecute->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! return false;
! }
! break;
! case PLPGSQL_STMT_GETDIAG:
! return false;
! case PLPGSQL_STMT_OPEN:
! {
! PLpgSQL_stmt_open *stmt_open = (PLpgSQL_stmt_open *) stmt;
! PLpgSQL_var *var = (PLpgSQL_var *) func->datums[stmt_open->curvar];
! if (expr_walker(stmt, var->cursor_explicit_expr, context))
! return true;
! if (expr_walker(stmt, stmt_open->query, context))
! return true;
! if (expr_walker(stmt, stmt_open->dynquery, context))
! return true;
! if (expr_walker(stmt, stmt_open->argquery, context))
! return true;
! foreach(l, stmt_open->params)
! {
! if (expr_walker(stmt, (PLpgSQL_expr *) lfirst(l), context))
! return true;
! }
! return false;
! }
! case PLPGSQL_STMT_FETCH:
! case PLPGSQL_STMT_CLOSE:
! return false;
! default:
! elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type);
! return false; /* be compiler quite */
}
}
! /**********************************************************************
! * Release memory when a PL/pgSQL function is no longer needed
! *
! * The code for recursing through the function tree is really only
! * needed to locate PLpgSQL_expr nodes, which may contain references
! * to saved SPI Plans that must be freed. The function tree itself,
! * along with subsidiary data, is freed in one swoop by freeing the
! * function's permanent memory context.
! **********************************************************************/
! static bool
! free_expression(PLpgSQL_stmt *stmt, PLpgSQL_expr *expr, void *context)
{
if (expr && expr->plan)
{
SPI_freeplan(expr->plan);
expr->plan = NULL;
}
+ return false;
}
void
plpgsql_free_function_memory(PLpgSQL_function *func)
{
/* Better not call this on an in-use function */
Assert(func->use_count == 0);
! /* Release plans in statement tree */
! plpgsql_expr_walker(func, (PLpgSQL_stmt *) func->action,
! free_expression,
! NULL);
func->ndatums = 0;
func->action = NULL;
/*
*** a/src/pl/plpgsql/src/pl_handler.c
--- b/src/pl/plpgsql/src/pl_handler.c
***************
*** 34,40 **** static const struct config_enum_entry variable_conflict_options[] = {
--- 34,47 ----
{NULL, 0, false}
};
+ static const struct config_enum_entry prepare_plans_options[] = {
+ {"on_demand", PLPGSQL_PREPARE_PLANS_ON_DEMAND, false},
+ {"on_start", PLPGSQL_PREPARE_PLANS_ON_START, false},
+ {NULL, 0, false}
+ };
+
int plpgsql_variable_conflict = PLPGSQL_RESOLVE_ERROR;
+ int plpgsql_prepare_plans = PLPGSQL_PREPARE_PLANS_ON_DEMAND;
/* Hook for plugins */
PLpgSQL_plugin **plugin_ptr = NULL;
***************
*** 65,70 **** _PG_init(void)
--- 72,87 ----
PGC_SUSET, 0,
NULL, NULL, NULL);
+ DefineCustomEnumVariable("plpgsql.prepare_plans",
+ gettext_noop("Sets timing of preparation plans of embeded SQL."),
+ NULL,
+ &plpgsql_prepare_plans,
+ PLPGSQL_PREPARE_PLANS_ON_DEMAND,
+ prepare_plans_options,
+ PGC_SUSET, 0,
+ NULL, NULL, NULL);
+
+
EmitWarningsOnPlaceholders("plpgsql");
plpgsql_HashTableInit();
*** a/src/pl/plpgsql/src/pl_scanner.c
--- b/src/pl/plpgsql/src/pl_scanner.c
***************
*** 129,138 **** static const ScanKeyword unreserved_keywords[] = {
--- 129,141 ----
PG_KEYWORD("next", K_NEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("no", K_NO, UNRESERVED_KEYWORD)
PG_KEYWORD("notice", K_NOTICE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("on_demand", K_ON_DEMAND, UNRESERVED_KEYWORD)
+ PG_KEYWORD("on_start", K_ON_START, UNRESERVED_KEYWORD)
PG_KEYWORD("option", K_OPTION, UNRESERVED_KEYWORD)
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("prepare_plans", K_PREPARE_PLANS, UNRESERVED_KEYWORD)
PG_KEYWORD("prior", K_PRIOR, UNRESERVED_KEYWORD)
PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD)
PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD)
*** a/src/pl/plpgsql/src/plpgsql.h
--- b/src/pl/plpgsql/src/plpgsql.h
***************
*** 157,162 **** typedef enum
--- 157,173 ----
PLPGSQL_RESOLVE_COLUMN /* prefer table column to plpgsql var */
} PLpgSQL_resolve_option;
+ /* --------
+ * Behavioral modes for plpgsql execution plan timing
+ * --------
+ */
+ typedef enum
+ {
+ PLPGSQL_PREPARE_PLANS_UNDEF,
+ PLPGSQL_PREPARE_PLANS_ON_DEMAND, /* prepare plan when it is necessary (default) */
+ PLPGSQL_PREPARE_PLANS_ON_START /* prepare all plans on start */
+ } PLpgSQL_prepare_plans_option;
+
/**********************************************************************
* Node and structure definitions
***************
*** 700,705 **** typedef struct PLpgSQL_function
--- 711,717 ----
int tg_argv_varno;
PLpgSQL_resolve_option resolve_option;
+ PLpgSQL_prepare_plans_option prepare_plans_option;
int ndatums;
PLpgSQL_datum **datums;
***************
*** 836,841 **** typedef enum
--- 848,854 ----
extern IdentifierLookup plpgsql_IdentifierLookup;
extern int plpgsql_variable_conflict;
+ extern int plpgsql_prepare_plans;
extern bool plpgsql_check_syntax;
extern bool plpgsql_DumpExecTree;
***************
*** 930,935 **** extern PLpgSQL_nsitem *plpgsql_ns_lookup(PLpgSQL_nsitem *ns_cur, bool localmode,
--- 943,953 ----
extern PLpgSQL_nsitem *plpgsql_ns_lookup_label(PLpgSQL_nsitem *ns_cur,
const char *name);
+ extern bool plpgsql_expr_walker(PLpgSQL_function *func,
+ PLpgSQL_stmt *stmt,
+ bool (*expr_walker)(),
+ void *context);
+
/* ----------
* Other functions in pl_funcs.c
* ----------
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
***************
*** 1,3 ****
--- 1,5 ----
+ load 'plpgsql';
+ set plpgsql.prepare_plans to on_start;
--
-- PLPGSQL
--
***************
*** 2410,2415 **** drop function missing_return_expr();
--- 2412,2418 ----
create table eifoo (i integer, y integer);
create type eitype as (i integer, y integer);
create or replace function execute_into_test(varchar) returns record as $$
+ #prepare_plans on_demand
declare
_r record;
_rt eifoo%rowtype;
***************
*** 2469,2475 **** LINE 1: SELECT sqlstate
--- 2472,2486 ----
^
QUERY: SELECT sqlstate
CONTEXT: PL/pgSQL function "excpt_test2" line 5 at RAISE
+ /*
+ * this example has blocked a plan preparation on start
+ * because we would to demonstrate a nested trapping errors.
+ * When we use a plans generated on start, then we raise
+ * exception before we start a protected section, because
+ * exception is raised when plan is generated.
+ */
create function excpt_test3() returns void as $$
+ #prepare_plans on_demand
begin
begin
raise exception 'user exception';
***************
*** 2527,2532 **** insert into conttesttbl(v) values(20);
--- 2538,2544 ----
insert into conttesttbl(v) values(30);
insert into conttesttbl(v) values(40);
create function continue_test1() returns void as $$
+ #prepare_plans on_demand
declare _i integer = 0; _r record;
begin
raise notice '---1---';
***************
*** 2852,2857 **** select footest();
--- 2864,2870 ----
ERROR: query returned more than one row
CONTEXT: PL/pgSQL function "footest" line 5 at SQL statement
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- should work
***************
*** 2866,2871 **** NOTICE: x.f1 = 5, x.f2 = 6
--- 2879,2885 ----
(1 row)
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- this should work since EXECUTE isn't as picky
***************
*** 2925,2930 **** select footest();
--- 2939,2945 ----
ERROR: query returned more than one row
CONTEXT: PL/pgSQL function "footest" line 5 at SQL statement
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- should work
***************
*** 2939,2944 **** NOTICE: x.f1 = 3, x.f2 = 4
--- 2954,2960 ----
(1 row)
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- should fail, no rows
***************
*** 2947,2954 **** begin
end$$ language plpgsql;
select footest();
ERROR: query returned no rows
! CONTEXT: PL/pgSQL function "footest" line 5 at EXECUTE statement
create or replace function footest() returns void as $$
declare x record;
begin
-- should fail, too many rows
--- 2963,2971 ----
end$$ language plpgsql;
select footest();
ERROR: query returned no rows
! CONTEXT: PL/pgSQL function "footest" line 6 at EXECUTE statement
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- should fail, too many rows
***************
*** 2957,2963 **** begin
end$$ language plpgsql;
select footest();
ERROR: query returned more than one row
! CONTEXT: PL/pgSQL function "footest" line 5 at EXECUTE statement
drop function footest();
-- test scrollable cursor support
create function sc_test() returns setof integer as $$
--- 2974,2980 ----
end$$ language plpgsql;
select footest();
ERROR: query returned more than one row
! CONTEXT: PL/pgSQL function "footest" line 6 at EXECUTE statement
drop function footest();
-- test scrollable cursor support
create function sc_test() returns setof integer as $$
***************
*** 3027,3032 **** select * from sc_test();
--- 3044,3050 ----
(5 rows)
create or replace function sc_test() returns setof integer as $$
+ #prepare_plans on_demand
declare
c refcursor;
x integer;
***************
*** 3049,3054 **** select * from sc_test();
--- 3067,3073 ----
(3 rows)
create or replace function sc_test() returns setof integer as $$
+ #prepare_plans on_demand
declare
c refcursor;
x integer;
***************
*** 3220,3225 **** NOTICE: 6
--- 3239,3245 ----
drop function exc_using(int, text);
create or replace function exc_using(int) returns void as $$
+ #prepare_plans on_demand
declare
c refcursor;
i int;
***************
*** 3352,3357 **** select * from forc_test;
--- 3372,3378 ----
-- same, with a cursor whose portal name doesn't match variable name
create or replace function forc01() returns void as $$
+ #prepare_plans on_demand
declare
c refcursor := 'fooled_ya';
r record;
***************
*** 3685,3690 **** drop function stacked_diagnostics_test();
--- 3706,3712 ----
-- check cases where implicit SQLSTATE variable could be confused with
-- SQLSTATE as a keyword, cf bug #5524
create or replace function raise_test() returns void as $$
+ #prepare_plans on_demand
begin
perform 1/0;
exception
***************
*** 3772,3778 **** select case_test(13); -- fails
--- 3794,3807 ----
ERROR: case not found
HINT: CASE statement is missing ELSE part.
CONTEXT: PL/pgSQL function "case_test" line 5 at CASE
+ /*
+ * should to have blocked early plan preparation, because
+ * a case_test(6) is evaluated too early - it should be
+ * replaced by constant, and then it cannot to demonstrate
+ * a expected efect.
+ */
create or replace function catch() returns void as $$
+ #prepare_plans on_demand
begin
raise notice '%', case_test(6);
exception
***************
*** 4131,4143 **** LINE 4: return 'foo\\bar\041baz';
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
select strtest();
- NOTICE: foo\bar!baz
WARNING: nonstandard use of \\ in a string literal
LINE 1: SELECT 'foo\\bar\041baz'
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
QUERY: SELECT 'foo\\bar\041baz'
CONTEXT: PL/pgSQL function "strtest" line 4 at RETURN
strtest
-------------
foo\bar!baz
--- 4160,4172 ----
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
select strtest();
WARNING: nonstandard use of \\ in a string literal
LINE 1: SELECT 'foo\\bar\041baz'
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
QUERY: SELECT 'foo\\bar\041baz'
CONTEXT: PL/pgSQL function "strtest" line 4 at RETURN
+ NOTICE: foo\bar!baz
strtest
-------------
foo\bar!baz
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
***************
*** 1,3 ****
--- 1,6 ----
+ load 'plpgsql';
+ set plpgsql.prepare_plans to on_start;
+
--
-- PLPGSQL
--
***************
*** 2052,2057 **** create table eifoo (i integer, y integer);
--- 2055,2061 ----
create type eitype as (i integer, y integer);
create or replace function execute_into_test(varchar) returns record as $$
+ #prepare_plans on_demand
declare
_r record;
_rt eifoo%rowtype;
***************
*** 2099,2105 **** end; $$ language plpgsql;
--- 2103,2117 ----
-- should fail
select excpt_test2();
+ /*
+ * this example has blocked a plan preparation on start
+ * because we would to demonstrate a nested trapping errors.
+ * When we use a plans generated on start, then we raise
+ * exception before we start a protected section, because
+ * exception is raised when plan is generated.
+ */
create function excpt_test3() returns void as $$
+ #prepare_plans on_demand
begin
begin
raise exception 'user exception';
***************
*** 2146,2151 **** insert into conttesttbl(v) values(30);
--- 2158,2164 ----
insert into conttesttbl(v) values(40);
create function continue_test1() returns void as $$
+ #prepare_plans on_demand
declare _i integer = 0; _r record;
begin
raise notice '---1---';
***************
*** 2381,2386 **** end$$ language plpgsql;
--- 2394,2400 ----
select footest();
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- should work
***************
*** 2391,2396 **** end$$ language plpgsql;
--- 2405,2411 ----
select footest();
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- this should work since EXECUTE isn't as picky
***************
*** 2433,2438 **** end$$ language plpgsql;
--- 2448,2454 ----
select footest();
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- should work
***************
*** 2443,2448 **** end$$ language plpgsql;
--- 2459,2465 ----
select footest();
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- should fail, no rows
***************
*** 2453,2458 **** end$$ language plpgsql;
--- 2470,2476 ----
select footest();
create or replace function footest() returns void as $$
+ #prepare_plans on_demand
declare x record;
begin
-- should fail, too many rows
***************
*** 2518,2523 **** $$ language plpgsql;
--- 2536,2542 ----
select * from sc_test();
create or replace function sc_test() returns setof integer as $$
+ #prepare_plans on_demand
declare
c refcursor;
x integer;
***************
*** 2535,2540 **** $$ language plpgsql;
--- 2554,2560 ----
select * from sc_test();
create or replace function sc_test() returns setof integer as $$
+ #prepare_plans on_demand
declare
c refcursor;
x integer;
***************
*** 2657,2662 **** select exc_using(5, 'foobar');
--- 2677,2683 ----
drop function exc_using(int, text);
create or replace function exc_using(int) returns void as $$
+ #prepare_plans on_demand
declare
c refcursor;
i int;
***************
*** 2738,2743 **** select * from forc_test;
--- 2759,2765 ----
-- same, with a cursor whose portal name doesn't match variable name
create or replace function forc01() returns void as $$
+ #prepare_plans on_demand
declare
c refcursor := 'fooled_ya';
r record;
***************
*** 3014,3019 **** drop function stacked_diagnostics_test();
--- 3036,3042 ----
-- check cases where implicit SQLSTATE variable could be confused with
-- SQLSTATE as a keyword, cf bug #5524
create or replace function raise_test() returns void as $$
+ #prepare_plans on_demand
begin
perform 1/0;
exception
***************
*** 3059,3065 **** select case_test(11);
--- 3082,3095 ----
select case_test(12);
select case_test(13); -- fails
+ /*
+ * should to have blocked early plan preparation, because
+ * a case_test(6) is evaluated too early - it should be
+ * replaced by constant, and then it cannot to demonstrate
+ * a expected efect.
+ */
create or replace function catch() returns void as $$
+ #prepare_plans on_demand
begin
raise notice '%', case_test(6);
exception
Re: VIP: plpgsql - early embedded sql plan preparation
On Tue, Aug 23, 2011 at 6:30 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
I am returning back to discus two months ago about usage of
plpgsql_lint. I integrated this module to plpgsql's core. This feature
is controlled via plpgsql GUC variable prepare_plans and via plpgsql
option. It works with all plpgsql's regress tests without tests where
dynamic sql or refcursors are used. Early plan preparation is disabled
default so it should not to break current applications.This feature can live as contrib module too, but integration has one
advantage - there is still a possibility to use other plpgsql
extensions - mainly plpgsql debugger.I didn't work on documentation yet, so I there is small example:
CREATE TYPE tp AS (a int, b int);
CREATE OR REPLACE FUNCTION test()
RETURNS int AS $$
DECLARE v tp;
BEGIN
v := (10,20);
IF false THEN
RAISE NOTICE '%', v.z;
END IF;
RETURN v.a;
END;
$$ LANGUAGE plpgsql;postgres=# SELECT test();
test
------
10
(1 row)with enabled early planning it found a bug in not executed code
CREATE OR REPLACE FUNCTION public.test()
RETURNS integer
LANGUAGE plpgsql
AS $function$
#prepare_plans on_start
DECLARE v tp;
BEGIN
v := (10,20);
IF false THEN
RAISE NOTICE '%', v.z;
END IF;
RETURN v.a;
END;
$function$postgres=# select test();
ERROR: record "v" has no field "z"
LINE 1: SELECT v.z
^
QUERY: SELECT v.z
CONTEXT: PL/pgSQL function "test" line 7 at RAISEyou can set GUC
postgres=# set plpgsql.prepare_plans to on_start;
SETand you can overwrite this global setting with directive
#prepare_plans on_demandCREATE OR REPLACE FUNCTION public.test()
RETURNS integer
LANGUAGE plpgsql
AS $function$
#prepare_plans on_demand
DECLARE v tp;
BEGIN
v := (10,20);
IF false THEN
RAISE NOTICE '%', v.z;
END IF;
RETURN v.a;
END;
$function$Regards
Pavel Stehule
Is it possible to raise warnings at function creation time?
merlin
Re: VIP: plpgsql - early embedded sql plan preparation
Is it possible to raise warnings at function creation time?
when this code will be in core, then it is possible without one
significant exceptions - triggers :( I am not able to specify a target
table - and real type for NEW and OLD records.
Regards
Pavel
Show quoted text
merlin