explain doesn't work with execute using

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: explain doesn't work with execute using
Date: 2008-06-01 09:30:19
Message-ID: 162867790806010230r5bd5ea7as53e33155998d8108@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 = &paramLI->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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gregory Stark 2008-06-01 12:20:51 Re: synchronized scans for VACUUM
Previous Message Pavel Stehule 2008-06-01 09:08:51 Re: Packages in oracle Style