explain doesn't work with execute using
От | Pavel Stehule |
---|---|
Тема | explain doesn't work with execute using |
Дата | |
Msg-id | 162867790806010230r5bd5ea7as53e33155998d8108@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: explain doesn't work with execute using
|
Список | pgsql-hackers |
Hello I found following bug - using explain in stored procedures like: CREATE OR REPLACE FUNCTION test(int) RETURNS void AS $$ DECLARE s varchar; BEGIN FOR s IN EXECUTE 'EXPLAIN SELECT * FROM o WHERE a = $1+1' USING $1 LOOP RAISE NOTICE '%', s; END LOOP; END; $$ LANGUAGE plpgsql; produce wrong result. Real plan is correct, etc variables are substituted. Bud this explain show variables. Reason is in difference in pflags. Planner works with PARAM_FLAG_CONST's variables, but explain (proc ExplainQuery) get variables from Portal, where flag PARAM_FLAG_CONST is lost. Portal SPI_cursor_open_with_args(const char *name, const char *src, int nargs, Oid *argtypes, Datum *Values,const char *Nulls, bool read_only, int cursorOptions) { ... paramLI = _SPI_convert_params(nargs, argtypes, Values, Nulls, PARAM_FLAG_CONST); // variables are correct but result = SPI_cursor_open(name, &plan, Values, Nulls, read_only); // result->portalParams lost flags Portal SPI_cursor_open(const char *name, SPIPlanPtr plan, Datum *Values, const char *Nulls, bool read_only) { CachedPlanSource *plansource; CachedPlan *cplan; List *stmt_list; char *query_string; ParamListInfo paramLI;.... if (plan->nargs > 0) { /* sizeof(ParamListInfoData)includes the first array element */ paramLI = (ParamListInfo) palloc(sizeof(ParamListInfoData) + (plan->nargs - 1) *sizeof(ParamExternData)); paramLI->numParams = plan->nargs; for (k = 0; k < plan->nargs; k++) { ParamExternData *prm = ¶mLI->params[k]; prm->ptype = plan->argtypes[k]; /***************************************************/ prm->pflags = 0; // correct flags is overwritten /***************************************************/ prm->isnull = (Nulls && Nulls[k] == 'n'); if (prm->isnull) { /* nulls just copy */ prm->value = Values[k]; } so this is strange bug - EXECUTE USING use well plan, but isn't possible verify it. Regards Pavel Stehule
В списке pgsql-hackers по дате отправления: